Best Practices: Fact Tables & Measures

astrodon

New Member
Joined
Dec 29, 2019
Messages
3
Office Version
365, 2010, 2007
Platform
Windows
Somewhat of a power user of Excel since 2.0 but very, very new to Power Query/Pivot/BI.

I am beginning to shift paradigm from normalization to dimension ==> fact et al. What I predominantly work with is real property sales and subsequent analyses and reports.

I use Excel 365. My question: how big/complex should a fact table become? Is there a practical limit?
EG. analysis of real property sales requires relationship of current market value to recent sale value; current market value to time adjusted sale value; subsequent central tendencies and conclusions.
A sale fact table can become large with many, many measures (some redundant. ie. same measures for before adjustment columns repeated for after adjustment columns). Would it be best practice to break those down into separate tables with a 1:1 relationship, ie fBeforeAdjustments 1:1 fAfterAdjustments?

May be an obvious answer but, curious about best practices, ...

TIA
doco
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,543
Office Version
365, 2010
Platform
Windows, Mobile
In general it is better to have raw data in a single table as long as it well laid out (just makes it easier to pull information). Basically you are creating a database.
If the database was really huge then you would look to using/combining with something like Access or Oracle.
 

astrodon

New Member
Joined
Dec 29, 2019
Messages
3
Office Version
365, 2010, 2007
Platform
Windows
In general it is better to have raw data in a single table as long as it well laid out (just makes it easier to pull information). Basically you are creating a database.
If the database was really huge then you would look to using/combining with something like Access or Oracle.
Thank you for your response.

What I was thinking was in terms of a fact table loaded into a data model. as an example a table of sales data: date, sale price, ratio:value/price, abs dev from median ratios, among other things. Then after loading to the data model add some calculated columns: time in months, rate per mth for time adjustment, adjustment factor, adjusted price, ratio of value/adjusted price, abs dev from median ratios, etc.

Then calculation of central tendencies on the two ratios as measures: mean, geomean, weighted mean, median, stdev, avedev, and a couple others (prefixed with 'b') This would be repeated for both ratio columns - after adjustments (prefixed with a). Of course there may be a host of other measures on other columns as well. Pretty straightforward and simple. But, the fields list when pivoted gets to be pretty cumbersome. This was my question about what is or is not practical? Or is it just a matter of personal preference? Should the tables be split: before sales and after sales then join the two in the data model? ...

TIA
doco
 

Forum statistics

Threads
1,082,727
Messages
5,367,203
Members
400,948
Latest member
Handypom

Some videos you may like

This Week's Hot Topics

Top