Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
What purpose would a table like this serve? If I understand correctly, you want 3 separate tables, one for all (Org, Gender) valid combinations, one for (Org,Age) and one for (Org,State). Why mixing them up? –  ypercube Oct 3 '13 at 18:44
1  
+1 to ypercube's comment. These should go in separate tables. –  Jon Seigel Oct 3 '13 at 19:26
    
Because in reality there are 20+ fields that will need to be handled like this. I'm sure I can make 20 different tables, but this seems a much more concise storage method. –  Chris Chubb Oct 4 '13 at 1:16
2  
Yup, it's so concise and dynamic that help is needed to figure out how to create it! (Best of luck trying to do updates.) Why not keep things simple? If the reporting engine is more or less automated, it's really easy to drive the table to read by a metadata table, which probably ought to exist already. –  Jon Seigel Oct 4 '13 at 3:32
add comment

2 Answers

Without knowing anything about the source data, perhaps this would do what you want?

USE Test;
GO
CREATE TABLE GENDER
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NOT NULL
);

CREATE TABLE AGE
(
    ORG INT NOT NULL
    , AGE TINYINT
);

CREATE TABLE STATES
(
    ORG INT NOT NULL
    , STATENAME VARCHAR(255)
);

INSERT INTO Gender (ORG, GENDER) VALUES (1, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'F');

INSERT INTO AGE (ORG, AGE) VALUES (1,27);
INSERT INTO AGE (ORG, AGE) VALUES (1,28);
INSERT INTO AGE (ORG, AGE) VALUES (1,29);
INSERT INTO AGE (ORG, AGE) VALUES (1,30);
INSERT INTO AGE (ORG, AGE) VALUES (2,37);
INSERT INTO AGE (ORG, AGE) VALUES (2,38);
INSERT INTO AGE (ORG, AGE) VALUES (2,39);
INSERT INTO AGE (ORG, AGE) VALUES (2,40);
INSERT INTO AGE (ORG, AGE) VALUES (3, 2);

INSERT INTO STATES (ORG, STATENAME) VALUES (1,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'NM');

CREATE TABLE FACTS
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NULL
    , AGE INT NULL
    , STATENAME VARCHAR(255) NULL
);

INSERT INTO FACTS (ORG, GENDER, AGE, STATENAME)
SELECT ORG, GENDER, NULL, NULL
FROM GENDER
GROUP BY ORG, GENDER
UNION ALL
SELECT ORG, NULL, AGE, NULL
FROM AGE
GROUP BY ORG, AGE
UNION ALL
SELECT ORG, NULL, NULL, STATENAME
FROM STATES;

SELECT *
FROM FACTS
ORDER BY ORG;

The results:

enter image description here

This will create a FACTS table that has all the data from several source tables. As @ypercube and @jon-seigel said, this really doesn't make much sense; perhaps we are missing something compelling about your setup.

If this is not what you were expecting, please provide the source tables, and any other pertinent details.

share|improve this answer
    
That's close, but still not as compact as it could be. In fact, that's the method I'm currently using to create the aggregation table, but many of the distinct values have 50+ values, so it results in a lot of wasted rows. –  Chris Chubb Oct 4 '13 at 12:13
add comment

Would a more generic FACT and DIMENSION table meet your needs? The values in that table seem more like dimensions - is AGE a fact? I don't understand the context so not sure of the best solution. I assume that the objective is slicing, dicing, and analytics as opposed to storing data in a more relational structure.

If that assumption is wrong, a normalized relational table structure seems more appropriate.

share|improve this answer
    
These are starting with classic Kimball star schema Fact and Dimension tables. Gender, Age and State and all Dimensions, but the dimension tables hold the possible values for all Orgs. I need the distinct list for each Org in order to support a reporting requirement to present only valid values after the Org is known. –  Chris Chubb Oct 4 '13 at 12:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.