Forum Discussion
Data modeling: dim/facts or views?
Hello!
I have a huge doubt with regards of connecting PowerBI (in import mode) to my database.
I've create a new datawarehouse and I've created it with a dimensional model (i.e. facts and dimension tables) instead of importing my flat data. Why? Because today the reporting tool of choice is PowerBI and the data is below 2GB: tomorrow we might move to direct query due to size (and thus move from our current MySQL to a RedShift or else) or might even use a different tool, so having a full dimensionally-correct structure looked to me more future-proof.
The dataset is made of three fact tables and 26 dimension tables.
Now I'm connecting PowerBI and I have a doubt. Should I connect PowerBI to all the tables (facts and dims) and let PowerBI manage the relationships OR create three views (because there are three main facts) in db itself and connect PowerBi to the views?
I'm really not sure what is the best option.
CONNECTING TO DIM/FACTS
+ seems the natural way to go
+ I can manage relationships in the tool
+ seems more future-proof
- ...I have to manage the relationships in the tool, having to choose direction of joins, having to use USERELATIONSHIPS when I have role-playing of date dimensions
- makes the creation of dashboards messy because I have like 20 dims tables
CONNECTING TO VIEWS
+ easier as joins are in the DB and it's just three tables
+ I can manage role playing easily
- ..feels like having spent 3 weeks making a fully dimensional model a waste of time
What is the best course of action in your opinion?
Thanks
- Anonymous8 years ago
Well, I think this answer my questions: https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/
2 Replies
- AnonymousNot applicable
Well, I think this answer my questions: https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/
- v-frfei-msft
Community Support
Hi Anonymous,
Yes, refering to the blog you shared, we can save time and improve usability and performance. kindly mark your answer as a solution to close the case please.
Regards,
Frank