SSAS Measure with multi concept

Question

I Use SQL Server Analyse Service and have a sales cube. In first I use role playing dimension in my cube and have about 10 Date Dimension on it.

In this step I have two problems.

first: usually users use excel to get data from cube and in pivot table exists 10 date dimension. it's difficult to find a suitable dimension.

second: for example user select SalesDate Dimension in Row and select InstallationCount Measure. User can get multi concept from this result. for Example user See September In SalesDate and Value in InstallationCount:

1. This Value is the installation Count in September
2. This Value is the Installation count related to September Sales
3. This Value is the Installation Count in September that related to September Sales

In another word my cube is not a self document cube.

I use following Technic for solve my problem.

I create a fact table with one Date column and Type Column, and convert data to this new fact table. Sales Date, Installation Date and another date fields save in the same Date Column with different Type Column.

Now I have a Cube with single Date Dimension and multi measure with different concept. for example "Sell In - Sable Date" and "Sell In - Installation Date" Measures.

"Sell In - Sales Date" Measure means Sales Value that related to Sales Date.

"Sell In - Installation Date" Measure means Sales Value that related to Installation Date.

End Users are happy because can use cube in excel easy and all measure are self document. but now I have a problem.

User can't get result such as "Installation Count By Installation Date In row that Sales In September.

How can I solve this problem.

Thank in advance.

Answer 1

Go back to using role playing dimensions, which is the correct way to model your cube.

If you need to make your cube more self-documented, you can create calculated members that have user-friendly names. But really, this should be a simple matter of training your users to understand how cubes work in Excel.