Embedded Files
I update the Ledger Semantic model multiple times a month, which used to be a task I dreaded due to the massive data volume and the time it consumed. However, that's no longer the case! I discovered a way to manage the data volume effectively, keeping the model lightweight during updates. Once the changes are applied to the server, I can simply adjust a parameter to fully load the data as needed.
I did question whether it was worth investing so much time documenting my discovery, considering that Power BI might eventually allow full model editing directly within the service. After all, it already supports some modeling and DAX creation within the workspace. However, I still believe this approach will remain relevant even if such capabilities are introduced.
Let’s recap the situation. The challenge is dealing with an 'Import Model' that takes a significant amount of time to update due to its large data volume. By "update," I mean the process of downloading the semantic model, making changes, and then uploading it back to the workspace. The solution I’ve been exploring involves managing the data volume by using a parameter to keep the model lightweight during updates.
Step 1: Create a Parameter
Step 1: Create a Parameter
To control data volume, create a Parameter of type Text with a predefined list of values. For this exercise, we’ll call the parameter EditMode. This parameter will act as a toggle for data volume:
If EditMode is set to 1, the data volume will be constrained to the top 10 values only.
If EditMode is set to 0, all data will be loaded.
This approach allows you to efficiently manage the model size during updates.
Note: The parameter data type MUST BE Text type.
Step 2: Control the 'Fact' table data volume through the parameter.
Step 2: Control the 'Fact' table data volume through the parameter.
In this exercise, I’m using an **SQL database** as the source for facts. While the solution is designed for SQL, it could potentially work with other data sources as well (though I haven’t tested it, so no guarantees).
For SQL, the approach is straightforward:
Declare a local int variable.
Initialize i.e. Set the variable with a default value of 0.
Use an IF statement to compare the local variable with the EditMode parameter:
If the condition evaluates to true (EditMode = 0), use a BEGIN and END block to query all the data.
Otherwise, within the same structure, query only the top 10 values.
This allows you to toggle between loading all the data or a limited subset based on the parameter's value, offering flexibility and efficiency.
DECLARE @varEditMode int
SET @varEditMode = 0
IF @varEditMode = " & EditMode & "
BEGIN
SELECT
*
FROM [GL].[LedgerBudget]
END
ELSE
BEGIN
SELECT
TOP(10)*
FROM [GL].[LedgerBudget]
END
Step 3: Parameter Update
Step 3: Parameter Update
For my use case, I applied the EditMode parameter only to control the Fact tables since my Dimension tables were relatively lightweight. Every time I downloaded the model, I would first set EditMode to 1 before making any changes. This setup allowed me to:
Perform fast data refreshes (loading only the top 10 rows).
Work efficiently on modeling, DAX, and calculations without the burden of a full dataset.
Quickly upload the updated model to the workspace.
Once the model was uploaded to the server, I would update the EditMode parameter back to 0. This ensured that all subsequent refreshes would load the full dataset instead of just the top 10 rows. This approach streamlined the process, saving significant time while maintaining flexibility.
And there you have it—a simple yet effective way to update your model without wasting time on unnecessary data refreshes. This approach keeps the process efficient and hassle-free!
Reach out to me for any questions here: nsfdec14@gmail.com
Page updated
Google Sites
Report abuse