Forum Discussion
SSAS-Hybrid mode.
Hi
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
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):
- Select the INVENTORY table in the Model view.
- In the Properties pane, set the Direct Query Mode property to Off.
- This will configure the table to use Import Mode .
- SALES Table (Direct Query Mode):
- Select the SALES table in the Model view.
- In the Properties pane, set the Direct Query Mode property to On.
- This will configure the table to use Direct Query Mode (data is queried directly from the source).
- 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.
12 Replies
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
Thank you for the solution. I’m looking to manage this at the SSAS level since I have existing relationships and DAX measures
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 purposeDAX
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.
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
KikiHi Kakikupart
After importing tables into tables into model Open the Model.bim File
- 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 .
- 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 LearnIf 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.
