October 28, 2014 at 8:16 am
I know little about SSAS but I have to troubleshoot and MDX Query who's output does not correspond to what it should. We have a normalized Relational Database on SQL Server 2008R2 that serves as the source for an SSAS 2012 Analysis Services cube. What I need to do is figure out where in the process (from relational DB to DB used by SSAS to Cube to output of MDX Query) the math is failing.
our Relational DB has a view that returns several pieces of information about the entities (apartment complexes) including a count of units or rental apartments at each complex. This view is used as the starting point to populate a DB used by the cube. What I need to know is how to trouble shoot from this view which is showing the correct values to the output of the MDX query which is not showing the correct values. I do know that the cube has been processed/updated (or whatever the proper term in SSAS to indicate that new/updated data from the source relational database has been copied over to the DB used by the cube)
Below is the MDX query being used to return 2 pieces of data; the name of an apartment complex and the number of units associated with each. How do I find out how the cube is determining how to calculate UNITS (the second column of the output from the MDX Query)?
SELECT
{[Accounts Budget].[Default].&[UNITS]} ON 0,
NON EMPTY {DESCENDANTS([Properties].[Default].&[Total], , LEAVES)} ON 1
FROM [Budget Model]
WHERE ([Time].[Default].&[2014M01], [Measures].[Value], [Time Perspective].[Default].&[BASE], [Version].[Default].&[ACT]) CELL PROPERTIES VALUE
Thanks
Kindest Regards,
Just say No to Facebook!November 6, 2014 at 3:18 am
YSLGuru (10/28/2014)
SELECT{[Accounts Budget].[Default].&[UNITS]} ON 0,
NON EMPTY {DESCENDANTS([Properties].[Default].&[Total], , LEAVES)} ON 1
FROM [Budget Model]
WHERE ([Time].[Default].&[2014M01], [Measures].[Value], [Time Perspective].[Default].&[BASE], [Version].[Default].&[ACT]) CELL PROPERTIES VALUE
It would seem that the measure group where you are getting the [Measures].[Value] measure from is intended to be linked to the [Accounts Budget] dimension. This dimension might be there to indicate what type of fact is in a measure (for example Budget, Actual or Forecast - in this case Units). So to find out what the constituent parts of the query are then you should look to the fact table that's building the measure group. I would expect this fact table to have some sort of foreign key in it to indicate what member of the [Accounts] budget dimension that the fact pertains to. Isolate the ones that pertain to UNITS and you have your answer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply