Ok... I got cricket's on that question... Here's what I'm talking about...
In the article, it first shows Step 1 as a T-SQL query to get the data "into" the Power BI environment.
Next, it shows how to create a couple of columns, one of them being a ranking column and you need to know another "language" to do it, not to mention a whole bunch of clicking around in menus to get the job done. I can understand the power provided in being able to easily filter things but why not go "full monty" on the SQL side of things? Like this...
,SalesAmount = SUM(SalesAmount)
FROM dbo.FactResellerSales sale
JOIN dbo.DimProduct prod ON prod.ProductKey = sale.ProductKey
JOIN dbo.DimProductSubCategory scat ON scat.ProductSubcategoryKey = prod.ProductSubcategoryKey
JOIN dbo.DimProductCategory pcat ON pcat.ProductCategoryKey = scat.ProductCategoryKey
JOIN dbo.DimSalesTerritory terr ON terr.SalesTerritoryKey = sale.SalesTerritoryKey
GROUP BY terr.SalesTerritoryRegion
,RegionProductBestRank = RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SalesAmount DESC)
,RegionProductWorstRank = RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SalesAmount ASC)
,RegionProductPctSales = CONVERT(DECIMAL(5,2),SalesAmount / SUM(SalesAmount) OVER (PARTITION BY SalesTerritoryRegion) * 100)
,RegionTotalSales = SUM(SalesAmount) OVER (PARTITION BY SalesTerritoryRegion)
,RegionProductPctTotalSales = CONVERT(DECIMAL(5,2),SalesAmount / SUM(SalesAmount) OVER () * 100.0)
,ProductPctTotalSales = CONVERT(DECIMAL(5,2),SUM(SalesAmount) OVER (PARTITION BY EnglishProductCategoryName) / SUM(SalesAmount) OVER () * 100)
,TotalSales = SUM(SalesAmount) OVER ()
...which you could even dump into a "preaggregated" table during whatever ETL process you use to keep your DW up to date.
That's the other thing I'll never understand about DWs... people spend a shedload of data creating denormalized data for reporting but the preaggregation done to support the denormalization is paltry compared to the needs of the reporting and a ton of people end up running the same reports against some pretty long tables instead of hitting some thoughtfully constructed preaggregated tables.
Yep... people will say that you cannot anticipate what people want to seen until they see it but, lordy, once they do see it, why not do the preaggregation so that when a thousand people are all banging on the same reports with different criteria so that poor little DW box doesn't have to work so hard and people can get their reports more quickly?
If you went all the way and only supplied preaggregated tables, think of how little data you might actually have to transfer during your DW ETL processes.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)