Archive for the 'SSAS' Category

Ragged Dimensions and Excel 2007

If you have ragged dimensions, i.e. some paths from the top node to a leaf node differ in length, and you are using Excel 2007 as a front end, then… bad news! It will not work.

Read more »

Deploying a Microsoft BI Solution via Script

At the end of a phase of a business intelligence project, when it has been accepted and it is time to move into production, comes the deployment on the production environment. The deployment itself should also be testable, reproducible and of course be completely debugged. The method largely depends on the customer’s IT team’s way of working. Some will insist on Installshield executables, some will prefer moving a server from acceptance to deployment, some will restore backups, some will work script based. I prefer that last approach and go into some detail on how to do this for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.

Read more »

Drillthrough with Multiple Selected Values in Excel2007

Excel2007 is a great tool for power users/analysts to access a SSAS cube. They can slice ‘n dice the data as never before and even make appealing reports on the fly. Yet the new drillthrough capability is not completely bug free. First of all, it is impossible to drillthrough if the user has selected multiple values in a filter hierarchy or in a row or column axis. Okay, that is a restriction with the current state of SSAS2005. (I’ll explain how to get around it via MDX in another post.) What is worse is that in certain situations it does allow a drillthrough but then displays wrong results.

Read more »

Dimension Security and Drillthrough with SSAS2005

We are facing a problem when applying simple dimension security: it disables drillthrough.

The business case is simple enough: there are two usergroups, one that is denied access to a specific dimension and one that is allowed to see its contents. (In theory we would like to completely hide that
dimension for the first group, but that is impossible – vote for this issue here.)

To implement this we have set the Allowed Member Set to {} for all attributes of the particular dimension. It works as expected.

But… the drillthrough stops working for the users that have no access to the dimension (the drillthrough does not contain elements from that dimension). There are no frivolous calculated measures, no cell security, …

Read more »