Possible to Create OLAP Cube filtered by dimension?

Question

I hope this is a quick yes or no question and I believe the answer is yes, but wanted to confirm with someone more knowledgeable than I.

First the background story: We've just started using OLAP cubes where I work and have one setup each for financial, staffing, and project data (3 cubes) in our new ERP system. We have several offices where this data is needed, but we don't want for them to have access to the other office's data. There are only a few individuals in the organization that have access rights to these 3 online cubes.

For distributing the appropriate set of data for the individual offices, the solution I've currently come up with is to create offline cubes that are connected to pre-configured excel files (using Excel 2013 Standard or Plus). Each offline cube is created via the "CREATE GLOBAL CUBE" MDX command and then is filtered via one of the dimensions (see below).

This solution has not been very effective as these offline cubes use absolute paths in the excel connection files. We've been remotely connecting to the users desktops to reconnect the offline cube that gets distributed to them. This process is not very effective as it seems excel sometimes doesn't want to reconnect to the offline cube and in some instances simply won't work.

The question: Is there a way to create an online cube that has been pre-filtered on a particular dimension so that I can allow access to their online cube for only their data rather than using offline cubes?

I've listed the commands I'm using to create the offline cube below as an example of what I'm looking for.

Cheers

CREATE GLOBAL CUBE
    [Offline Cube Name]
STORAGE
    'C:\SomeFile.cub'
FROM [Online Cube Name] (
    MEASURE [Online Cube Name].[Some Measure 1],
    MEASURE [Online Cube Name].[Some Measure 2],
    DIMENSION [Online Cube Name].[Dimension].[Office Name]
    (
        LEVEL [Organization],
        MEMBER [Organization].&[Office Name]
    )
);

GregGalloway GregGalloway · Accepted Answer · 2016-03-17T00:52:08

If you have been completely successful with local cubes count yourself lucky as they are tricky. But I would recommend you avoid them.

Instead setup role-based security in the cube and let users connect live. If you have a list of Active Directory users or groups per office and want to create one role in SSAS per office follow this approach. If you prefer to add a few hidden tables to the cube that drive security then use the dynamic security approach.

Possible to Create OLAP Cube filtered by dimension?

1 Answers