Dataset creation in DirectQuery mode and the Snowflake data source

[mark_ledwich]

I am trying to use the API to create a dataset with relationships and measures with a direct query connection to snowflake.

However i have reached different limitations with both th epublic and embedded API's that prevent this.

Public API: Allows the full features dataset creation, but not in DirectQuery Mode. Also unsure how to create connection strings for other types of data sources like snowflake

Embedded API: Doesn't allow the new dataset creation. And only supports direct query for Azure SQL/DW sources.

Question 

1. Is my understanding correct? Can i do any of this now?
   
   When can i expect
2.  embedded api to have the new dataset creation capability? 
3. support for  direct query with dataset creation?
4.  support for creating/updating snowflake connections using the api ?

Thanks!

[Eric_Zhang]

@mark_ledwich

The dataset creation API is also very limited. You can only to add rows to the dataset created via REST API, no update/delete, no direct query connection set. That is to say, you maintain the dataset totally via REST API. One scenario would be like , read the data from snowflake in C# via the snowflake API, add the rows to the dataset via Power BI REST API. Meanwhile, you can only generate visuals in Power BI Service against the dataset, there's no various operations as in Power BI Desktop, such as data type converison, measure creation, re-model the dataset etc.

And, Power BI Embedded doesn't support any DirectQuery in data source other than Azure SQL DB/Azure SQL DW.

Usually, a promising approach would be like, connect to the snowflake in direct query mode, and then publish the report to Power BI service, set up a gateway and configure data source in Power BI Service. Then embed the reports to your application via Power BI REST API. However, the snowflake is still in preview and it is not supported in gateway data source. To bypass this limitation, you may consider using ODBC to snowflake, and schedule refresh in Power BI Service, which means, DirectQuery is not avaible. To be frank, I don't expect too much on an preview feature.
 

[mark_ledwich]

"One scenario would be like , read the data from snowflake in C# via the snowflake API, add the rows to the dataset via Power BI REST API"

@Eric_Zhang 

Thanks. I am doing that for the moment.

Do you know the likelyhood of those additional features of the API and DirectQuery (i.e. are they short term priority and are likely to land in the next 4 months?). Using the desktop feature is ok for prorotyping but I am creating something that needs to deployed and manage a dimensional model without human interaction.

[Eric_Zhang · Accepted solution]

@mark_ledwich

That scenario can be the only approach, to implement "no human interaction", you could schedule your C# script to run in a certain interval. When adding rows, do note the API limitation.

I have no idea on any ETA, just keep one eye on the developer blog.

[mark_ledwich]

I want to use the API to create a dataset (with relationships, aggregation options, measures etc..) in direct query mode to the SnowFlake data source.

This is my understanding of current limitations of API's that prevent me from doing this:

Embedded:

• Only supports the simpler dataset creation (just tables and columns). 
• Allows import of a model in direct query. But only can update them for Azure SQL/DW (not snowflake) as mention in the FAQ

Public:

• Supports the new featured dataset creation but thre is no way to modify the data source to be in Direct Query mode or specifiy the connection string.

Questions

• is my understanding correct?
• when will the API's support data set creation in direct query mode
• when will the snowflake data source be supported for creation/modificaiton in the API

Thanks!

Mark