I have assets that are in locations. The locations are in banks. The banks are in sections. I have a filter setup to only view banks of certain sizes. The size of the bank is equal to the count of locations on the bank. The problem I am having is I have been unable to build a dataset that only lists the unique bank sizes for a parameter available values query. I can build a query that lists the location count for each section and bank but there is a lot of redundancy there. If I have 20 banks that are size 8 then I get '8' listed 20 times. I want '8' listed only once and the bank sizes in ascending order.
To list all the banks including duplicate sizes I use the following MDX;
With MEMBER [MEASURES].[BankSize] AS DistinctCount( [Properties].[Location].[Location] )
SELECT [MEASURES].[BankSize] ON 0, ([Properties].[Section].[Section].ALLMEMBERS * [Properties].[Bank].[Bank].ALLMEMBERS ) on 1
I thought I might be able to get the results I want by making the query above a sub query but I have just been getting one row with the total count when I have been able to get anything to process.
There is another field that has a '1' for every day the asset is active in a location and '0' for inactive days. I have used a line like the one below but my results have been the same as the location count.
With MEMBER [MEASURES].[BankSize] AS Sum( [Measures].[Days] )
If that is not clear, I could show how I would do it in SQL. I just have not been able to figure it out in MDX yet.
Why not make the BankSize a column in your relational source and then add Bank Size as an dimension attribute? That's going to be the most straightforward way to accomplish what you're trying to do. You could use a partitioning window function to add Bank Size to your relational source.
SELECT LocationKey, LocationName, BankKey, BankName, COUNT(*) OVER ( PARTITION BY LocationKey ) AS BankSize, ...
<a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>
- Edited by Martin Mason Wednesday, October 23, 2013 2:33 AM
Thank you for a solution Martin. While I considered that adding the field to the cube might be the simplest solution, I thought that there ought to be a solution in MDX. I am still learning MDX but I figured that I could do everything in it that I could do in SQL once I get more experience with it. While in this case, I can have someone modify the the cube, I figure there may be times where I cannot modify the source and still need to produce data in a similar way. Admittedly, perhaps what I need to learn is that sources and output may have a more overlapping relationship in OLAP than in relational databases. If anyone knows of a way to accomplish the solution in MDX, I would still be interested.