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
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.
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