On July 2018, Microsoft announced the availability of "Storage Mode" (still in Preview mode, not ready for Production) in Power BI, giving you the chance to decide for each table if the table will be:
- Import
- Direct Query (Live)
- Dual
This is a great option, especially, if you think that for some models:
a) You do not want to Import (copy) the whole dataset to your model because it is is too big or will add latency.
b) You need to combine Direct query (Live) data with other data (before July 2018 this was not possible, when you were using a direct query, there was no possibility to add load data)
A possible scenario could be; you have your actuals coming from a HANA System (BW, S/4, etc.) which usually are millions of records, you do not want to load this to Power BI. (Point a) ). For the actuals, you use a live (direct query) connection to HANA, but for your projections, you get the data from Excel. (Load Mode).
The schema will look:
1) Trough the direct query (live) connection you are getting the Actuals and Plan. The table is called "Hana_Live" and the storage mode is "Direct Query" as can be seen here:
3) And the last step is to combine this two tables to be able to create the report, because of the model we need to make a union; and yes it's possible to make a union with Import and direct query tables, with the standard expression: UnionCombined = union(HANA_LIve,Proyection_Excel)
Another way of showing this is:
As you can see this is a great new feature that enables a lot of new modeling options, hopefully, this becomes generally available soon.
Any experiences with this?
I also found interesting that here:
https://docs.microsoft.com/en-us/power-bi/desktop-storage-mode
It says that:
The following multidimensional sources cannot be used with composite models:
- SAP HANA
- SAP Business Warehouse
- SQL Server Analysis Services
- Power BI datasets
but enabling the option treat HANA as the relational source allows the possibility of using HANA in composite models.
