SSAS-Hybrid mode.

[Kakikupart]

Hi

I need your expertise on how to configure the SSAS database to implement hybrid mode. This hybrid mode will allow Power BI reports to use SSAS tables in Import mode while also supporting tables in DirectQuery mode. The DirectQuery mode will extract data directly from the source SQL tables, minimizing SSAS storage usage.

For example, I have an SSAS database named HYBRID, which consists of two tables:

• SALES table: I want to use DirectQuery mode.
• INVENTORY table: I want to use Import mode.

Additionally, I need to ensure that these two tables can utilize relationships and DAX measures effectively.

Currently, I am using:

• Visual Studio Version: Microsoft Visual Studio 2022 (64-bit) - Current Version 17.5.3
• SSAS (Microsoft Analysis Server) Version: 16.0.43.226/Server Mode:Tabular/Default Compatibility Level:1600

Thank you
Kiki

[v-karpurapud]

Hi @Kakikupart 

Thanks for reaching out to the Microsoft Fabric Community Forum.

To enable a hybrid mode in SSAS, where some tables use Import mode and others use DirectQuery mode, follow these steps:
1. Create a New SSAS Tabular Project in Visual Studio, Select Analysis Services Tabular Project and Name your project and choose the appropriate directory.

2.Set Up Data Source Connections i.e., in solution explorer click on data sources and select new data source. Select the data source type(SQL Server).
3.Enter the connection details for SQL Server instance (where SALES and INVENTORY tables reside) , Test the connection and ensure it is successful.
4. Set Table Storage Modes in the Model section SALES table’s Storage Mode to DirectQuery and  INVENTORY table’s Storage Mode to Import(INMemory).
5. Ensure that the relationships between these tables are established or create relationships if necessary.
6. Define DAX measures for analysis purpose

DAX

Total Sales = SUM(SALES[Amount])

Total Inventory = SUM(INVENTORY[StockLevel])

7. Process the model to load data for Import tables and validate the DirectQuery connection and deploy it to  SSAS server.
8. Connect Power BI to SSAS using Get Data > Analysis Services in Power BI to connect to SSAS model after that use data from both tables in reports(SALES: DirectQuery mode, INVENTORY: Import mode).
9. Verify relationships, measures, and visuals in Power BI, ensuring data is queried from the correct storage mode.

If you have any further questions or need additional help with this, feel free to reach out to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Kakikupart]

Hi @v-karpurapud 

Thank you for the solution.
Regarding Step #4, I am facing the same issue where I cannot find how to set the "Table Storage Modes" and the available table-level features, as shown in the screenshot below. For example, I need to set the SALES table to DirectQuery mode.

Currently, I am using:

• Visual Studio Version: Microsoft Visual Studio 2022 (64-bit) - Current Version 17.5.3
• SSAS (Microsoft Analysis Server) Version: 16.0.43.226/Server Mode:Tabular/Default Compatibility Level:SQL Server 2022 / Azure Analysis Services (1600)

Thanks
Kiki

[v-karpurapud]

Hi @Kakikupart 

After importing tables into tables into model Open the Model.bim File

1. In Visual Studio, double-click the Model.bim file in the Solution Explorer then scroll a bit there we have a option DirectQuery option is visible . Set it to ON mode .
2. Now select the SALES Table and then click on partition  there we will find the partition type click on OK.   
   Please refer the below pictures for better understanding.                                            
       
   

   For Reference purpose i have attached a microsoft document link, please go through it to have better knowledge .
   Enable in Analysis Services DirectQuery mode in Visual Studio | Microsoft Learn

   If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

   If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Kakikupart]

Hi @v-karpurapud 

I appreciate your help! Will all the SSAS tables in the SSAS database be in DirectQuery mode, or will they remain in Import mode? My expectation is that the INVENTORY table should operate in Import mode, while the SALES table should use DirectQuery mode, both within the same SSAS database
Thank you,
Kiki

[v-karpurapud]

Hi @Kakikupart 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-karpurapud]

Hi @Kakikupart 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-karpurapud · Accepted solution]

Hi @Kakikupart 

I would like to sincerely apologize for the solution provided in my earlier below given response. I realized that my reply was not accurate.


Here is the information  as follows:

1.Understanding Import Mode and Direct Query Mode

• Import Mode: When Direct Query Mode is turned off, the table operates in Import Mode. This means the data is loaded into memory and queried from there.
• Direct Query Mode: When Direct Query Mode is turned on, the table queries data directly from the source.

2.Steps to Configure the Tables:

• INVENTORY Table (Import Mode):
1. Select the INVENTORY table in the Model view.
2. In the Properties pane, set the Direct Query Mode property to Off.
3. This will configure the table to use Import Mode .
• SALES Table (Direct Query Mode):
1. Select the SALES table in the Model view.
2. In the Properties pane, set the Direct Query Mode property to On.
3. This will configure the table to use Direct Query Mode (data is queried directly from the source).

3.   Save the changes and process the model to reflect the updated configurations.

4.Test in Power BI after deploying, connect to the model and run queries to ensure:

      INVENTORY table uses in-memory(Import) data and SALES table fetches data directly from the source.

If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[v-karpurapud]

Hi @Kakikupart 

May I know if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

HI @Kakikupart 

In SQL Server Analysis Services (SSAS) Tabular, starting from compatibility level 1200 and above, we can use a feature called mixed storage mode. This allows US to configure individual tables within the same SSAS Tabular model to operate in either Import mode or DirectQuery mode, depending on  business needs. Since you're using SSAS version 16.0.43.226 (Tabular mode with compatibility level 1600), you can achieve the configuration where the INVENTORY table operates in Import mode and the SALES table operates in DirectQuery mode.

Validate the Mixed Storage Configuration

In the Model.bim file, inspect the JSON code to confirm the storage mode settings. For example:
{
"tables": [
{
"name": "INVENTORY",
"partitions": [
{
"storageMode": "import"
}
]
},
{
"name": "SALES",
"partitions": [
{
"storageMode": "directquery"
}
]
}
]
}

Using mixed storage mode in SSAS Tabular allows configuring individual tables for specific needs—e.g., setting the INVENTORY table to Import mode for performance and the SALES table to DirectQuery mode for real-time data. This approach optimizes performance and data freshness within a single SSAS database.

If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Kakikupart]

Hi @v-karpurapud 

Sorry,I have tried but still cannot achieve.

I inspected the model.bim file and noticed that the defaultMode property only becomes available after changing the DirectQuery mode from Off to On. If the DirectQuery mode is set to Off, the defaultMode property does not appear in the script.

The first issue I encountered is that I am unable to set the defaultMode to Mixed Storage, as this results in an error in the model.

Below is an example script that I copied from model.bim, with two tables mentioned and limited fields included for testing

{

  "name": "SemanticModel",

  "compatibilityLevel": 1600,

  "model": {

    "defaultMode": "directQuery",

    "culture": "en-US",

    "dataSources": [

      {

        "type": "structured",

        "name": "SQL",

        "connectionDetails": {

          "protocol": "tds",

          "address": {

            "server": "SQLANAL",

            "database": "SALES"

          },

          "authentication": null,

          "query": null

        },

        "credential": {

          "AuthenticationKind": "Windows",

          "kind": "SQL",

          "path": "sqlPBI;SALES",

          "Username": "XXXXXX\\XXXXXXXX",

          "EncryptConnection": false

        }

      }

    ],

    "tables": [

      {

        "name": "SALES",

        "columns": [

          {

            "name": "ArticleId",

            "dataType": "string",

            "sourceColumn": "ArticleId"

          },

          {

            "name": "Article Desc",

            "dataType": "string",

            "sourceColumn": "Article Desc"

          }

        ],

        "partitions": [

          {

            "name": "Partition",

            "dataView": "full",

            "source": {

              "type": "m",

              "expression": [

                "let",

                "    Source = #\"SQL\",",

                "    dbo_V_SALES = Source{[Schema=\"dbo\",Item=\"V_SALES\"]}[Data]",

                "in",

                "    dbo_V_SALES"

              ]

            }

          }

        ]

      },

      {

        "name": "INVENTORY",

        "columns": [

          {

            "name": "Inv Number",

            "dataType": "string",

            "sourceColumn": "Inv Number"

          },

          {

            "name": "Inv Date",

            "dataType": "dateTime",

            "sourceColumn": "Inv Date"

          }

        ],

        "partitions": [

          {

            "name": "Partition",

            "dataView": "full",

            "source": {

              "type": "m",

              "expression": [

                "let",

                "    Source = #\"SQL\",",

                "    dbo_V_INVENTORY = Source{[Schema=\"dbo\",Item=\"V_INVENTORY\"]}[Data]",

                "in",

                "    dbo_V_INVENTORY"

              ]

            }

          }

        ]

      }

    ],

    "annotations": [

      {

        "name": "ClientCompatibilityLevel",

        "value": "700"

      }

    ]

  },

  "id": "SemanticModel"

}

Thank you
Kiki

[Jai-Rathinavel]

@Kakikupart Follow the below steps to create your Hybrid model

1. Open a New Power BI Desktop File

2. Click on Get data -> Analysis Services.

3. Enter your connection string and choose import mode. This will pull the tables from SSAS cube in Import mode. 

Authenticate using your microsoft account

4. Now to import SQL Table. Click on SQL Server and set the mode as DirectQuery

5. Once the Tables are loaded you can establish a relationship and work with the measures / model.

Did I answer your question ? Please mark this post as a solution.

Thanks,

Jai

[Kakikupart]

Thank you for the solution. I’m looking to manage this at the SSAS level since I have existing relationships and DAX measures