Please Help- Drill Through To Very Large Dimension Table confusion...

[sergioza]

Hi,

I'm struggling with following design issue.

We're currently using Excel Pivot Tables which are tied to SSAS cubes.

This cube represents sales which were generated against certain promotional campaigns.

The way we built it, we summarized all dimensions and facts in one SQL Serger view.
It was generated by using group by against all dimension fields (5 or 6 fields).

We don't have "CustomerID" dimension though.
We have this "Customers" table (over 10 mln Rows) in SQL server, but it's not being used while building the cube
--------------------------------------------------------------------------------------

Now, the requirement is that we want to allow our users (preferably from Excel Pivot Table) to be able to choose
certain Pivot Table cell (measure cell) and see list of "CustomerIDs" which represent this cell.

Here are a few potential solutions and problems related to those:

1. Attach SSAS "Cube Rowset Action" to the cube and run statement on the Server side against SQL Server
"Customers" table or create a "Dimension" and run MDX against this dimension.
The problem is that if users use "multi select" filter "Actions" get disabled in Excel Pivot Table (well known Exel issue)

2. Create "Dimension Table" which may contain tens of MILLIONS of Records and include surrogate key in the cube. Surrogate Key should be created in a way that it's still one to one relationship between existing View and Newly created "Customers" dimension table.

Then create a Macro from Excel which would run SQL or MDX request.
Technically if this makes sense there are a few big questions:
a. Should this surrogate key (which probably will be actually RowNumber with millions of values) be part of "Pivot Table" and dangers around it?
b. If it is part of "PivotTable" how it can be used.

Many Thanx for Reading This

S