Append Other PBIX data set with existing queries

[ykannan]

Hi Friends,

I have 3 Oracle Materialized Views as data sources.

1. Historical sales -  Sales Data which will not change ever. Ex:2003 to 2019

2. Delta_sales  - Year Sales Data will get add every year (Query to get difference in sales years - now, 2020) 

3. Sales_last_2Yr_current_yr - (last2 years + current Yr sales) 2021, 2022 and 2023

when 2024 comes,

2020 and 2021 will be in Delta_sales.

2022, 2023 and 2024 will be in 3rd set.

2003 to 2019 sales have millions of data in Oracle. We have Pro license and use Import model. The pbix file exceeds 10GB when have all data.

So trying to keep 2003 to 2019 (in workspace) say 6GB, rest other sales 2 GB. For 1 yr,  we want to sustain with this model until we move to Datmart.

I never want to refresh 2003 to 2019, just one time when deploy. Other will be in Scheduled refresh.

Import 2003 to 2019 as as Data source, refer below, and append to Delta and lastYr_current_yr sales, so we can show 2003 to 2023 data. But cannot able to append in this way.  (Excel sources used for sample)

Questions:

1. Is there a way to combine all data? 

2. Is ther any other suggestion to acheive this need in import model ?

3. Near future, we will not go for premium. So 10GB limitation in workspace is an issue

4. If we add 2003 to 2019 in dataflow and import the dataflow, it increases the PBIX file size however

Please suggest best solution to sustain for an year to sustain in this way. We are working to implement Datamart or anyother cloud concepts or go to premium later next year.

[ykannan]

Thanks @MFelix  for you time to look into this.

You are right. My issue is 10GB limit in PRO workspace.

My idea is to just append Historical data to Current data. So we dont need to chage any existing measures and relations.

Just a brief of our Architecture,

1. We have Production user where all our Models and reports exist.

2.  We have sales reports for management, VP, AD in respective workspaces and released to users as App.

3.  currently we move import type PBIX which has data and measures to all non-prod environments prior to production. There are limitation in Souce control to have max of 5GB file.  So PowerBI team contacts DB team to delete data in Development environment before check in the PBIX. After Test environment deployment, will get data from Test DB through service refresh. 

4. With more data now, we need to keep to PBIX files, 1 with just data and relation and other PBIX with that dataset keep the measures. So any change we move only second PBIX.

Question:

1. If we upgrade our production user account to PPU where as rest consuming report workspaces in PRO, would this work? Any other limitation in this approach?

2. Is there a way in PBIX or other support tools like Tabular editor, can we truncate data instead of truncatiing in database Materialized view (compiled view which holds data)?  Because by bringing 16yrs data need to delete more in DB view. Instead if we have option in Power BI, No DB team need to involve.

3. Thought of keeping Historic data in Data flow, but when I import dataflow in PBIX, same file size increase will happen after refresh. Changing to Direct mode have lot of restrictions in exsting measures and performace issue will come. Any suggestion, how others handling ?

We welcome, any other best solution (may be totally new approach) for the scenario. 

Thanks,

Yoga

[MFelix · Accepted solution]

HI @ykannan ,

Sorry for the delay, here are some toughs on your questions:

1. The usage of dataset trought the several type of licenses is possible especially when you refer to premium and PPU. On the option you refer that can't be done  meanig that a information shared in PPU cannot be access by pro (https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-per-user-faq) and the table below that allows you to see the full combination between sharing and consuming information for different licenses.

2. Believe the option you refer is the incremental refresh that will allow to store the data based on date filters and have different partitions that aren't refresh and others that are:

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

3. Believe this option is also solved with the incremental since you can have your incremental with only a few lines of data, keeping the PBIX smal and then on the service when you do the refresh the refresh will be done.

[ykannan]

Hi @MicrosoftLC ,
I amo not sure how to contact microsoft in this community. I am looking for answer to my questions.

[MFelix]

Hi @ykannan

Have you tried incremental refresh this allows to have the save of the dates withouth impacting the refresh of the full dataset.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

[ykannan]

Thanks. Our main problem is size of PBIX file with in a PRO workspace for more number of years. 

Currently we have 3 Years data for the reports. We want to bring from 2003.

[MFelix]

Hi @ykannan 

Has you refer the Pro has a limit of 10GB, per workspace., while PPU has a 100 TB limit (but this you already know :D)

Depending on the way the model is build, and without a lot of informations but the ones you refer above one option can be tyo have a composite model. don't know how many facts you have but you can have a model with the historic values and then have another with the current.

Only disavantage is that you would need to have your measures split between the two table something like:

Total Sales = SUM(Historic[Sales total) + SUM(Current[Sales Total])

Then adding the several dimensions would do the filter on both table and combining them together.

Again just trowing some suggestion since don't know all the details of your setup and possible size of the model.