How to Write data into a data flow every day ?

[Anonymous]

Hi Community, 

I need some help regarding a project (Snap shot in time ) i am handling .  I have a report that refreshes every day at 04:00 AM EST . 90 % of the data in this report comes from a SQL data source A  .  10% of data comes from a different SQL data source B.  Data source B gets the data loaded into one of the tables after the queries runs at 03:30 AM EST .   Every day a SQL Job runs the below Insert query at 03:30 AM EST and insert information into one of the tables of Data source B . 

Insert Query : 

insert into T8375_SRV_MGT_DAL_AST_CNT

select getdate()-1 as ADD_DATE,

(select [T8374_AST_TYP_CD] from [dbo].[T8374_SRV_MGT_AST_TYP] where [T8374_AST_TYP_CD] = 1 ) as asset_type, --asset type application. change to 2 for devices

(select count(distinct Instance_ID)
from ast_application BMCAP
where Data_set_id = 'BMC.Asset' and (AssetLifecycleStatus = 9 or AssetLifecycleStatus = 3)) as active_cnt

OutPut : Every day a new row of data should be added . 

This process is working fine. However, instead of having a second data source B for that 10% of information into Power BI report , I would like to know if we can do it in a different way using data flows or any other Power BI related tools  so that we can avoid a second SQL data source B . 

Please assist me with the problem i am facing . Looking forward for a solution . 

Thanks,

G Venkatesh 

[lbendlin]

I am not saying it is a good idea. I am saying it is mandatory. If you don't have the resources for that then that is unfortunate, but Power BI cannot help you in that situation.

[lbendlin]

Keep in mind that Power BI has no guaranteed memory, not even for dataflows.  Any historical/snapshot data storage needs to be done at the data source if you need recoverability.

[Anonymous]

Thanks Ibendlin for the reply. 

Yeah Thats a good idea to develop snapshots in time based on data stored at data source level itself . However, we do not have enough resources to write back data to data source for historical use. 

G venkatesh 

[lbendlin]

Are you solving an actual problem or are you only curious about other options?

https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

[Anonymous]

@lbendlin ,

Thanks for the reply. I am trying to solve an actual problem itself.  I followed the steps that are in the link you have provided.  What i see is the previous day's data is being replaced with new data ,however new day's data is not being added . Not sure if i am making any mistake . below is the M code i have tried. 

let

  Source = Sql.Database("mssqlremrptprd1", "ARsystem", [Query = "select count(distinct Instance_ID) 'App Count',getdate() 'Refresh Date'#(lf)from  ast_application BMCAP#(lf)where Data_set_id = 'BMC.Asset' and (AssetLifecycleStatus = 9 or AssetLifecycleStatus = 3) #(lf)"]),

  #"Query-526566726573682044617465-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([#"Refresh Date"]) >= RangeStart and DateTime.From([#"Refresh Date"]) < RangeEnd)

in

  #"Query-526566726573682044617465-autogenerated_for_incremental_refresh"

Expected output : 

App Count     Refresh Date 

1652                4/12/2021

1654                4/13/2021

1659                4/14/2021

Thanks,

G Venkatesh