Forum Discussion
Power BI Gen2 ODBC
Hi team,
Need help with a scenario in Power BI. Won't require spoon feeding. Any help will be great.
I have SSAS tabular model created for one of our client where we have huge amount of snapshot data. We are currently taking snapshots of data for current month and previous on weekly basis; 13 monthly snapshots of month prior to previous month. This is happening for 3 different tables in the model. Now we are trying to leverage Gen2 power BI to host datasets on service and use composite model to leverage other prepared models to connect with this as well.
1.) How do we handle partitions in Power BI here for our data source is ODBC?
2.) Does incremental refresh work in Power BI Service?
3.) Since the data is huge, is it possible to load only metadata in Power Bi desktop, create modelling and host it in service and process/refresh the data there? Each time i try making a query change it loads he data. If i turn the option to load data off and forget to change it then data will be out of sync.
4.) If we do manage to create partition and do only incremental load by passing parameter to query, how do we delete the oldest monthly snapshot from model when first weekly snapshot for a new month is pulled in?
Thanks
Hi Anonymous
1) Through incremental refresh, the service dynamically partitions and separates the data that needs to be refreshed from the data that is less frequently refreshed.
2) Incremental refresh is supported for Power BI Premium , Power BI Premium Generation 2 is an improved and architecturally redesigned generation of Power BI Premium. So theoretically it is supported .
3)If you use ODBC driver to load data from SQL to Power BI Desktop, you can load metadata and identifies relationships in the data to Power BI .Then create report in desktop and publish to service .In service => Manage gateways , you can set gateway with ODBC datasource and refresh data form ODBC .
4)As you said that delete the oldest monthly snapshot from model when first weekly snapshot for a new month is pulled in, you can set it in Incremental refresh setting in desktop .
You can choose the time period to store the data.
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
1) Through incremental refresh, the service dynamically partitions and separates the data that needs to be refreshed from the data that is less frequently refreshed.
2) Incremental refresh is supported for Power BI Premium , Power BI Premium Generation 2 is an improved and architecturally redesigned generation of Power BI Premium. So theoretically it is supported .
3)If you use ODBC driver to load data from SQL to Power BI Desktop, you can load metadata and identifies relationships in the data to Power BI .Then create report in desktop and publish to service .In service => Manage gateways , you can set gateway with ODBC datasource and refresh data form ODBC .
4)As you said that delete the oldest monthly snapshot from model when first weekly snapshot for a new month is pulled in, you can set it in Incremental refresh setting in desktop .
You can choose the time period to store the data.
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply. I tried using the incremental refresh option. It works like a charm for incremental regular refresh. But for question 4;
4.) if we require a monthly snapshot and delete the olderst one, incrementally refreshing is just pulling in all data from the month on a monthly basis instead of one snapshot from that month. Incremental refresh i'm afraid can't handle this. Any other solution we have here?