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?
Relationships, Power Pivot vs Access
-
- Posts: 1067
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Relationships, Power Pivot vs Access
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.
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.

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: Relationships, Power Pivot vs Access
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.
I appreciate the short answer and will keep an eye out for an upcoming article.
Who is online
Users browsing this forum: No registered users and 2 guests