April 4, 2005 at 8:53 am
I am and SQL guy and AS newbie looking for a 'best practice' approach to both cube/dimension design and MDX usage for a database with several unbalanced hierarchies. What I need to be able to do is pull data elements such that values from different levels can be displayed in the same output row. For example, if fact table A containing community parks has a dimension with State, City and ParkName levels with a NumberVisitors member and fact table B containing Parks Departments has as dimension with only State and City levels with an AverageBudget calculated measure, how do I construct a statement that pulls the AverageBudget from table B at the City level and the NumberVisitors from table A at the Park level?
State City ParkName AverageBudget NumberVistors
(For City) (Per Park)
CA City 1 Park A $15,000 1000
CA City 1 Park B $15,000 1200
CA City 2 Park C $12,000 1100
CA City 2 Park D $12,000 1600
What we want to do is have average values from lesser detail levels repeat down in the result set so that they show next to values from cubes with a greater level of granularity.
We will have at least 20 cubes with a varying number of shared dimensions which we will combine into a single virtual cube. I am not really looking for a specific solution per se, just advice regarding the relationship of our cube/dimension design to our ability to generate the type of result above.
Suggestions?
April 5, 2005 at 10:06 am
From what I can gather by your situation the best way to accomplish this task is with MDX. It doesn't sound so much like a schema issue to me.
"SELECT { [Measures].[SlsTtl_U], [Measures].[SlsTtl_R] } ON COLUMNS , { [Time].[2004], [Time].[2005_Q1], [Time].[2005_FEB], [Time].[2005_W01], [Time].[2005_W02] } ON ROWS FROM [cubAcadMerchandise]"
In the above example I am just selecting different levels of the time dimension as is my preference. For your situation it seems like you could just specify the city level for table b and the park level for table a. it would probably be something like
"select {measures.AverageBudget,measures.NumberVisitors}
on columns, {[DimensionName].State,[DimensionName].City,[DimensionName].Parkname} on rows from [CubeName]"
This separates your measures from your dimensions by placing them on separate axis'. In your example you have all of your measures and dimensions along the x axis which does not makes sense.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply