TPC-H Query 1: Performance Optimization Through Materialization

In this tutorial, we will attempt to improve the performance to TPC-H query 1 for scale factor 10 database. For this purpose, we have generated the LINEITEM table with scale factor 10. Table generation will take some time as it will be of 7.3 GB. For this tutorial, I will be using Intel Core 2 Duo 2.2 GHz machine with only 2 GB of RAM running Windows 7. I will be using SQL Server 2008 DBMS. Time may vary for you depending upon the type of hardware you have.

Once LINEITEM.tbl file was generated. I create LINEITEM table using following TPC-H ddl query:

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,

                             L_PARTKEY     INTEGER NOT NULL,

                             L_SUPPKEY     INTEGER NOT NULL,

                             L_LINENUMBER  INTEGER NOT NULL,

                             L_QUANTITY    DECIMAL(15,2) NOT NULL,

                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,

                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,

                             L_TAX         DECIMAL(15,2) NOT NULL,

                             L_RETURNFLAG  CHAR(1) NOT NULL,

                             L_LINESTATUS  CHAR(1) NOT NULL,

                             L_SHIPDATE    DATE NOT NULL,

                             L_COMMITDATE  DATE NOT NULL,

                             L_RECEIPTDATE DATE NOT NULL,

                             L_SHIPINSTRUCT CHAR(25) NOT NULL,

                             L_SHIPMODE     CHAR(10) NOT NULL,

                             L_COMMENT      VARCHAR(44) NOT NULL);

After creating the table I loaded the data from LINEITEM.tbl file to my table using SQL Server BULK INSERT command.

BULK INSERT LINEITEM FROM 'D:\lineitem.tbl' WITH (FIELDTERMINATOR = '|')

This will take some good amount of time. On my hardware it took around 45 minutes. Once data loading is successfully complete, I will recommend you to restart SQL Server Database Engine services. This will free lot of memory that SQL Server might not be willing to release otherwise. This behavior can be observed in image below:

After successfully loading the data. The second step is to create constraints. In this tutorial, I will only be creating the primary key constraint. However, there are two more foreign key constraint in proper TPC-H specification. Please create the primary key for LINEITEM table using the query below:

ALTER TABLE dbo.LINEITEM

ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

On my computer, it took around 32 minutes to complete. And I again restarted SQL Server Database Engine services to ensure that my computer is with enough memory to progress forward without heavily relying on virtual memory.

Now we are ready to execute TPC-H Query 1 for first time on our scale factor 10 LINEITEM table. Please execute the following query:

SELECT

L_RETURNFLAG

, L_LINESTATUS

, SUM(L_QUANTITY) AS SUM_QTY

, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE

, AVG(L_QUANTITY) AS AVG_QTY

, AVG(L_EXTENDEDPRICE) AS AVG_PRICE

, AVG(L_DISCOUNT) AS AVG_DISC

, COUNT(*) AS COUNT_ORDER

FROM LINEITEM

WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))

GROUP BY L_RETURNFLAG, L_LINESTATUS

ORDER BY L_RETURNFLAG,L_LINESTATUS

This query took around 8 minutes to complete on my computer. Now consider a business user executing the same query. Will it be affordable to to wait for 8 minutes, especially when you boss is on your head waiting for your response? Of-course not. We must find a way to reduce the query execution time.

As I mentioned earlier, keep check on your computer memory consumption. If it is high, a quick relief will be to restart the engine to keep progressing with this tutorial.

The result of query 1 was as shown below:

What are possibilities to reduce the query execution time? One popular approach is to use materialized views. In this tutorial, we will use the same approach, but as we are using SQL Server 2008. It will be done a different way. To achieve similar behavior as materialized view. We have to make use of clustered index in SQL Server 2008.

The solution is simple. First create a view. In this view you have to return all columns that you want to use as criteria, i.e., to restrict the query results. For example, in TPC-H Query 1 L_SHIPDATE is is used in where clause, therefore, we will return L_SHIPDATE as column in our view. A sample view is give below:

create view queryone with schemabinding as

SELECT

L_SHIPDATE

, L_RETURNFLAG

, L_LINESTATUS

, SUM(L_QUANTITY) AS SUM_QTY

, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE

, SUM(L_QUANTITY) AS AVG_QTY_SUM

, SUM(L_EXTENDEDPRICE) AS AVG_PRICE_SUM

, SUM(L_DISCOUNT) AS AVG_DISC_SUM

--, AVG(L_QUANTITY) AS AVG_QTY --We have done AVG_QTY_SUM above

--, AVG(L_EXTENDEDPRICE) AS AVG_PRICE --We have done AVG_PRICE_SUM above

--, AVG(L_DISCOUNT) AS AVG_DISC -- We have done AVG_DISC_SUM

, COUNT_BIG(*) AS COUNT_ORDER

FROM dbo.LINEITEM

--WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))

GROUP BY L_SHIPDATE, L_RETURNFLAG, L_LINESTATUS

--ORDER BY L_RETURNFLAG,L_LINESTATUS --invalid in views

go

An important point to observe here is that we have used with schemabinding option while creating view. It is important to successfully complete our next step. the Once the view is created. The next step is to create a unique clustered index on view using GROUP BY clause fields in the same order. Unique clustered index query is given below:

create unique clustered index queryonemv on queryone(L_SHIPDATE, L_RETURNFLAG, L_LINESTATUS)

go

Creating clustered index will take some time. And again before proceeding further, it is recommended to restart the SQL Server Database Engine to release the memory still in use by engine as shown below in Figure.

Now we are done with our optimization. All we need to do is to check our TPC-H query 1 again, but this time we will execute it on our newly created view instead of LINEITEM table directly. The query rewritten to get the same result as the TPC-H Query 1 on LINEITEM table is given below:

select

[L_RETURNFLAG]

      ,[L_LINESTATUS]

      ,sum([SUM_QTY]) as SUM_QTY

      ,sum([SUM_BASE_PRICE]) as SUM_BASE_PRICE

      ,sum([SUM_DISC_PRICE]) as SUM_DISC_PRICE

      ,sum([SUM_CHARGE]) as SUM_CHARGE

      ,round(avg([AVG_QTY_SUM]/[COUNT_ORDER]), 2) QTY_SUM

      ,round(avg([AVG_PRICE_SUM]/[COUNT_ORDER]), 2) PRICE_SUM

      ,round(avg([AVG_DISC_SUM]/[COUNT_ORDER]), 2) DISC_SUM

      ,sum([COUNT_ORDER]) as COUNT_ORDER

from queryone

WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))

GROUP BY L_RETURNFLAG, L_LINESTATUS

ORDER BY L_RETURNFLAG,L_LINESTATUS

This query took no time to return our results as show in figure below. Either we rely on Codd's Rules or FASMI test, this response time will be acceptable for all for any OLAP task.

In case you are using Oracle. You can perform similar optimization using following materialized view query (please correct the syntax and naming correction according to ORACLE conventions):

CREATE MATERIALIZED VIEW queryone as

SELECT

L_SHIPDATE

, L_RETURNFLAG

, L_LINESTATUS

, SUM(L_QUANTITY) AS SUM_QTY

, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE

, SUM(L_QUANTITY) AS AVG_QTY_SUM

, SUM(L_EXTENDEDPRICE) AS AVG_PRICE_SUM

, SUM(L_DISCOUNT) AS AVG_DISC_SUM

--, AVG(L_QUANTITY) AS AVG_QTY

--, AVG(L_EXTENDEDPRICE) AS AVG_PRICE

--, AVG(L_DISCOUNT) AS AVG_DISC

, COUNT(*) AS COUNT_ORDER

FROM LINEITEM

--WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))

GROUP BY L_SHIPDATE, L_RETURNFLAG, L_LINESTATUS

--ORDER BY L_RETURNFLAG,L_LINESTATUS --invalid in views

OLAP Query Languages

For data warehousing, two widely used storage models are 1) Relational and 2) Multidimensional. Irrespective of what ever storage model you use, you must have a query processing language and components to execute these queries to bring you results. If you are working with relational databases for data warehousing. Use of SQL query language is a must. For this purpose, SQL 99 specification has added few features in SQL to facilitate OLAP operations on relational databases. For example, consider following query that calculates order and sales according to country, product, and shipment date using the AdventureWorksDW database FactInternetSales tables. I restricted to data output to keep the resultset concise and readable.

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName

Above query need three joins and two aggregations to return require results. Now if I need the subtotals for each year, for each product, and for each country as well as the grand total. I have to re-write this query as:

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
UNION
SELECT DST.SalesTerritoryCountry
,'COUNTRYSUBTOTAL'
,'COUNTRYSUBTOTAL'
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany')
group by
DST.SalesTerritoryCountry
UNION
SELECT 'YEARSUBTOTAL'
,DT.CalendarYear
,'YEARSUBTOTAL'
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
WHERE
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004')
group by
DT.CalendarYear
UNION
SELECT 'PRODUCTSUBTOTAL'
,'PRODUCTSUBTOTAL'
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
DP.EnglishProductName
UNION
SELECT 'GRANDTOTAL'
,'GRANDTOTAL'
,'GRANDTOTAL'
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS

You can observe the complexity of the query. This resulted in addition of few features in SQL-99, which allows us to get the same results using much simpler queries. GROUPING SETS is one of those features. Following query make user of GROUPING SETS feature:

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
GROUPING SETS(
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName)

Above mentioned query will generate three groups, i.e., Country, Product and Year. If we also need the calculations on combinations of these three attributes. GROUP BY ROLLUP feature comes into play. The query mentioned below will return the same result as for our four union query above:

* Before executing ROLLUP or CUBE, please make sure that you have set the compatibility level of your database to 100 or use following query:
ALTER DATABASE AdventureWorksDW set compatibility_level = 100

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
ROLLUP (
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName)

Please do remember that order is significant to ROLLUP. GROUP BY ROLLUP (Year, Product, Country)  is equivalent to GROUPING SETS of (Year, Product, Country), (Year, Product), (Year), (All). This means that n-elements of ROLLUP translate into n+1 grouping sets.

If we move ahead and plan to calculate the all possible combinations of attributes, i.e., including (Year, Country), (Product, Country), (Product), and (Country), which were missing in GROUP BY ROLLUP output. We can make use of GROUP BY CUBE. All we need to do is to change the ROLLUP keyword with CUBE keyword.

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
CUBE (
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName)

Another feature, which is not part of SQL99 standard, but can be very handy is NTILE. It allows us to split our resultset into equal groups. Use of NTILE is demonstrated in query below:

SELECT DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName
, SUM([OrderQuantity]) as TORDER
, SUM([SalesAmount]) as TSALES
, NTILE(3) OVER (
ORDER BY DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName) as BUCKETNO
FROM [AdventureWorksDW].[dbo].[FactInternetSales] FIS
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimSalesTerritory] DST
ON
FIS.[SalesTerritoryKey] = DST.[SalesTerritoryKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimTime] DT
ON
FIS.[ShipDateKey] = DT.[TimeKey]
LEFT OUTER JOIN [AdventureWorksDW].[dbo].[DimProduct] DP
ON
FIS.[ProductKey] = DP.[ProductKey]
WHERE
(DST.SalesTerritoryCountry = 'Australia' or DST.SalesTerritoryCountry = 'Germany') and
(DT.CalendarYear = '2003' or DT.CalendarYear = '2004') and
(DP.EnglishProductName = 'All-Purpose Bike Stand' or DP.EnglishProductName = 'Road-550-W Yellow, 44')
group by
CUBE (
DST.SalesTerritoryCountry
,DT.CalendarYear
,DP.EnglishProductName)



MultiDimensional eXpression (MDX)

On the other-hand, for mutidimensional databases. We need to make use of MultiDimensional eXpression (MDX). Developed by Microsoft and later adopted by all major vendors, MDX is a very useful tool to query multidimensional databases. It has three basic constructs, i.e., SELECT, FROM, and WHERE similar to SQL. SELECT is used to specify axis dimensions on columns and rows, FROM is used to specify CUBE/s we want to use for data retrieval, and WHERE is used to restrict the data area. Below is a simple MDX query. To execute MDX query, you should connect to SQL Server Analysis Services using SQL Server Management Studio. After login, select the appropriate database and right click. Select the New Query -> MDX options. This will open a new query windows for writing MDX queries.

SELECT
[Dim Product].[Product] ON ROWS
, [Dim Sales Territory].[Country] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]

Above query returns the sales quantity for all products in all regions. If we want to view the sales quantity for each individual country and product, we have to specify the members keyword for each dimension as show below:

SELECT
[Dim Product].[Product].members  ON ROWS
, [Dim Sales Territory].[Country].members ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]

Both of above-mentioned DMX queries listed the sales quantity measure for our cube. If we want to view the sales amount, we have to mention is explicit. Otherwise default measure is selected for calculation. Below DMX query calculates [Sales Amount] measure.

SELECT
[Dim Product].[Product].members  ON ROWS
, [Dim Sales Territory].[Country].members ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Measures].[Sales Amount])

If you want to select specific member of dimension. You can specify it in SELECT clause or WHERE clause. In DMX query below, we select only the sales for Australia:

SELECT
[Dim Product].[Product].members  ON ROWS
, [Dim Sales Territory].[Country].&[Australia] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Measures].[Sales Amount])

SELECT
[Dim Product].[Product].&[AWC Logo Cap]  ON ROWS
, [Dim Sales Territory].[Country].&[Australia] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Measures].[Sales Amount])

In DMX query below, we select only the sales for Australia for AWC Logo Cap product:

DMX handles Measures as special dimensions. They hold numerical values only and do not contain any concept hierarchy. Therefore, Both dimension and measures can be used in both SELECT and WHERE clauses alternatively.

 SELECT
[Ship Date].[Year].&[2004]  ON ROWS
, [Measures].[Order Quantity] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Dim Product].[Product].&[AWC Logo Cap]
, [Dim Sales Territory].[Country].&[Australia])

We can also make use of tuples to restrict our required data from cube. In query below, ([Dim Product].[Product].&[AWC Logo Cap], [Dim Sales Territory].[Country].&[Australia]) represent a tuple.

SELECT
[Ship Date].[Year].members  ON ROWS
, [Measures].[Order Quantity] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Dim Product].[Product].&[AWC Logo Cap]
, [Dim Sales Territory].[Country].&[Australia])

IF you want to view all measures then members keyword can be used with measures to list all measures as show in query below:

SELECT
[Ship Date].[Year].members  ON ROWS
, [Measures]. members ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
WHERE ([Dim Product].[Product].&[AWC Logo Cap]
, [Dim Sales Territory].[Country].&[Australia])

DMX also gives up provision to make use of multiple tuples as Set. A set is used to define axis on rows in query below:

SELECT
{([Dim Product].[Product].&[AWC Logo Cap], [Dim Sales Territory].[Country].&[Australia])
, ([Dim Product].[Product].&[Adjustable Race], [Dim Sales Territory].[Country].&[Australia])} ON ROWS
, [Measures].[Order Quantity] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]

DMX is not only restricted to ROWS and COLUMNS for axis, instead we can also get PAGES, CHAPTERS, and SECTIONS for more than two axis as shown in query below: 

SELECT
[Ship Date].[Year].members ON ROWS
, [Due Date].[Year].members ON COLUMNS
, [Order Date].[Year].members ON PAGES
, [Dim Product].[Product].members ON CHAPTERS
, [Dim Sales Territory].[Country].members ON SECTIONS
FROM
[Adventure Works DW InternetSales Cube]

Furthermore, DMX also allows us to work without defining any axis. For example, the query below has not axis. This query is legal and it works. It returns the overall summary for our cube measures.

SELECT
FROM
[Adventure Works DW InternetSales Cube]

DMX also support sub-queries as shown in query below:

SELECT
([Dim Product].[Product].members, [Dim Sales Territory].[Country].members) ON ROWS
, [Measures].[Order Quantity] ON COLUMNS
FROM(
SELECT
{([Dim Product].[Product].&[AWC Logo Cap], [Dim Sales Territory].[Country].&[Australia])
, ([Dim Product].[Product].&[Adjustable Race], [Dim Sales Territory].[Country].&[Australia])} ON ROWS
, [Measures].[Order Quantity] ON COLUMNS
FROM
[Adventure Works DW InternetSales Cube]
)

DMX also allows us to define new measures or calculated measures using the existing measures. We can define these new measures using WITH MEMBER keywords as shown in figure below:

WITH
MEMBER [Measures].[Sales Diff] as
( [Measures].[Sales Amount] / [Measures].[Order Quantity])
SELECT
[Dim Product].[Product].members ON 0 --Column
, [Measures].[Sales Diff] ON 1 --Rows
FROM
[Adventure Works DW InternetSales Cube]

Business Intelligence using MS Excel PivotTable with SSAS

In my last post, we learned how to use SQL Server Analysis Services for creating Data Cube for any Data Warehouse. Generating cubes is not the final step, instead another important step is to make your data cube and associated analytical features made available to your end-user. The SQL Server Business Intelligence development studio is for developers. The browser tab available in your design window (shown below) is for us to view and verify the data cube that we have generated. For end-user, we need special tools to make this data cube and its analytical feature available.

The first tools that I am going to discuss is Microsoft Excel PivotTable. A very simple, but yet effective front-end BI tool for OLAP.

In this tutorial, we had SQL Server Analysis Services installed on our local machine. In production environment, we might not like to give direct access to our SSAS to end-user. For this purpose, we can also make use of Microsoft Internet Information Services web server to allow HTTP access to our analysis servcies. A very comprihensive tutorial is available at Configure HTTP Access to Analysis Services on IIS.