April 2, 2015 at 10:04 am
Good Day
I have 2 environments (Dev and Prod).
In my Dev env, I have the following calculated member
SUM(HEAD(DESCENDANTS([DIM DATE].[FSCL_YQMD].currentmember,
[DIM DATE].[FSCL_YQMD].[Month Nm])),
[Measures].[Beginning LIS])
Essentially, this is supposed to return the opening balance for me for the LIS measure and it works fine in Prod. However when run in Dev and the cube processed, I am getting some very wierd behaviour in Excel.
The values display only when a user hierarchy in the Dim Date dimension is used in row labels.
If I want to view this same measure say on another dimension say DimRegion, nothing is returned. I have the same scenarioi mimicked in prod that works fine.
This used to work in my Dev env but is now broken and I cant seem to figure out why.
April 3, 2015 at 6:28 am
Interesting...I would start with capturing the query executed against your SSAS instance. See if it is different when connecting to PROD vs. DEV.
Also check for any differences in aggregations, dimension usage in the cube or version/edition of SSAS between the two.
Also try to use the same code but as a calculated member in an MDX query in Management Studio. See if there is any difference in the results.
April 3, 2015 at 7:57 am
Hey Martin
Thanks for your help as always. Something seems to br wrong with the script because I am now replicating the problem in Prod.
So this is what doesnt work
SELECT
{[Measures].[BEGIN_LIS],[Measures].[BEGIN_LIS_tmp]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [VESFINCUBE_DEV] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
And this one works
SELECT
NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel
({[Dim Date].[FSCL_YM].[All]})}},
{[Dim Date].[FSCL_YM].[Year No].&[2015]}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Dim Date].[FSCL_YM].[Month Nm].[Quarter No] ON COLUMNS
FROM [VESFINCUBE_DEV]
WHERE ([Measures].[BEGIN_LIS])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
I notice that the Dim Date Hierarchy and drilldownmember need to be added before I can pull data. Its as though the calculation wont run without the Dim Date
The underlying Calculated Member script I am using is
CREATE MEMBER CURRENTCUBE.[Measures].BEGIN_LIS
AS SUM(HEAD(DESCENDANTS([Dim Date].[FSCL_YQM].currentmember,
[Dim Date].[FSCL_YQM].[Month Nm])),
[Measures].[Beginning LIS]),
NON_EMPTY_BEHAVIOR = { [Beginning LIS] },
VISIBLE = 1;
Not sure if the DimDate attribute relationship has anything to do with it but I'm attaching that too. Thanks for all your help.
Just wanted to add that this script works - ie with other dimensions but fails in that it adds up monthly totals to give me the opening balance and doesnt just return the first member as the opening.
SUM([Measures].[Beginning LIS], HEAD(EXISTING [DIM DATE].[FSCL_YQM].CURRENTMEMBER,1).ITEM(0))
April 3, 2015 at 9:19 am
Ok...I see what's going on here now. Took a while 🙂
The problem with your first query is that there is no dimensional scope. Without any dimension members on the axis, you would think that "[Dim Date].[FSCL_YQM].currentmember" would return the [All] member...but it doesn't.
To test this, add the following as a calculated measure to your first query:
member [Measures].[Test2]
as
(
settostr([Dim Date].[FSCL_YQM].currentmember)
)
You'll see that the measure comes up empty...no rows are returned.
Now add any dimensional member to your axis (from any dimension), and you will see that it now returns the [All] member.
I cannot necessarily tell you why this nuance exists, but it definitely seems to prove that without a dimension member on any axis of your query there is no dimensional scope and therefore no "currentmember". I stand to be corrected on this, but the behavior seems to confirm that. I'd need to do more research to try and find the official reason...
April 3, 2015 at 9:32 am
Thanks Martin
The behaviour you described is spot on in that without a date dimension on the axo=is, nothing is displayed. The question now is how do I get the values to show.
I need these Opening balances (and closing) to be analysed within other dimensions.
April 3, 2015 at 1:22 pm
ttdeveloper (4/3/2015)
Thanks MartinThe behaviour you described is spot on in that without a date dimension on the axo=is, nothing is displayed. The question now is how do I get the values to show.
I need these Opening balances (and closing) to be analysed within other dimensions.
Just include a dimension member on an axis in your query. That should work...I've tested it with the Adventure Works cube and it behaved as I expected.
When a dimension member (from any dimension) was included on the axis, the first month of the first year was returned.
April 3, 2015 at 1:42 pm
Thats totally crazy Martin because it only seems to work with the Date Dimension only in my env.
SELECT
NON EMPTY Hierarchize({DrilldownLevel({[Dim Region].[REGION_HCY].[All]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [VESFINCUBE_TRA]
WHERE ([Measures].[BEGIN_LIS])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
comes back with no results.
arrrghhh
April 3, 2015 at 1:52 pm
What does the following query return?
with member [Measures].[Test]
as
(
settostr([Dim Date].[FSCL_YQM].currentmember)
)
select{
[Measures].[BEGIN_LIS]
,[Measures].[Test]
} on 0
,{
{[Dim Region].[REGION_HCY].[REGION_HCY]} -- or replace with the correct attribute name
} on 1
from[VESFINCUBE_TRA]
April 3, 2015 at 1:56 pm
Nada
🙁
Attached output - just so i know im not losing my mind here
April 3, 2015 at 2:01 pm
ttdeveloper (4/3/2015)
Nada🙁
Not sure what the problem is then. The only other thing I can suggest is that you check the dimension usage in your cube. Ensure that the fact table has references to both the date dimension and region dimension (if that is what you need to use here).
Also try attributes from other dimensions to see if the behavior is different.
Lastly, add the calculated measure to the query also and see if the behavior is different from the one in the cube.
April 3, 2015 at 6:36 pm
Hey Martin
I appreciate you taking the time to even help with this issue.
I made a few attribute changes to your script and have the following result set attached.
I am going to be working on this during the weekend and Ill see what I can come up with by Monday.
Have a good weekend. Ill post back with updates if I have any following your suggestions.
April 6, 2015 at 8:23 am
Hey Martin
Good Morning. I am current troubleshooting this issue further and have nailed it down to the fact that my .currentmember instead of returning members inside the hierarchy it iterates is returning .All as in {[Dim Date].[FSCL_YM].[All]} as supposed to something like
{[Dim Date].[FSCL_YM].[Year No].&[2014].$[Q1]..}which i am assuming is what it should be doing across each level.
I think this is affecting the way the opening balance is trying to calculate.
Ill post with any updates
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply