Issues Using Perspectives with Snowflake and DirectQuery

[topdawn8]

I have a very simple use-case where I want users to be able to connect to perspectives in a model created with Power BI Desktop with a connection to Snowflake in Direct Query mode. The following setup is only to test issues we were seeing with using perspectives like you would use in a regular Tabular or Multidimensional model--the data has no functional use-case.

Our end goal is to have a large DirectQuery model and expose fit-for-purpose parts of the model for given subject areas via perspectives to help simplify the user-experience.

My setup is:

1. Create a connection to Snowflake from Power BI Desktop (using the Snowflake connector)
2. Import the metadata of two calendar date dimensions in DirectQuery Mode
   1. DIM_CALENDAR_DATE (physical table)
   2. CALENDAR_DATE (a view created on top of DIM_CALENDAR_DATE)
3. Use Tabular Editor 2 to create a perspective named "PerspectiveTest"
4. Map DIM_CALENDAR_DATE to this perspective
5. Save this change back to the Power BI Desktop

My Executed Use-Case is:

1. Open SSMS
2. Connect to the local Power BI Desktop Model (localhost:xxxxx as identified using DAX Studio)
   1. Advanced Properties: cube=model
3. Run the following DAX:
   1. EVALUATE VALUES('DIM_CALENDAR_DATE'[WEEKDAY_IND])
   2. Observe successful completion
   3. 
   4. Observe successful execution in Snowflake history
4. Connect to the local Power BI Desktop Model (localhost:xxxxx as identified using DAX Studio)
   1. Advanced Properties: cube=PerspectiveTest
5. Run the same DAX
   1. Observe error: "OLE DB or ODBC Error: The command 'SELECT TOP (1000001) ...' isn't supported
   2. 
   3. Note: SELCT TOP is not supported in Snowflake
   4. Observe no indicator a query was sent to Snowflake

Several other variations of this scenario have been ran:

1. Using MDX instead of DAX (same results)
2. Connecting to the perspective and main model using Power BI, using the Analysis Services connector and directly connecting to the perspective (Same Results)
3. Deploying the model to Power BI Services and connecting with SSMS or Power BI (Same Results)
4. Same executed use-case as above, but on an import model (SUCCESSFUL)
   1. Supports the error is not a function of using a perspective

It appears when connected to the full model, the SQL being generated through the embedded ODBC Driver in Power BI is correct:

SELECT "WEEKDAY_IND"

FROM "<database>"."<schema>"."DIM_CALENDAR_DATE"

GROUP BY "WEEKDAY_IND"

LIMIT 1000001 OFFSET 0

But when using the perspective on the exact same table, it decides to write it in SQL Server syntax (using TOP N). Has anyone ran into this type of issue before?

[topdawn8]

Thanks, @v-chenwuz-msft. 

I don't think we're fighting compatibility with VALUES() in DirectQuery mode as it works when using the core 'model' (and I use VALUES() in several other DAX scripts using DirectQuery against Snowflake). I didn't realize you get support with a pro license, so I did create a ticket and that team is looking at the diagnostic logs. I'll update this thread with any findings from that effort.

[v-chenwuz-msft]

Hi @topdawn8 ,

This should be a limitation of DirectQuery. There are some modeling capabilities that aren't available, or are limited, when using DirectQuery. The limitations are generally applied to avoid performance issues.  Many dax are not support in DirectQuery model.

For more details about the limitaion of DirectQuery please refer this official article.

Using DirectQuery in Power BI - Power BI | Microsoft Docs

Values() 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.