powerbidaxdata-analysispowerbi-desktopdata-modeling

What data type is appropriate for star schema keys (integer or text)?

I have inherited a star schema based report.

However the keys (links) are text fields. Not numeric ids.

For example:

The DimClient has values like:

Client Name
JLR
Mercedes
Audi
Volvo

And the FactSales has values link:

id date       client value
1  01-01-2024 JLR    50000
2  05-01-2024 Audi   40000

Is this bad practice?

Is it recommended to handle this by adding an index column to the dim, then joining the fact to the dim on client name and fetching the id from the dim, followed by removing the client name from the fact table?

Solution

  • No, it's fine as text keys. VertiPaq uses dictionary encoding for things like this.

    Reporting databases do not follow the same design patterns as transactional databases and it is fine to have things like denormalisation etc (recommended even).