To write a simple MDX query using the
DWSimpleSales cube, make sure to first complete the following tasks in Visual
Studio:
- Open the SSAS project called “MDXProgrammingDemo.
- Create the DWSimpleSales database using the SQL script provided in the solution folder.
- Open the SalesCubes Project properties dialog window and configure the server deployment setting as necessary.
- Open the Data Source file and configure the server impersonation settings as necessary.
- Deploy, Process, and Test/Browse the DWSimpleSales cube and resolve any errors.
Your Cube Structure
should appear as follows once the tasks above have been completed:
Open
Microsoft SQL Server Management Studio and connect to the DWSimpleSales cube
Click
on:
> Windows
(aka Start) button
>
All Programs
>
Microsoft SQL Server 2012 (or the version
available on the computer used)
>
SQL Server Management Studio (make sure
to run the program as an administrator).
In
the Connect to Server window:
>
In the Server type box, select Analysis Services.
>
In the Server name box, type the name of your Analysis Services server.
>
In the Authentication box, select Windows Authentication.
>
Click on Connect.
Once
connected to Analysis Services:
>
Find the DWSimpleSales cube in the list of Databases in Object Explorer (left side).
>
Expand and explore the nodes and elements of the cube in Object Explorer.
In
the toolbar:
>
Click on either New Query, or the Analysis Services MDX Query button.
When
the new query tab opens:
>
Choose DWSimpleSales in the Cubes drop down menu if not already selected.
>
Expand the Measures and dimensions on the Metadata tab to review their
contents.
>
Auto-hide the Object Explorer window.
To
compose the MDX query, decide what information is needed from the cube while
keeping in mind the MDX Basic Concepts (Note:
There may be more considerations depending on the complexity of a query and/or
its desired results):
Review
this basic query's syntax and comments:
//MDX
Example Query With Basic SELECT Statement Syntax
SELECT
--The
asterisk * is not required to retrieve all records
{ [Measures].[SalesDollars] /*<Attribute Set>*/ } ON
COLUMNS,
--Axis
0; note Measures hierarchy
{ [DimProducts].[Product].[Prod1] /*<Attribute
Set>*/ } ON ROWS
--Axis
1; note Products hierarchy
FROM
[DWSimpleSales] /*<Cube Name>*/
WHERE ( [DimSalesDates].[SalesYear].&[2015] /*<Member>*/ );
--The
WHERE clause is not required to narrow the query
--The
semicolon ; is not required to end the statement
The query retrieves the total
amount of sales so far for Product 1 during 2015.
To create this MDX query in
SSMS:
- It can be typed directly into the new MDX query window.
- The drag-and-drop technique can be used to prevent typing errors.
Since we are just learning,
set up the MDX query structure to include the ON COLUMNS and
ON ROWS syntax:
SELECT
{ } ON COLUMNS,
{ } ON ROWS
FROM
WHERE ( );
Remember using curly braces
and parentheses, even with just one tuple, is makes for better organization,
easier reading, and is good practice.
Since drag-and-drop is the
easiest method and prevents typing errors, we will use that method for our
first query.
Find the measure needed to
create the query, which is SalesDollars.
- Point the cursor at the SalesDollars attribute in the Measures list on the Metadata tab.
- Click on, hold, and drag it to the line below the SELECT keyword in the query window between the curly braces before the ON COLUMNS keyword.
SELECT
{
[Measures].[SalesDollars] } ON COLUMNS,
{ } ON ROWS
FROM
WHERE ( );
**Notice the required brackets are included using the drag-and-drop
technique.
Find the product needed to
create the query, which is Product 1.
- Point the cursor at the Prod1 member in the DimProduct dimension hierarchy on the Metadata tab.
- Click on, hold, and drag it to the line below the SELECT keyword in the query window between the curly braces before the ON ROWS keyword.
SELECT
{[Measures].[SalesDollars]
} ON COLUMNS,
{[DimProducts].[Product].&[1]}
ON ROWS
FROM
WHERE ( );
**Notice only the Prod1 key appears.
To be more user
friendly, type in "Prod" in
front of the key.
SELECT
{ [Measures].[SalesDollars]
} ON COLUMNS,
{ [DimProducts].[Product].&[Prod1]
} ON ROWS
FROM
WHERE ( );
Find the cube needed to
create the query, which is DWSimpleSales.
- Point the cursor at the DWSimpleSales cube on the Metadata tab.
- Click on, hold, and drag it to the right of the FROM keyword in the query window.
SELECT
{ [Measures].[SalesDollars]
} ON COLUMNS,
{ [DimProducts].[Product].&[Prod1]
} ON ROWS
FROM [DWSimpleSales]
WHERE ( );
Find the time period needed
to create the query, which is 2015.
- Point the cursor at the 2015 member in the SalesDates dimension hierarchy on the Metadata tab.
- Click on, hold, and drag it to the right of the WHERE keyword between the parentheses in the query window.
SELECT
{ [Measures].[SalesDollars]
} ON COLUMNS,
{ [DimProducts].[Product].&[Prod1]}
ON ROWS
FROM [DWSimpleSales]
WHERE ( [DimSalesDates].[SalesYear].&[2015] );
Since we created this query
for the purposes of learning MDX, comment your newly created MDX query
accordingly for all future users (and newbies):
//MDX
Example Query With Basic SELECT Statement Syntax
SELECT
--The
asterisk * is not required to retrieve all records
{
[Measures].[SalesDollars] /*<Attribute Set>*/
} ON COLUMNS,
--Axis
0; note Measures hierarchy
{
[DimProducts].[Product].[Prod1] /*<Attribute Set>*/ } ON
ROWS
--Axis
1; note Products hierarchy
FROM
[DWSimpleSales] /*<Cube Name>*/
WHERE ( [DimSalesDates].[SalesYear].&[2015] /*<Member>*/ );
--The
WHERE clause is not required to narrow the query
--The
semicolon ; is not required to end the statement
Now you are ready to
There are three ways to do
so. First, highlight the query. Then:
- Click the Execute button in the toolbar.
- Press the F5 key.
- Right click the highlighted query, and choose Execute from the context menu.
Now review
both the Results and Messages tabs in the Results pane, both of which give
information about the query structure and result set:
You're done!
Now keep practicing....
No comments:
Post a Comment