Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 212

Here, we exploit the many-to-many relationship between employees and cities
through the bridge table Territories . We assume that we are using Analysis
Services, and thus, we make use of the Territories Count measure that is
automatically added to each measure when it is created, as explained in
Sect. 5.9.5 . We rename this measure as NoCities at the beginning of the
query. Then, for the NoStates calculated measure, we perform a cross join
that obtains the states to which the current employee is related and apply
DISTINCTCOUNT to the result, in order to compute the number of states for
such employee. Notice that a similar approach can be used for obtaining the
number of cities if the measure Territories Count does not exist in the cube.
Finally, the SELECT clause displays the measures.

FullName Sales Amount NoCities NoStates

Andrew Fuller $152,164.80 6 3

Anne Dodsworth $69,046.17 7 5

Janet Leverling $186,197.80 4 2

Laura Callahan $122,528.86 4 3

Margaret Peacock $224,397.30 3 2

Michael Suyama $64,969.63 5 3

Nancy Davolio $184,758.38 1 1

Robert King $117,020.49 10 3

Steven Buchanan $68,792.28 5 3

6.3 Querying the Northwind Data Warehouse in
SQL

Given the schema of the Northwind data warehouse in Fig. 6.3 , we revisit
the queries of the previous section in SQL. This allows us to compare the
expressiveness of both languages. This is of particular importance because
some OLAP tools automatically translate MDX queries into SQL queries
which are then sent to a relational server.

Query 6.1. Total sales amount per customer, year, and product category.
SELECT C.CompanyName, T.Year, A.CategoryName,
FORMAT(SUM(SalesAmount), ' $###,##0.00 ' )ASSalesAmount
FROM SalesS,CustomerC,TimeT,ProductP,CategoryA
WHERE S.CustomerKey = C.CustomerKey AND
S.OrderDateKey = T.TimeKey AND
S.ProductKey = P.ProductKey AND P.CategoryKey = A.CategoryKey
GROUP BY C.CompanyName, T.Year, A.CategoryName
Here, we join the fact tables with the involved dimension tables and aggregate
the results by company, year, and category. The FORMAT function is used
to format the aggregated measure.