The
final SQL statement is shown below, with Figure 10-15 showing the final query as
designed, albeit a bit compressed due to the constraints of space for screenshots.
SELECT DimCustomer.FirstName, DimCustomer.LastName,
DimTime.FullDateAlternateKey,
DimProductSubcategory.EnglishProductSubcategoryName,
DimProduct.EnglishProductName, FactInternetSales.SalesAmount
FROM FactInternetSales INNER JOIN
DimCustomer ON FactInternetSales.CustomerKey = DimCustomer.
CustomerKey INNER JOIN
DimTime ON FactInternetSales.OrderDateKey = DimTime.TimeKey INNER JOIN
DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey
INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey =
DimProductSubcategory.ProductSubcategoryKey INNER JOIN
338 B u s i n e s s I n t e l l i g e n c e w i t h M i c r o s o f t O f f i c e P e r f o r m a n c e P o i n t S e r v e r 2 0 0 7
DimProductCategory ON DimProductSubcategory.ProductCategoryKey =
DimProductCategory.ProductCategoryKey INNER JOIN
DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey
WHERE (DimProductCategory.EnglishProductCategoryName IN
(@ProductCategory)) AND (DimTime.CalendarYear IN (@Year)) AND
(DimGeography.EnglishCountryRegionName = @Country)
After adding the WHERE clause, three parameters are automatically created:
ProductCategory, Year, and Country. Normally the developer would now create data
sources to fill lists for each of these parameters so that users could select values, but
this particular example will be used for other purposes in a moment.
Pages:
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429