Ashwani 的个人资料Ashwani Roy's BI Blog照片日志列表更多  |  工具 帮助 |
|
|
3月20日 Creating Sub Cubes - Visual and Non Visual ModeWith AS 2008 , you can create subcube in Visual and Non Visual Modes. First of all What is Visual and Non Visual Mode. I will try to write some simple MDX to demonstrate it.
Fire this query without any subcubeing
select {[Measures].[Reseller Sales Amount] } on 0,[Business Type].members on 1
from where You Get
Alright so this is the All Resellers total Value ($80,450,596.98)
Now lets fire the next MDX but this time with simple SUB CUBE
CREATE Select {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} from with member ([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount])
select [Business Type]. from where
You would see that Visual total is eaqul to Reseller total which is much less than the actual total. Why does this happen? This is because when you try to do a total on SUB CUBE with considers only VISUAL TOTALS , the other values ( of Reseller Sales here) will not be aggregated. Sometimes this is what is required in a business scenarion but many times you will find your customers want to see total figure . In this case the query below will show the SUB CUBE total and the NON VISUAL total . For this we will create a sub cube with NON VISUAL mode.(this is available from AS 2008)
CREATE NON {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} from
select [Category].members on 0,[Business Type]. members on 1from [Adventure Works]where [Measures].[Reseller Sales Amount]
with member Measures.VisualSum As([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount]) select {[Measures].[Reseller Sales Amount] ,Measures.VisualSum} on 0,[Business Type]. members on 1from [Adventure Works]where [Category].members
this is what you get
See that the NON VISUAL total $80,450,596.98 is without considering the sub cube and this is eaqul to the first total that we had , which was on the complete cube , but the Visual total is same as the Total we had for the SUB CUBE in Visual Mode.
I have tried my best to explain that Visual and Nonb Visual totals are how can Create SUB CUBE be used in both modes. For more do go to http://msdn.microsoft.com/en-us/library/ms144916.aspx
11月30日 Talking about NonEmpty$$ and that all-important second parameter
Quote NonEmpty$$ and that all-important second parameter
|
|
|
