Filter dimensions by other tables

Question

I'm attempting to convert an existing relational database to a SSAS solution. I'm new to SSAS though and would appreciate advice on how to handle the following issue:

I have a cube with 6 hierarchical dimensions (Product, Account, Geography, Time, and 2 custom categorizations for sales data). The current application also has a separate data structure to define Markets, a Market is just a collection of selected dimension members. So, even though the dimensions are fixed, this allows the user to say select Market A which includes only certain geographies, certain products, certain accounts etc. It functions similar to SSAS roles I believe but is not user or role specific, just a parameter.

There are approximately 14k defined dimension members. How, in MDX I assume, can I query those market dimensions and use them as filters efficiently against the cube dimensions? Markets don't seem to belong to the cube as they don't define the data, just filters on the dimensions. MDX only seems to be able to query cubes.

Would I need to query the market structure separately and just create a massive INTERSECT clause for each of the dimensions that includes all the markets members for that dimension and would that perform well?

Answer 1

I would add Markets as a Measure Group with a single Count Measure called Market Members.

I would ensure Markets has a row for each Market Member and columns for all 6 dimension keys. I would relate the Markets Measure Group to each dimension via those keys.

Then you can use pretty much any client tool or a simple MDX query on the Market Members Measure and any dimensions of interest.