October 10, 2011 at 6:57 am
We have a fact table which has a RowID that comes from the OLTP system. That OLTP system has a table with that ID defined as an identity.
Now our customer want in some way to be able to see that ID in his MDX queries and reports, for example when he does a DRILLTHROUGH.
I know he can query it when we provide a dimension with each original key. But because we have more than 50000000 rows in the fact table, I want to have a better solution.
Anyone who knows another possible design?
October 10, 2011 at 8:41 am
It is not really a good idea to introduce an attribute, in your case the operational key, with such a cardinality (ca. 50 million) in the cube.
However, you could add it to the underlying relational fact table.
That way it would be possible to use it for reporting.
There is no law that forbids to query your data warehouse on the relational level.
Franky
Franky L.
October 11, 2011 at 8:15 am
You can create a fact dimension that references such properties that are part of the fact table but don't belong in an dimension. You must remember to reprocess it anytime rows are added. I created them for exactly the reason you mentioned--to include some key identifying data in the drill through.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply