easyROLES the fastest way to set up SSAS roles

Surviving corporate restructuring

This is inevitable – corporate restructuring, when different units are merged, moved around and created, will hit your Data warehousing / Business Intelligence team one day or another (some of us deal with this yearly). An avalanche of changes to hierarchies, security roles and reports will follow as well. Here are some considerations to help you reduce workload during these hectic times.

The first thing to make sure of is that dimension data (master data or what ever you might call everything that is not facts) maintenance is outside of your team responsibility. There should be someone on the ‘business side’ making sure that all corporate units are in the right hierarchies, all products are assigned to correct product groups etc. As long as people responsible for this data have adequate tools for the job, BI/DW team should only need to read the data and do no structural changes to it what so ever. This means that when ever there are changes to corporate structure you will not need to deal with changing the labels- changes will just arrive by them selves.

The second big thing – managing our favorite security roles. These need to follow the new hierarchies as well. Removing all existing roles and adding the new ones will take for ever unless you have some kind of semi-dynamic solution for this. However, if you use SSAS as your data source, you can just copy the list of users and members they should have access to into easyROLES, click update button and be over with the changes. You get this list with user-to-member relationship from the business owner anyway, so why click it all in manually when all can just happen automagically?

OK, so we are in the clear in regards to data and accessing it. Now the only thing left to do is make sure all of our reports are updated and there are no more filters and references to objects (members) that are no longer available. I guess this would be the major challenge as this is not so much dependent on your own team, but ┬ámore of a long and rough road of educating the business. Your ultimate goal is to make sure that all reports that are made are created using relative objects only. That means there should be no “Year 2014” report, no “New York sales”, etc. All these references to members will cause trouble as one day these members will become obsolete and thus your report will require attention.

To get away from all these references we want to make sure our reports use hierarchies, levels, relative references to members, relative time items, filtering members via role security and other goodies that will make sure that your report is still running and is just as usable and up-to-date after all the changes in real life. The basic example of this would be to move away from 2014 New York Sales report that is visible to NY region manager to Current Year Regional Sales report that is automatically filtered via a security role. This implies that every region manager in the country would be able to open this one report (we minimise the number of reports by moving away from one report per region to one report per role) and automatically see his/her region sales data. Also, as we would use relative time, this report would always show current year, no matter which year it is. This way we no longer need to maintain our reports when, for example, NY is merged with MA, or when we go from one year to another.

As you can see, by using hierarchy data as it is and combining that with smart reporting and SSAS security role functionality we can make even the worse times like corporate restructuring go by without much trouble for the BI/DW team. It’s all about being relative and fast.

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookShare on StumbleUponEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

We want you to be comfortable with your purchase and therefore offer 30 day 100% money back guarantee.