OLAP Cube percentages of count

Vote count:

0

I am really new to SSAS, OLAP and Data Warehousing in general so if I don't make myself clear please let me know.

I have a table in my database (300,000 rows) with multiple columns that are properties of registrations and a datetime column. What I want to do is display those registrations by Time(column area) and Brand(row area) and have a percentage (market share) in the data area of a pivot grid.

So far I've created a simple cube with two dimensions: Properties and Time(I created a calendar dimension) and a Count measure. I've also added a simple calculated measure (Share) which calculates a percentage of the row count based on the total:

Case
// Test to avoid division by zero.
When IsEmpty
 ( 
    [Measures].[Count] 
 ) 
Then Null

Else ( [Registrations].[Id].CurrentMember,
   [Measures].[Count] ) 
 /
 ( 
   // The Root function returns the (All) value for the target dimension.
   Root     
   ( 
      [Registrations] 
    ), 
    [Measures].[Count] 
 )

End

This works fine so far.

My problem is that I want to create some presets in my application (where I will be displaying the Pivot) that the end user will be able to select and have the Share measure be calculated on the total of that filtered data and since the Share measure is calculated on the Analysis Server I suppose this is where the filtering and recalculation should happen as well.

For example, I have an attribute in my Properties Dimension that is called Registration Type and contains 2 members (NEW and USED). The same thing for a different column etc.

How can I filter the cube based on those attribute filters (e.g. select only rows where Registration Type = USED) and have the share measure be calculated by the new total rows?

I've already tried by using Custom MDX queries and while it filters the data correctly the Share Measure remains the same.

asked 1 min ago

user4483037

3

OLAP Cube percentages of count