Modeling help needed for transactional data

Hello friends, hope you are doing well. I have a typical requirement in Power BI. We are having five different transactional tables in DW and having more than ten years of data. Our auditors wanted to randomly select backdated data (i.e.2016/2015…etc.), due to the high volume, we can’t import data in PBI. I tried with a direct query in PBI, but the performance is very bad. Is there any technique to get data based on user selection at run time with a quick turnaround time?. Also, we have a premium capacity not sure how we can leverage the options. Appreciate your help.

HI @rush2bi ,

Given that you already have DW so we should prefer Direct Query instead of loading the entire data but about performance if it’s slow then you can try using Aggregation Table if they are using few fields which gives you the summary

Next option is – you can use development pipeline feature in which you just load a subset of data in Dev environment and in PRD environment you load the entire data. Make sure to use parameters to manage the changes ( Date range or the source).

Another option is - use Dataflow which is online version of Power Query for Load and Transform data. use this as source to load the data. you can use deployment pipeline here too when you want to create dataset from this as source.

Hope the above solution will help you. Just make sure how your user going to use, are they going to use once a while then it is wort to have little less performance instead of duplicating the entire data from DW. If you have your data-model well designed then there will be faster performance. That’s said it’s totally depends on your requirement and design which is best way to use these feature.

Thanks
Mukesh

3 Likes

Hi @rush2bi.

Another option might be to create multiple copies of your reports (if they’re only for auditing purposes), and have each copy only pull-in a specific year (or years). This way, the auditors can open the (small) report (with the date range of interest) without loading all data.

Hope it helps.
Greg

1 Like

Thank you Greg & Mukesh for the valuable details. I will explore the options and let you all know my feedback regarding this design issue.

Thanks,
Kumar

Hi @rush2bi

Based on your requirements I think the best solution will be to use Power Query Filter Toggler as explained by Imke Feldmann.

Can create two parameters one for Date and other for toggle. In example in above Post, Date Parameter will replace Static order number in Filter condition and toggle will be same as Filter Toggle.

Once data is loaded and file is saved, parameters can be changed from Home page in Power BI Deskop or scheduled refresh in Power BI service.

Benefit of this is the same conditions can be applied on multiple fact tables as in your case.

Do check this out and let me know if it is helpful.

Thanks
Ankit J

1 Like

Thank you Ankit. I will check filter toggle option and let you know my feedback. Thanks for sharing good info.

Hi @rush2bi, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @rush2bi, we’ve noticed that no response has been received from you since the 7th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @rush2bi, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!