HufferTextileDW.abf
HuffnerTextile.sln
This solution contains 4 Perspectives. The way you create them is when you click on the New Perspective button (top-left), you need to give it a name. Under Fields you will find the table names and the attributes. You pick fields related to your perspective (sub cube). Those with check marks will be visible for a particular perspective.
PERFORMANCE BENEFITS OF PARTITIONING IN ANALYSIS SERVICES TABULAR
What is Partition
Partitions split a table into logical partition objects. Each partition contains a portion of the data, partitions can be processed in parallel independent of other partitions or excluded from processing operations if they don’t need to be refreshed.
When to partition
As a rule of thumb, I usually partition tables with more than 15M rows but if the refresh of your model is still fast enough there may be no need for partitioning yet. By default, in SSAS the data are stored in segments where each segment has by default 8M rows, and the first segment can store up to twice the default size (16M rows by default) so this where my rule of thumb is coming from.
Partitioning Dos and Don’ts
Partitions should be equally distributed as much as possible (time columns are typically good candidates for partitions)
Underlying tables should be indexed on the column used to filter the partition
Avoid over partitioning your model as it could lead to the opposite intended effect
Refresh time will increase as the engine will spend more time aggregating each partition together
The memory size of your model increases if the partitions are too small (lower than the default segment size)
If your model is large, it’s important to test different number of partitions
Refresh only the partitions that need to be refreshed (historical data may not have changed)
As you can see from this picture, my tables are small, up to 0.5M rows. This is the main reason I skipped creating the partitions for this model.
This model contains over 70 measures and 11 KPI's, 4 Perspectives.
This is Revenue KPI for Client Sales. When creating KPI you can either hardcode the value as I did in this case or use a measure in Target > Measure: section.
(sales_ly = CALCULATE([Totaldue Clients],DATEADD ('Date'[Date],-1,YEAR)))
When using DATEADD function, make sure, your DimDate table is marked as date table, otherwise this function won't be working.
Revenue YoY % Growth:=
var sales = [Totaldue Clients]
var sales_ly = CALCULATE([Totaldue Clients],DATEADD('Date'[Date],-1,YEAR))
var sales_change=sales-sales_ly
var result =DIVIDE(sales_change,sales_ly,0)
return
result
KPI settings:
over 10% = Green; between 5-10% = Orange; less than 5% = Red
You can connect to this mode with PowerBi and create the reports. If you want to publish them, you need to have Active Directory install on your computer - permission issues.
Comments