Databases Reference
In-Depth Information
source using a query builder. Click Query Builder to launch the
Query Builder page.
7. The default query builder page is a generic query builder that can be
used against any relational data source. Click the leftmost icon on
the query builder page to switch over to a graphical designer user in-
terface. This query builder has four panes as shown in
Figure 17-5
.
◦ Initially the Table organizer pane is empty. Right-click within
this pane and select Add Table. You will see the list of tables
available within the AdventureWorksDW database. Select
the FactInternetSales, DimTime, DimProduct, and DimPro-
ductSubcategory tables and click Add. The query builder will
retrieve the relationships between tables from the database
and show them graphically in the Table Designer pane (see
Figure 17-6
). You can select the required columns from the
table designer pane by clicking the check boxes adjacent to
the columns. The designer in the Query pane will create ap-
propriate SQL queries. You can edit the selections in the
Column chooser pane or the query directly in the query
pane. The query used for retrieving the sales information in
Figure 17-5
is shown below. Enter the SQL query in the
query designer.
SELECT DimTime.CalendarYear, SUM
(FactInternetSales.SalesAmount) AS TotalSales,
DimProduct.EnglishProductName,
DimProductSubcategory.EnglishProductSubcategoryName
FROM FactInternetSales INNER JOIN
DimProduct ON FactInternetSales.ProductKey
= DimProduct.ProductKey
INNER JOIN
DimTime ON FactInternetSales.OrderDateKey
= DimTime.TimeKey INNER JOIN
DimProductSubcategory ON
DimProduct.ProductSubcategoryKey =
DimProductSubcategory.ProductSubcategoryKey
GROUP BY DimTime.CalendarYear,
DimProduct.EnglishProductName,
Search WWH ::
Custom Search