Forum Discussion

Kakikupart's avatar
Kakikupart
Frequent Visitor
1 year ago
Solved

SSAS-Hybrid mode.

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 tabl...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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).
    1.   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's avatar
v-karpurapud
Community Support
1 year ago

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's avatar
    Kakikupart
    Frequent Visitor
    1 year ago

    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's avatar
      v-karpurapud
      Community Support
      1 year ago

      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's avatar
        Kakikupart
        Frequent Visitor
        1 year ago

        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