Basic Modeling Questions (New User, Inherited Model)

[bgatzemeyer]

I am brand new to Power BI but not new to BI design and implementation.  I just started to work for a company that had hired a 3rd party to do data modeling and dashboard creation for them.  

I gained access to the system and have some immediate questions I was hoping the community could help me with as I learn the difference for Power BI vs other BI software.  

1. The table in the picture to the far left is all the measures from the Fact Tables within the report.  Is that normal? What benefit is there to having them in a stand alone table rather than in the appropriate data source?  Also they did each measure with TY (this year) and LY (last year) as two seperate measures within this table, this seems wildly inefficent and problematic.  

2. They have direct connection to all the files instead of import, a quick QA session on some of the designed dashboards shows poor load times and delays.  Any reason the data wouldn't be better severed imported?  (size is not an obstical)

3. This structure has 14 Dimension Tables and 11 Fact Tables; I see no reason the majority of those couldn't be combined (especially on the Fact tables as they have identical source and format).  Cutting this down to 6 Dimensions and 5 fact tables, also allows me to connect everything far more efficiently and be scalable, what am I missing?

Appreciate any help you can give, I don't want to start the new job kicking down others work before I better understand it!!!

[Greg_Deckler · Accepted solution]

@bgatzemeyer Yeah, that looks like a train wreck. Having measures in their own separate table is not unusual and some would consider it a best practice. In terms of TY and LY measures. Not unusual but could potentially be simplified through the use of calculation groups. However, that requires external tools like Tabular Editor. If data model size is no issue and refresh times are no issue, then absolutely, import mode will perform better than DirectQuery. I cannot speak absolutely 100% to the last point. There may or may not be reasons for how the data model was designed including things like RLS or who really knows what, maybe scenario dimensions? Like having multiple date dimensions based upon different fact table dates like Order Date, Delivery Date, etc? In general though, the closer you can get to a star schema and less tables the better. You might consider breaking this report into multiple reports. Even 5 fact tables is a lot of fact tables. It may be that you can't and all 5 fact tables are required though.