How to get "raw" facts data from Analysis Services

Question

We are developed a custom MOLAP engine for live processing a large amount of data in process. And now we got a requirement to integrate SSAS with our system. Dimension's meta info and attributes data are very easy to get. But how can I get facts data without making SSAS to calculate aggregates i.e. data that stored only on leafs? Aggregates and calculations we perform ourselves.

Selecting data from SQL Database is not a solution because Cube while loading can perform joins, filtering. clearing of data etc.

Example:

Suppose we have Product Dimension:

• Product.All
  +Product.Bread
  +Product.Chair
  +Product.Book

And facts table - Sales:

|Product|Qty|  
|Bread  | 1 |  
|Chair  | 3 |  

I want to get from cube only real data, not aggregated one:

|Product|Qty|
|All    | 4 |  
|Book   | 0 |  
|Bread  | 1 |  
|Chair  | 3 |

Answer 1

Bottom line, you cannot get the updated facts without reprocessing the cube. If you want the raw data in real time you will have to actually calculate it yourself from the dimension/fact tables in the data warehouse.

Answer 2

in ssas u can disable hierarchy level aggregation and for the empty rows use non- empty as a keyword before fetching the data

Answer 3

Try looking at DrillThrough in SSAS. This can display the fact-grain data, and is available in Excel as the default action when you double-click a cell. You can customise the result set, so long as all measures/dimensions come from the relevant fact table:

http://csjohnlam.spaces.live.com/Blog/cns!996308EF3A99432B!214.entry?sa=340601263

You'll also find this useful:

http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

Answer 4

hi paranjai, I tried using "Allmemberagregationusuage" and have also set custom member formula. But it is still showing the aggregated values, not the value from the formula. Please help.

Jameel