[Issue] Unable to save model with Hybrid mode.

[Koushikrishnan]

Describe the issue Hello. We are currently working on a POC on one of our Power BI datasets to use Hybrid Data mode. This is what we are trying to achieve:

1. All our data is spread across Snowflake and Databricks ADLS.
2. We want to pull the data for our Fact for the current year using Import Mode, with data coming from Databricks ADLS. The data is stored in parquets.
3. For all years prior, we want to pull data from Snowflake using Direct Query Mode.

When trying to do this, we are unable to save the model as it throws up an error. Some screenshots are below.

2022 - Direct Query - Snowflake Data

2023 - Import - DBX ADLS Parquet

After updating these and trying to save the model, we get this error:

Also noticed a mode property on the table itself which has "Hybrid" as an option but we are never able to select that (doesn't allow selection of that option at all)

Is the way to fix this? Are we doing something incorrect here? Any assistance or suggestion on this will be very helpful to solve our problem.

Please let me know if you need more information to look into this issue deeper. Thank you!

Which version are you currently using? Tabular Editor 3, version: 3.3.8 Tabular Editor 3 edition: Enterprise

To Reproduce The screenshots should help, can't really figure out why if the data sources aren't setup the way we need.

Expected behavior Should allow Hybrid mode selection and save the model.

Screenshots Screenshots in the description.

Desktop (please complete the following information): Windows 10.

Additional context Add any other context about the problem here.

[DBojsen]

Hi @Koushikrishnan

What you are trying to build is called a Composite model (mixing import and DirectQuery), and this is only possible on Power BI datasets. It seems you are either saving to a workspace database that is a local SSAS server or trying to deploy to a SSAS server. Neither will work, as this is only supported on Power BI.

You can put your workspace on Power BI - just connect to Power BI when starting your development instead of connecting to SSAS.

I hope this helps - please let me know if you are able to fix or not.

BR David - TE3 Support

[Koushikrishnan]

Thanks for the response @DBojsen. But the dataset I was trying was in fact a Power BI dataset and was never an SSAS dataset earlier. We are trying to improve the performance of our Power BI datasets by making them hybrid and this was the first model we chose for our POC. If you need more details around the dataset, data source, please let me know.

[otykier]

Hi @Koushikrishnan

What you're describing should theoreticaly be possible, although I am not sure if Power BI supports having two different sources (ADLS+Snowflake) making up the partitions of the same table. In any case, the error message you're seeing, indicates that the partition/table properties have not been set up correctly, and unfortunately, the documentation from Microsoft is a bit lacking in this area.

You may have more luck starting with a table created in Power BI Desktop with DirectQuery against Snowflake, and then manually adding the imported partition through Tabular Editor.

I found this blog post (check out scenario #2), which describes the process: https://towardsdatascience.com/hybrid-tables-in-power-bi-the-ultimate-guide-bfe07d480275

Make sure to take note of the limitation mentioned in that article, specifically, that any DAX query that needs data from the table, will cause a DirectQuery SQL to be executed against Snowflake even if no data from that partition is needed by the original query.

For this reason, it might be better to keep the data in two separate tables (one pure import, one pure DirectQuery), and then use DAX to determine when data from one or either would be needed. Something like:

IF(
    MIN(Calendar[Year]) < 2020,
    SUM(ColdFact[SalesAmount])
)
+ SUM(HotFact[SalesAmount])

In the above example, ColdFact is the table that uses DQ from Snowflake, while HotFact is the imported one. If the Calendar table has a filter, such that only data from 2020 (or newer) is requested, we only use data from HotFact, and in this case no DirectQuery SQL should (theoretically) be generated. However, if the filter on Calendar includes 2019 (or older), then we will fetch data from both ColdFact (DirectQuery) and HotFact (Import).

Of course you will have to test to see if this solution works for you.

[Koushikrishnan]

Thank you for your feedback and suggestions @otykier! I was able to get the Hybrid mode with SF DQ Partition + DBX Import Partition to work. The only thing I figured out was blocking me to save the model was the Compatibilty level of the PBI model which needed to be changed to 1565. I was able to get this setup via TE3, publish and refresh the model without issues after that. But as you mentioned, the DQ option (SQL to Snowflake) impacted the overall performance of the model and it got very slow. Probably we need to try the 2-table approach as you've mentioned but again for a model with 1000+ measures, we are worried as to how it will impact the overall page-level performance. We will discuss and take a decision on this. Thanks again for your help!