Forum Discussion
SSAS-Hybrid mode.
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.
Hi 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.
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
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 ConfigurationIn 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.
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
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.
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.
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.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.