How to segregate SSAS Cube data for well established cube and reporting suite?

Question

We have a well established SSAS cube design in production, with a large selection of SSRS reports and ad hoc user reports available. The cube design is somewhat complex, with a large number of business rules written into the calculations.

There is a new business requirements to add what is essentially a new entity to the data. Normally this would be allowed for in the design of the cube and would fit well within the existing dimensions, specifically this a new office location within a firm hierarchy linked to all the new fact data. However, the requirement this time is that is does not roll up within the main firm hierarchies, but should be reportable in the exact same way.

My thoughts on possible solutions for this:

Add the new entity as normal like in the example, a new office. Then change all the existing MDX SSRS reports to Except() this office.
Write more cube calculations that scope the firm level of all hierarchies and exclude the new office.
Create a new cube, which is an exact duplicate of the existing cube but uses a set of views which excludes the data via SQL. Copies of required reports could be pointed at this new cube.

I'm looking for options I have possibly not thought about and guidance on the best practice approach for this further development.

Please let me know if I need to add more information.

SebTHU SebTHU · Accepted Answer · 2017-01-09T11:45:53

All your listed options seem like a lot of work.

I think this change could be more easily done within the existing dimension structure itself - rather than recoding every single thing in the entire cube to cope with this one exceptional case.

If, for example, your existing hierarchy looks like this:

ALL
 Region
  Country
   Office Location

you could assign your "special" office to a new, irreal region and country, so that your regions list might look like this:

Europe
Asia
USA
South America
Special Office

The "special" office would then only roll up into the absolute highest level of the hierarchy. If required, you could mitigate this by adding a new level to the hierarchy, between "All" and "Region" - let's call it "Company" for convenience's sake - which would look like this:

TheNormalCompany SpecialOfficeOnly

You could then use dimension security to restrict most users to member TheNormalCompany at this level in the hierarchy (but watch out for the Visual Totals gotcha). Those who do want to see the "special office" data can be restricted to the SpecialOfficeOnly member, or granted access to both.

How to segregate SSAS Cube data for well established cube and reporting suite?

1 Answers