Relationships, Power Pivot vs Access

[tlh]

I've been using Power Pivot/Power Query to simplify a rat's nest of data in many spreadsheets and expand our reporting capabilities with DAX.

Some of our data will soon be stored in an Access database.

When possible is the preferred method to data table relationships within Access or continue doing it within the data model?

[MattAllington]

These relationships are not a 1 for one substitute although I understand why you are asking.

In short, you should prefer a star schema for the data loaded in power pivot. Read here https://exceleratorbi.com.au/the-optima ... ivot-data/

You should not be aiming to simply replicate relationships in one or the other.

If creating relationships in Access help with building the star schema, then do it.

That’s the short answer, anyway. I may write a blog about this later (you are the second person to ask in the last few weeks.

[tlh]

Your Optimal Shape article is one I read early on in my power pivot awakening.

I appreciate the short answer and will keep an eye out for an upcoming article.