How to append data over time

[bexbissell]

Hi folks,

I have a problem that I am going round in circles trying to solve and possibly it's not doable in Power BI (according to some posts on this forum) unless I go down the incremental refresh route which I think requires a Power BI Premium licence:  

https://community.powerbi.com/t5/Power-Query/Maintain-historical-Data/m-p/1136581#M37120

https://community.powerbi.com/t5/Desktop/How-to-model-queries-in-Power-BI-for-daily-append-of-new-da...

• Every quarter we receive a MS Access database with around 10,000 rows of data – this database represents a snapshot of the market at a moment in time (given in the Config table)
• The database has a few relationships which I have simplified to show between Groups and Service Providers. In general, quarter-on-quarter, the Groups stay the same but some are created and a few are deleted. Also, some relationships between the Service Providers and Groups change, i.e. some Service Providers win business from another
• In Power BI Desktop I have successfully loaded and transformed the tables, built a star schema model with Fact and Dim tables and created a small report which works nicely
• My problem now is to append each quarter’s data to my Fact and Dim tables. I have created custom columns for composite keys called GroupKey and SvpKey and created relationships in the model using these keys which works well along the lines of:

GroupKey = Date.ToText(Table.First(#"Config")[Report Date],"yyyyMMdd")&Text.From([GroupID])

Now I want to load previous quarters for historical/trend analysis: I have tried using Append Query to append the “current” quarter query to the “previous” query but of course that only results in this quarter’s data being loaded!

I have tried to create a new table using DAX but I can’t see how to append to that table, only UNION which results in a new table, ideally I’d like to append to the fact tables (Groups, Service Providers and others) to grow them with the historical and future quarter data sets.

Any pointers would be much appreciated.

Many thanks    

Example Data

Groups
GroupID	Group Name
1	Group 1
2	Group 2
3	Group 3

Service Providers
SvpID	Company Name	GroupID	Role
100	Company A	1	Legal
211	Company B	2	Legal
432	Company C	3	Legal
215	Company D	1	Audit
326	Company E	2	Audit
326	Company E	3	Audit

Config
Reporting Date	Area
30/06/2020	Europe

[bexbissell · Accepted solution]

Thanks for your response AlexisOlsen.
I was intending to use Power BI because of its excellent transformative capability (much of the historical data we have requires massaging into a homogenous format). Also, having a full history allows for trend analysis which is a key aspect of Business Intelligence.

It would seem others have this type of requirement given the emergence of the incremental load feature.

I will re-evaluate the architecture of the solution to see if I need an intermediate layer from which Power BI would query (an ever growing) data set.
Thanks

[bexbissell · Accepted solution]

My solution architecture was missing a component or two: an OLAP DB and SSIS which I have now built (with staging, fact and dim tables). I can perform incremental refresh into the dim/fact tables using SSIS and directly integrate my Power BI reports to refresh, add measures and do any final transformation in readines for some 'data storytelling' on the full data set. Thanks for your help.  

[AlexisOlson]

If I'm understanding correctly, you want to use Power BI to store historical data that it no longer has a connection to and then append new data to that historical data (via the same connection the historical data came from). Basically, you are intending to use Power BI as a database that stores data you can update by inserting new rows.

Ignoring the incremental load capability (which is indeed similar to what you are asking for), Power BI is not intended to be used as a database store and manage data but rather a tool to load data from various sources and create models and reports. Power BI should not store large amounts of data that aren't systematically stored somewhere else.

[bexbissell · Accepted solution]

Thanks for your response AlexisOlsen.
I was intending to use Power BI because of its excellent transformative capability (much of the historical data we have requires massaging into a homogenous format). Also, having a full history allows for trend analysis which is a key aspect of Business Intelligence.

It would seem others have this type of requirement given the emergence of the incremental load feature.

I will re-evaluate the architecture of the solution to see if I need an intermediate layer from which Power BI would query (an ever growing) data set.
Thanks

[AlexisOlson]

Just for more context, the idea behind incremental refreshing is to be able to have large models that don't have to be fully refreshed every time there is new data (which wouldn't be feasible from a bandwidth perspective). Even with an incremental refresh set up, this approach usually involves a database other than Power BI that also contains all the data. It is possible to do what you are looking for with incremental refresh, but not recommended.