Forum Discussion

sresan477's avatar
sresan477
Frequent Visitor
1 year ago
Solved

Load data from snowflake into Semantic Model using TOM and C#

I am trying to load data into a semantic model in C# using the TOM model in import mode. I have tried these 2 approaches so far and both have resulted in the table being created with no data: 1. O...
Need Help
  • sresan477's avatar
    sresan477
    1 year ago

    Wanted to update the thread with a solution that worked for me. I followed this guide: https://www.azureguru.net/autogenerate-semantic-model-using-tabular-object-model-for-a-fabric-workspace
    which was helpful in resolving my problem. 

    These are the M expressions I used for Snowflake and Databricks in conjunction with the above article:

    // your inputs
string workspaceHost = "host.azuredatabricks.net"; // replace accordingly if google or aws
string warehouseId   = "/sql/1.0/warehouses/warehouse_ID";
string catalogName   = "health_care_sanity_payment";
string databaseName  = "payment_info";
string schemaName    = "payment_info";
string tableName     = "payments";

// build the M expression with interpolation
string mExpression = $@"
let
    host      = ""{workspaceHost}"",
    warehouse = ""{warehouseId}"",
    catalog   = ""{catalogName}"",
    database  = ""{databaseName}"",
    schema    = ""{schemaName}"",
    table     = ""{tableName}"",

    Source       = Databricks.Catalogs(host, warehouse, [Catalog = catalog, Database = database]),
    payment_data = Source{{[Item=table, Schema=schema, Catalog=catalog]}}[Data]
in
    payment_data";
    // your inputs
string account   = "your_account_identifier.region.snowflakecomputing.com";
string warehouse = "YOUR_WAREHOUSE_NAME";
string database  = "YOUR_DATABASE_NAME";
string schema    = "YOUR_SCHEMA_NAME";
string table     = "YOUR_TABLE_NAME";

string mExpression = $@"
let
    Source             = Snowflake.Databases(""{account}"", ""{warehouse}"", [Implementation = ""2.0""]),
    {database}_Database = Source{{[Name = ""{database}"", Kind = ""Database""]}}[Data],
    {schema}_Schema     = {database}_Database{{[Name = ""{schema}"", Kind = ""Schema""]}}[Data],
    {table}_Table       = {schema}_Schema{{[Name = ""{table}"", Kind = ""Table""]}}[Data]
in
    {table}_Table";

     

v-csrikanth's avatar
v-csrikanth
Community Support
1 year ago

Hi sresan477 
Thanks for the clarification. Since you've confirmed that the Snowflake connection is established and returns session info, the issue likely lies in how the M query is evaluated or how schema discovery is handled when building the model programmatically.


Please check that the M query you're assigning to the MPartitionSource returns a table structure with clearly defined rows and columns. A common reason for empty tables appearing in the semantic model is that the query resolves to something other than a table (like a list or a scalar), or the schema isn’t being inferred correctly. You can test this by pasting the same M query into Power BI Desktop's Advanced Editor to validate that data loads as expected.


If you're using Snowflake.Databases() or Snowflake.Contents(), make sure the expression navigates all the way to the actual table and returns the data.
In some cases, setting [InferSchema=true] as a parameter in the connector helps resolve missing columns during schema inference. You may also try manually defining the expected columns in TOM before processing the table.

Lastly, don’t forget to explicitly call RequestRefresh(RefreshType.Full) on the partition and then commit changes using model.SaveChanges() this is essential to trigger the actual data load into the model.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

  • sresan477's avatar
    sresan477
    Frequent Visitor
    1 year ago

    I have tried the given method and it is the same issue. The table is created but it has no data in it:

     

    for your reference, this is the M Expression I am using:

                var mExpr = $@"
let
    Source    = Snowflake.Databases(""{SfAccount}"",
                    [ Warehouse=""{SfWarehouse}"",
                      Database=""{SfDatabase}"",
                      Schema=""{SfSchema}"",
                      Role=""{SfRole}"",
                      InferSchema=true ]),
    TableData = Source{{ [Name=""{SfTableName}"",Kind=""Table""] }}[Data]
in
    TableData";

    I have tried the method to create a snowflake data source first using M Snowflake Connector, but as I am connecting to the Power BI Service, it is not able to create the data source at all.

     

    I have found that I do not need to create a data source when creating in the service as the M Expression used to load the data (given above) will implicity create the connection. Is this accurate?

    Here is the way I have created the Data Source:

    var sfDs = new StructuredDataSource { Name = "SnowflakeConnector" };    
var connJson = $@"{{
  ""protocol"": ""Snowflake.Databases"",
  ""address"": {{
    ""account"":   ""{SfAccount}"",
    ""warehouse"": ""{SfWarehouse}"",
    ""database"":  ""{SfDatabase}"",
    ""schema"":    ""{SfSchema}""
  }}
}}";

    var credJson = $@"{{
  ""authenticationKind"": ""UsernamePassword"",
  ""username"":           ""{SfUsername}"",
  ""password"":           ""{SfPassword}"",
  ""encryptConnection"":  true
}}";

    var sfDs = new StructuredDataSource { Name = "SnowflakeConnector" };
    sfDs.ConnectionDetails = new ConnectionDetails(connJson);
    sfDs.Credential        = new Credential(credJson);

    model.DataSources.Add(sfDs);
    model.SaveChanges();