Duplicating Queries is taking a long time.

[Anonymous]

Hi Community,

We are reporting on Enablon Synergy data for Health and Safety.

Since Power BI can't directly report on Enablon Synergy, I had to get business to export data in Excel (.xlsx) format onto SharePoint Online Site to be able to report in Power BI.

Since the exporting from Synergy was limited to only 48 Columns, the business had to do 4 Excel File exports.

I have merged all these 4 Excel files in Power BI and now have a Final Query with a total of 182 Columns. I have finished preparing the data in this Final Query called Synergy per the 1st screenshot. The total number of rows/records in the Synergy Final Query is only 2174 rows.

And now I am breaking this Synergy Query into many small queries by duplicating this Synergy Query (which is taking a long long time) to break this Query into 35 Smaller Queries to do Star Schema. This could be because of the heavy M Language that's been created as part of my Data Preparation activity in 2nd screenshot. I am breaking them based on the Enablon Synergy data fields (luckily they're so nicely categorized in the source) as per the 3rd screenshot.

FYI - I am working on a brand new HP ZBook Studio x360 (Intel Core i7-8750H CPU @ 2.20GHz 2.21 GHz Processor, 32.0 GB RAM, 64 Bit Windows 10 Enterprise OS). So, this is more than enough for any Power BI Development activity I guess.

I have tried to Disable both 'Enable load & Include in report refresh' but still the performance is very slow. For each duplicate query, it takes around 10 - 15 minutes and then to delete the unwanted columns from this new query takes another 10 - 15 minutes and then to rename this new query with a new name takes another 5 minutes.

Attributing the poor performance to my Excel Files being on SharePoint Online Site due to network issues, I've now copied all the 4 Synergy Source Excel Files onto my local drive and the performance is still slow (although a bit faster than when the source files were on SharePoint).

One other thing I thought about is probably, each duplicate query is redoing a whole lot of M language! The coding is here...https://drive.google.com/open?id=1ExdVGNVGb7UYo_KxjBDDRQWVCuGnm_z2iH8JJfoTHCU 

Am I doing something fundamentally wrong here, I got the idea of duplicating the main query into multiple to create smaller dimension tables from Guy in a Cube video @ Power BI Tutorial | From Flat File To Data Model

How would I be able to create duplicate quickly so that I can concentrate on doing the Star Schema to finish the data model for this project?

Or do I have to live with this until I finish creating all the 35 Queries, each with the required number of columns (not more than 2 - 7 columns in each) and then this slowness is no more an issue!

I've also attached screenshots of my Task Manager for your reference.

Any help with screenshots or guidelines would be of great help to me and others in a similar situation.

Hoping to hear soon.

Thanks in advance for your time.

Regards,

Chandu

[MKDK]

Hi,

did you find any better way to export data from Enablon, or are you still using Excel? If you found a better way, could you share your experiences?

There is this similar post about merging columns and source is Enablon.

[Greg_Deckler · Accepted solution]

Are you truly duplicating your query versus using a Reference? You might get faster results with using a Reference.

[Anonymous]

@Greg_Deckler 

Hi Greg

You're absolutely right. I'm now using Reference Queries and it is a big difference.

Duplicate Query was creating the whole query while I only needed a few columns in the subsequent queries to create smaller tables to contain subsets of data and then to create relationships using Star Schema.

Thanks a lot for the heads up!

Regards,

Chandu

[Greg_Deckler]

Yep, unless I am very much mistaken, that way you are loading the data once versus multiple times. Makes a huge difference especially on large datasets!!

[Anonymous]

@Greg_DecklerAbsolutely, can't agree more! Cheers