troubleshooting Question

Demographic: Dimension or fact

Avatar of johnftamburo
johnftamburo asked on
Microsoft SQL ServerDatabasesSSAS
19 Comments2 Solutions1937 ViewsLast Modified:
I have a fairly simple star.  7 dimensions that are easily set up.  Fact table with various dates and counts.  It contains customer ID  Customer is a dimension with primary key customer ID.

There is a demographic table.  I have denormalized it to this:
ID                  customer id  Demo                          Demo Value
46965000      274701      Employee Size      250 to 499
46355000      274701      Industry      Computer Manufacturer
45745000      274701      Job Title      Corporate Management
49145000      274701      Opt-out      Do Not Promote
43880000      274701      Original Product      Brand A Seminar
43880001      274701      Original Product      Brand B Print
43880002      274701      Original Product      Brand B Seminar
43880003      274701      Original Product      Brand C Print
43880004      274701      Original Product      Brand C Newsletter
47575000      274701      Purchasing Budget      less than $5,000,000
48185000      274701      Purchasing Scope      Technical Decision Maker
43270000      274701      Status      Incomplete
46965001      274702      Employee Size      250 to 499
46355001      274702      Industry      Financial/Banking/Insurance/Real Estate/Legal
45745001      274702      Job Title      Sales/Marketing
49145001      274702      Opt-out      Newsletter A Opt-out
43880005      274702      Original Product      Brand C Tradeshow
43880006      274702      Original Product      Brand C Seminar
43880007      274702      Original Product      Brand C Newsletter
47575001      274702      Purchasing Budget      $10,000,000 to $24,999,999
48185001      274702      Purchasing Scope      Technical Decision Maker
48185002      274702      Purchasing Scope      Financial Decision Maker
43270001      274702      Status      Reserve
46965002      274703      Employee Size      5,000 to 9,999
46355002      274703      Industry      Travel/Hospitality/Recreation
45745002      274703      Job Title      Purchasing/Buyer
43880008      274703      Original Product      Brand A Tradeshow
43880009      274703      Original Product      Brand A Newsletter
47575002      274703      Purchasing Budget      $150,000,000+
48185003      274703      Purchasing Scope      Evaluate Products/Services
43270002      274703      Status      Reserve
46965003      274704      Employee Size      500 to 999
46355003      274704      Industry      Transportation
45745003      274704      Job Title      Operating Management
49145002      274704      Opt-out      Do Not Promote
43880010      274704      Original Product      Brand A Print
43880011      274704      Original Product      Brand A Seminar
43880012      274704      Original Product      Brand B Print
43880013      274704      Original Product      Brand B Newsletter
47575003      274704      Purchasing Budget      $5,000,000 to $9,999,999
48185004      274704      Purchasing Scope      Technical Decision Maker

Each customer can have an infinite number of demographics.  Many already have 1500+ defined.

Management wants to drill into demographics and see the facts for it.  Normally one would spread the demographics into columns but there are too many demographics to do so.  But we run into column limitations.

Is there a way to define this as a fact table or something that would allow my managers to slice and dice the other fact table as if this demographic table were a Dimension?

I wish there were 500,000 points for this one!
ASKER CERTIFIED SOLUTION
agandau

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros