Introduction to SQL - Microsoft Business Intelligence Tools for Excel Analysts - page 187

Select Distinct a1.AccountDescription, a2.AccountDescription
From dbo.DimAccount a1
Join dbo.DimAccount a2
On a1.AccountKey=a2.ParentAccountKey
Order by 1,2
Inner joins
An inner join is not just limited to an equality relationship. Several situations in analytics require you
to go beyond that basic relationship and into more advanced ones. This requires a strong ability to
visualize data. A common example occurs in what is referred to as a Type II dimension. This type of
dimension shows a point-in-time snapshot of the data. Take the example of an organization hierar-
chy. In certain views you may want to see the organization metrics based on how the organization
hierarchy looked at the point that metric was taken. In that case, you have to use a between join in
your query.
Advanced grouping
SQL includes a very powerful grouping functionality. These functions are most commonly used in
analytics and allow you to perform things such as ranking or partitioning your result set. Following is
the basic construct of these functions:
➤ Over Clause: Used to determine how the dataset should be partitioned and ordered. Inside
the Over Clause , use the Partition By or Order By clauses to define the columns
involved and the output:
Over (Partition By col1, col2, col3)
➤ Window Functions: The Over Clause is used with Window Functions such as Rank or
Row_Number , or with an Aggregation function:
Select SalesTerritoryKey, ProductKey,
Sum(OrderQuantity) Over(Partition By SalesTerritoryKey)
SalesTerritoryOrderQuantitySum
From dbo.FactInternetSales
The output of this query returns the sum of OrderQuantity by SalesTerritoryKey and
repeats those numbers for all ProductKeys in that SalesTerritoryKey (see Table 9-2).