Best Practices: Fact Tables & Measures

[astrodon]

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]

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]

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