To begin with 2019, I just want to keep it simple. Let's see a small problem I found in SSAS tabular model recently: It could be very confusing when we need to change the case of a dimension member. To demonstrate this problem, I created a very simple table:
use master
go
create database demo
go
use demo
go
if exists (select 1 from sys.tables where name = 'product')
drop table product
go
create table PRODUCT
(
ProductKey int identity(1,1),
ProductName varchar(100),
Country varchar(100),
Constraint PK_Product Primary Key (ProductKey)
)
go
Insert into Product(ProductName, Country)
values
('BIKE', 'AU'),
('BIKE', 'UK'),
('BIKE', 'US'),
('BIKE', 'CN'),
('BIKE', 'NZ')
go
Select * from PRODUCT
As you can see it is a very simple table
And then I just created a tabular model based on this simple table.
Now I bring in the Power BI to visualise the product name by country
So far everything is good. But assume CN's manager wants to make some formatting changes: I want to see the product name in proper case, "Bike". Looks very simple, doesn't it? So we go to the database, update product name to proper case, re-process the model.
Update PRODUCT set ProductName = 'Bike' where Country = 'CN'
Now refresh the report, nothing happens!
What happened? You can find the reason here. In short, it is because tabular model uses columnar storage mode, so for the same string value, it will only save the first instance regardless of its form. So in tabular model, "BIKE", "bike", "Bike", and "biKE" are all saved as one entry "BIKE". So now doesn't mater how many times you update your data, or re-process your tabular model, the report will always show "BIKE" as the product name.
To approve this, let's update all "BIKE" to "Bike" now, then re-process the model.
Now we go back to the PBI report and refresh it.
So here is the conclusion. When we design the data model, usually we will denormalise the data to build the dimension, which obviously, will generate lots of duplicated entries. When there is a requirement to show members in different forms, we need to be very careful due to the storage mode. In my practice, I would suggest to have a format string field build into the model.
