In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list of all possible values of certain fields that are present in the fact table.
Assume that all of the source data is in a single flat Fact table. Imagine something like this:
|Org |Gender| Age|State| -------------------------- | 1| M| 1| FL| | 1| F| 2| VA| | 1| M| 3| CA| | 1| M| 2| NJ| | 2| M| 2| VA| | 2| M| 3| VA| | 2| M| 5| VA| (Times 400+ million, but for only about 100 Orgs...)
I would like to create an aggregation table that has the following output structure:
|Org |Gender| Age|State| -------------------------- | 1| M| 1| FL| | 1| F| 2| VA| | 1| NULL| 3| CA| | 1| NULL|NULL| NJ| | 2| M| 2| VA| | 2| NULL| 3| NULL| | 2| NULL| 5| NULL|
So that for each Org, the non-NULL DISTINCT list in each column represents the list of distinct values in the Fact table for each column. I can't use a DISTINCT operator because that will look for the list of distinct combinations of fields, not where each field is distinct and the number of rows for each Org is at most the number of distinct values for that Org.
Added comments: This is for a Data Warehouse, so the source data started off in the transactional relational database that is the companies working database. It's then denormalized into a Fact table and set of Dimension tables that support the possible values of other data. I don't have access to the original relational DB for this project.
Purpose: But we are now putting an ad-hoc reporting interface on top of the data warehouse and in order to efficiently query the Fact and Dimension tables we have to build some aggregation tables so that the selection fields of the reporting interface don't have to query the hundreds of millions of rows in the fact+dimension tables to find the possible values for each org to present in select dropdowns after the Org is chosen.
I've done a lot of googling and haven't found much, but I don't even know what you would call this kind of structure. "Columnar distinct?"
The database I'm using is SQL Server 2012, if it matters.