Fat and Slow Golden Dataset Situation How Could Dataflows and Datamarts Help?

[cerebraino]

Hello, at work we use a "golden" dataset containing all tables and measures, management does not want Power Bi developers like me creating stand-alone datasets and measures because in the past that has led to inconsistent reporting, for instance sales Q2 being differently calculated from one report to another as ways to calculate stuff often changes due to nature of business. That has caused the single dataset to constantly grow and become very slow, especially during development we have to wait about 4 minutes for the "spinning wheel" to stop, that's productivity-killing. Also, we developers have to take turns downloading the main file, making changes and uploading it, whilst the other developers wait for their turn, biting their nails as deadline approaches. This is an unsustainable situation.
We are analyzing using dataflows to solve "part" of the problem, taking out the ETL steps out of the PBIX will probably make it lighter but the main problem we have is not refreshing time is "development time" (BTW we hear a lot about debugging refresh time and visualization time but little to none about how to improve "development -waiting- time", any advice?) therefore we are not optimistic dataflows will make much for us, well probably if we use direct query to pull data from some large tables instead of loading them always via import mode that would help releasing the file from having to carry seldom used fat tables, do you think it will?
Anyway the question is about datamarts as they contain the modeling part dataflows do not, that sounds promising but in that case my question is how changes will be managed? for instance right now one developer downloads the main file and make changes in the model, adds measures and uploads the file again, also keeps a backup in case we need to roll-back the changes.
The question is how "rolling back" changes would work in the case of using datamarts? for instance if a model change done in datamart turn out to be an error. Currently using datasets we just upload the backup and we are back to business, how would that work using datamarts?

Thanks in advance for any comment or advice!

Juan

[Tutu_in_YYC]

Im having the same challenge with a client's golden dataset. Our case is we cant "remodel" the dataset to answer new questions and requirements, so we ended up with DAX calculations that are complex and time consuming.. The idea of Golden dataset is great, but it has it's challenges, especially if you want to create a optimized data model for fast report.

I dont have any input on your challenges, but one thing that you can try if you want to improve "development-waiting-time" is to use the ALM toolkit to perform data model updates eg. addding measures etc. Just be aware that its still limited.

[cerebraino]

Hi @Tutu thsnks for yoru answer and recommendation to use ALM toolkit, actually I am using Tabular editor for making changes in the model and have to update just once instead of each time, I will take a look at ALM toolkit. 

I totally relate to your comment about fat / innefficient / wrongly structured data model leading to complex DAX, in my case I would oike to add the fragile "house of cards" situation, often old reports stop working because slight changes in the data model, for instance two tables actually connected several times indirectly trough other tables, rendering the model unstable and often unpredictable.

Seems we share similar challenges maybe we could remain in touch sharing different solution/improvement approaches. Thanks again!

Juan

[Tutu_in_YYC · Accepted solution]

For sure! Another client is doing intense chaining with mixed storage model, i.e direct query an existing dataset (golden dataset) and add additional table using other data sources. It is more complex as they propragate.

[GilbertQ]

Hi @cerebraino 

A golden dataset is a good approach. But in my experience a dataset should only relate to a single question that is trying to be answered.

For example HR data should be a single dataset. Finance should be a single dataset, Sales should yet be a single dataset. This allows for it to be broken into specific business units with related tables.

With regards to datamarts right now it is still very new and I would not recommend using it for production loads. It will get better and hopefully have more options as time goes on.

Also make sure that each dataset is designed using a star schema with 1 fact table per business area and directly related dimension tables

[cerebraino]

Thanks for the suggestion about having different datasets for each "business area", instead of one , however my boss insists in just having one for all possible scenarios and questions, I will try to convince him to "split the sacred cow", thanks again! Juan

[GilbertQ]

Yeah I know it might be diffucult now, but it will save hours of time, effort and slow reports. 

It can always be done, it is often just getting the time to get it done!