Maximum number of facts in star schema / SSAR cube??

Question

Maximum number of facts in star schema / SSAR cube??

boxta

Ten Centuries

Points: 1374
More actions
December 2, 2008 at 9:15 am

#196758

I have been in the BI industry for many years, and have always worked on nicely populated dimensions with a reaonable number of fact in a star schema format.
recently.. i am a requirement where company xxx is tracking about 400 metrics per each of its store monthly. and that's all they do... instinctively. i have the store dimension and time dimension... with 400 facts in the fact table!!!
it looks more like a flat file table in this format than a true star schema... and it doesn't "seem" right.. but each of those 400 metrics DOES seem like it depends on store and change monthly...
so what's the best practice to handle this situation??
Also, if ultimately, reporting is all done from SSAS OLAP.. and those 400 facts will be populated to the cube's measures.. and user would not see the relational DW ... then does it matter thee DW look weird in the middle? (hmmmm..)
please advise...
thanks a lot!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

Seems rather unlikely that you would have 400+ metrics. I would think that some of these are possibly attributes of a store (like size, etc.) and not metrics, but I would not have any idea with out seeing it directly. Other metrics I would assume would have to be setup for calculations instead of straight metrics like (avg sales per sq ft, net profit, etc.).

I would think that you would have more dimensions then just store (which is probably a SCD Type2 tracking changes) and time dimension (probably just a date dimension). I would expect to see an employee (or sales person), transaction type or sales type, geography (could be a hierarchy in store dimension), customer, vendor/manufacturer, product, promotion, currency (possibly).

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

boxta Ten Centuries Points: 1374 More actions · Answer 2

That's my first reaction too.. i haven't seen any requirements where there are 400+ metrics at the same grain..

but I looked at their current reports in excel.. it's indeed the grain at store month level.. and then there are 400+ rows with different metrics.. and the columns are differnet month.. and they are all numeric metrics which changes every month.

let's use another example.. like a school dorm report..w here the grain is at a dorm / month level.

metrics would be like...

# of freshmens

# of sophomores..

# of beds (which changes monthly)

# of sr. staff.

# of alcohol incidents

etc etc..

there isn't really a common dimensionality across those metrics where i can easily divide it up into different fact tables with different dimensions...

any suggestions?

thanks

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 3

Can't you have another dimension called METRICS or something like this so that you measure dimensions would have like one or two figures, rather than storing the whole lot in measures dimension?

Sure it will add another dimensionality, but gee having 400 facts is ugly.

For sure 99% of them are numeric and the only difference between them would be the naming of the fact (figure) and the actual figure.

Smells like one dimension is missing

Correct me if I am wrong

VAL

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

boxta Ten Centuries Points: 1374 More actions · Answer 4

hmm.. i thought about having a measurement dimensions.. but how would it impact creating measures in SSAS??

Wouldn't SSAS automatically create the measures based on my fact table's columns?

would it be easier to create the SSAS.. and also report from the star schema via SSRS if i flatten out the facts (400+ facts)..

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 5

martin.lam (12/2/2008)
hmm.. i thought about having a measurement dimensions.. but how would it impact creating measures in SSAS??
Wouldn't SSAS automatically create the measures based on my fact table's columns?
would it be easier to create the SSAS.. and also report from the star schema via SSRS if i flatten out the facts (400+ facts)..

This is the gotcha -- you cannot use the same table anymore .. You should unpivot it -- mak it longer in length and slimmer in width.

Get what I mean?

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

boxta Ten Centuries Points: 1374 More actions · Answer 6

Sorry.. not sure I understand exactly what you said... but did you mean it's much easier to do the SSAS population with 400 facts as columns??

or did you mean it's easier with a measure dimension.. with 400 measures as dimension value and 1 fact with the numeric measurement??

thanks

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 7

boxta (12/3/2008)
Sorry.. not sure I understand exactly what you said... but did you mean it's much easier to do the SSAS population with 400 facts as columns??
or did you mean it's easier with a measure dimension.. with 400 measures as dimension value and 1 fact with the numeric measurement??
thanks

second....

first is what you got and what raised an alarm...

second is more logical, but it will require reshaping the fact table, and might introduce more aggregations, bigger cube and so on.

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM