Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to Write data into a data flow every day ?

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 

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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
  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    lbendlin
    Super User
    5 years ago

    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.