Relationships, Power Pivot vs Access

Anything related to PowerPivot and DAX Formuale
tlh
Posts: 2
Joined: Tue Oct 08, 2019 5:39 am

Relationships, Power Pivot vs Access

Postby tlh » Tue Oct 08, 2019 5:45 am

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
Posts: 1067
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Relationships, Power Pivot vs Access

Postby MattAllington » Tue Oct 08, 2019 7:11 pm

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

tlh
Posts: 2
Joined: Tue Oct 08, 2019 5:39 am

Re: Relationships, Power Pivot vs Access

Postby tlh » Wed Oct 09, 2019 11:36 am

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.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests