Scribd
Upload
12 views1 page

14592-OCR LT

The document outlines a strategy to optimize Power BI queries to reduce LS DB loading by targeting specific data and minimizing dataset updates. It proposes adding a BrokerageFirmId field to views and using a Master Report with a shared dataset for multiple reports within the same Workspace. The estimated implementation time for these changes ranges from 6 to 40 hours, and the approach aims to enhance efficiency and allow for more scheduled refreshes of datasets.

Uploaded by

vkscribdind
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views1 page

14592-OCR LT

The document outlines a strategy to optimize Power BI queries to reduce LS DB loading by targeting specific data and minimizing dataset updates. It proposes adding a BrokerageFirmId field to views and using a Master Report with a shared dataset for multiple reports within the same Workspace. The estimated implementation time for these changes ranges from 6 to 40 hours, and the approach aims to enhance efficiency and allow for more scheduled refreshes of datasets.

Uploaded by

vkscribdind
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

The main idea is to optimize Power BI queries to decrease LS DB loading.

To reach this we can


1) Reduce getting not targeted data from DB
2) Reduce number of updates of BI datasets

Proposed Solution:

1) To reduce getting not targeted data from DB we can add BrokerageFirmId field to each view we use in BI. Then we
need to add Parameter in PowerBI Desktop and apply filter for each view. It will build SQL queries like:
SELECT
Field1,
Feild2,
…
Fieldn
FROM our.Awesome_View
WHERE BrokerageFirmId = @OurBIParameter
So we can separate datasets by brokerage firms
2) To reduce number of updates of BI datasets we can have only one Master Report with real dataset, but rest reports of
agency can use published Master Dataset. It can be implemented only within the same Workspace. So, for instance, we
can have full Willis report with dataset. And rest reports Bain, Comvest etc. can use published Master Dataset.
However it requires placing all Willis reports within ONE Workspace.

Estimation:

Working scope Estimation (hours) Comments


Adding BrokerageFirmId to all BI views 6-8 To target data by brokerage agency
Adding BrokerageFirmId parameter to 3-5 To target data by brokerage agency
BI Report and applying to all views in BI
report

Migrating all existing reports to use one 4-8 Implies creating a new Workspace, publishing Master
Master dataset report, migrating and publishing rest reports of agency
Changing LS <-> BI integration 32-40 Now we have configuration for brokerage agencies and
implementation + settings based on brokers that follows ONE Workspace ONE Report rule. So
report not workspace level to use Power BI dataset service we need to change it and
to implement settings based on reports.
Migration existing employee/brokerage ? We can try to migrate all existing Power BI settings. E.g. if
settings someone has an access to Bain workspace we can try to
migrate this configuration to use Willis workspace and Bain
report inside.

It can significantly decrease DB loading. Also we can setup up to 48 scheduled refreshes for datasets, because data will be
targeted and we will have only one dataset for a brokerage agency (we have 5 for Willis now that are refreshed
independently). We can use Power BI Embedded more efficiently (now, 5 Willis reports get all DB data that is equal 5 separate
agencies). Also there is Incremental update policy approach in Preview, so we can keep in mind and use it in future.

You might also like