Slightly related dimensions in multidimensional design

Question

I am using the following (simplified) star scheme as the data source for an SSAS OLAP cube:

In my cube, I have measure [Days], which simply contains "1" for each date - this is just a neat way to get the number of days over some period, useful for calculating daily averages, etc. Obviously, this measure is unrelated to both the Shop- and the Employee-dimension.

Now, say for example, I use the following MDX query:

SELECT { [SalesAmount], [Days] } on 0,
    { [Shop].[Shop].[Shop].members } on 1
FROM
    [MyCube]
WHERE
    ( [Shop].[Country].[USA], [Calendar].[Month].&[201406] )

This returns a list of all shops in USA, with their sales amount for June 2014, and the number of days in June. It works as expected and performance is great.

Say now, that I want the same list, but in addition to shops, I want to split the sales amount by employee. Naturally, I cross the Employee-dimension with the Shop-dimension:

SELECT { [SalesAmount], [Days] } on 0,
    { [Shop].[Shop].[Shop].members * 
      [Employee].[Employee].[Employee].members } on 1
FROM
    [MyCube]
WHERE
    ( [Shop].[Country].[USA], [Calendar].[Month].&[201406] )

There are two issues with this: First of all, performance suffers a lot when crossing these two (potentially) big dimensions. Secondly, I get a lot of records with Sales Amount = NULL, for all those employees who are not affiliated with shops in the USA. If I remove the [Days] measure, I get the expected result, but I need that measure for daily averages, etc.

I am looking for alternative ways to model my cube, to avoid this problem. That is, when I filter my fact table by the Shop-dimension, I only want relevant records from the Employee-dimension shown (hence the title of this post, since the Employee and Shop dimensions are related through the fact table).

I have considered combining the Shop and Employee dimension into a single "organization" dimension, but this creates multiple problems: First of all, an employee may work in more than one shop, and over time affiliations may change.

Note: I am not looking for an alternative MDX solution, as my end-users use various front-end tools, where they don't have any control over the generated MDX. The problem, as I see it, should be solved in the multidimensional modelling, and not in the front-end. Modelling techniques and references to literature will be very appreciated.

Answer 1

If you can have employees under shops that can avoid crossing 2 independent dimensions. That will improve the performance I believe.

Answer 2

I am not sure if it is the case for your solution, but in the cases where I used a measure like your Days measure, it was necessary for the calculation of several calculated measures, but it was not necessary for the end users to see the Days measure itself. Thus, just making the measure invisible could resolve your issues:

As users cannot select the measure in normal client tools, the queries will not contain the records for employees and shop combinations not having a common record in your factSales table. At least this is true if the client tool uses NON EMPTY on the rows, which most tools do by default. And as the result set is much smaller, it should also arrive at the client tool much faster, solving the performance problem presumably.

You should take care in the expressions for your calculated measures that you keep the result as NULL when there are no data. How to do this will depend on the measures you define.

And you should be aware that whenever users are able to enter MDX expressions manually, they would be able to use the measure even if it is invisible. An invisible measure is available in the cube and can be used in an MDX query. It is just not listed in the list of measures displayed by many client tools. But if users deliberately use the Days measure, as they know it is there and they know the name of the measure and thus can enter it in an MDX expression, they should be aware that they need to design their MDX in a way that avoids the issues you have. These users would be "power users" anyway.