Dynamic (user requested) Dimension

In our project, we constantly have the need to analyse the data in the cubes, but with "restricted" dimension... maybe an example helps:

Like this: "Get the total for orders, but only those placed on a Wednesdays". This would be an easy task, since you only have 7 weekdays. In your cube browsing frontend you simply click away the 6 you do not need, and there is your result.

Now imagine you get a list of 10,000 Customer IDs, or maybe 30,000 Invoice IDs, or only 300 Product IDs that shall be used to restrict the space in which all further analysis takes place. No one wants to (manually) select 10,000 items out of a list of 2.5 million... even selecting the 300 Products out of the Products list (2,500 items) is a task you cannot really explain to someone.

The content of the lists do change, but not very frequent. IF the lists are changed however, they are changed completly, not just added upon! These special jobs happen maybe once a week, and are started only by one user at a time. So there is no need to make this work with several different lists at once or so.

Currently, I am importing the list into SQL Server, then build a query "joining" the list with my tables so I get the restricted data, and then recreate all groupings and calculations the cube does by manual SQL - the last part is the really bad job.

What can be done to have a dimension (or in this case, 3 lists = 3 dimensions) in the cube whose content is based on an "external" list that changes from time to time?

I will solve the "external" part first by creating a either mail-in or html form to upload a txt list file to the server and then run a SSIS package to import the data so it is part of the database - no problem there!

How would "intelligent" processing of this dimension and the cube be handeled? I understand it that "dimension update processing" is not possible, as the content can be totally different. Doing "dimension full processing" will solve that, but require a "full cube processing" as well?

Hi Ralf,

try to have a look at this precious document by Marco Russo and check the Multiple Groups chapter, probably is what you're looking for:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf