I've been tasked with building some ad-hoc reports in Excel that are sourced from an SSAS OLAP cube. I don't have the ability to alter the design of the cube's dimensions currently. I've been receiving repeated requests to filter results based upon the combination of two different dimensions and their attributes.
For example:
One dimension lists locations with their hierarchies. Another dimension contains codes for the various insurance companies we work with. I'm given a list of combinations of these, concatenated with a hyphen separating them, and they are supposed to be the only combinations within the report. For example, I get things like "001-AB5". Unfortunately, there are duplicates of the codes, so I can't just pull the code, seeing that AB5 means different things for different locations, which I can't do anything about at this time either.
For some of the smaller data sets, I've used PowerPivot and just created a calculated column, and added a relationship to the list in another sheet. The issue is that now they want the drill-through actions that have been setup for the cube. Is it possible to create something like a calculated dimension in Excel (or some other means) that would be the concatenation of these without using PowerPivot?
