Archive

Archive for April, 2014

DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server 2012

April 30, 2014 Leave a comment

OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project.

Download sample database and projects from CodePlex

You can follow this link to CodePlex to download the following files: tinyurl/AdventureWorks2012.

  • AdventureWorksDW2012_Data.mdf
  • AdventureWorks DW Tabular Model SQL Server 2012.zip
  • AW Internet Sales Tabular Model.zip
  • AdventureWorks Multidimensional Models SQL Server 2012.zip (also download this multidimensional sample project while you are there for leaning SSAS/MDX, if you haven’t done so.)

I suggest that you unzip all the files onto a one level folder on your C drive, instead of the Windows built-in Downloads folder or the Documents folder. If you have seen the annoying “Path too long” error when you copy/past or unzip folders with very deep levels, you will appreciate my suggestion.

On Window 7 and 8, also make sure that unblock all the folders/files, and your user ID has full control on them.

Assume you have installed SQL Server 2012

I’ll assume that you have installed the following using the SQL Server Developer Edition or Enterprise Edition 2012:

  • SQL Server 2012 Database Engine (server instance): this is the relational database instance.
  • SQL Server 2012 Analysis Services – Multidimensional model (server instance): this is not required for learning DAX, but it can be extremely important to learn MDX and DAX side-by-side.
  • SQL Server 2012 Analysis Services – Tabular model (server instance)
  • SQL Server 2012 Management Studio (desktop tool): older version of SSMS does not support Analysis Services tubular model.
  • SQL Server Data Tools for Visual Studio 2012 (desktop tool): if you have Microsoft Visual Studio Professional 2012, you should get SSDT and also

To keep things simple, I have installed all three instances on my laptop. The database engine is the default instance, and both the multidimensional model and the tabular model are named instances. I have chosen to name them as:

  • localhost
  • localhost\MULTI2012
  • localhost\TABULAR2012

To make things easy for myself, I always add my own user ID as the administrator for each instance.

(Disclaimer: do not use this type of architecture on your production server.)

Also need PowerPivot

While you are at the installation, also make sure that you have PowerPivot. PowerPivot is the desktop tool for analyst for creating the tubular model, which is eventually consumed by Pivot Table for reporting.

You can run PowerPivot in either Excel 2010 or Excel 2013. If you have Excel 2013, you are all set because PowerPivot is included by default. If you are still running Excel 2010 like me, follow this link to download the free Power Pivot add-in.

www.powerpivot.com

Attach AdventureWorksDW2012_Data.mdf

A typical multidimensional model is built on top of a relational star schema data warehouse. To create a tabular model, we also need data source(s). The most frequently used data sources are the common relational database systems, text files, and excel workbooks. The Adventure Works DW 2012 is the relational star schema database the sample tabular model database uses.

This .mdf file you have just downloaded does not have the matching log file. You can use the following T-SQL command with the option of FOR ATTACH_REBUILD_LOG to attach the database to the SQL Server database instance.

CREATE DATABASE AdventureWorksDW2012EE
ON (FILENAME = N’C:\Adventure Works for SQL Server 2012\AdventureWorksDW2012_Data.mdf’)
FOR ATTACH_REBUILD_LOG;

I’ve named the new database AdventureWorksDW2012EE for Enterprise Edition. This database and the data will be the source of both of my tubular and multidimensional model.

I don’t need to worry about backing up the log for this database, so I also made sure that the Recovery Model is set to Simple.

To learn more about how to attach a database, please refer to this TechNet article, Attach a Database.

Create the Adventure Works DW Tabular Model Database

If you are familiar with Analysis Services, you’d know that Analysis Services database can only be created by deploying the correspondent SQL Server Data Tools project. Unlike the SQL Server relational database, you will not be able to attaché nor import an Analysis Services database.

Fortunately SSDT tabular project you have downloaded is ready for deployment. After you have successfully deployed a tubular model project, you should be able to see two tubular model databases from SSMS.

dax2 2 database - Copy - Copy

 

 

 

 

Once you open the tabular model sample project in SSDT, be sure to make the following adjustments to ensure a smooth deployment.

To begin, right-click on a SSDT 2012 solution, and go to Add – Existing project…. Then find the following sample tubular project you just downloaded.

AdventureWorks Tabular Model SQL 2012.smproj

1.      Test Workspace server connection

As soon as you attempt to open the existing tubular project, you will be asked to identify an Analysis Services instance to use for authoring tabular model projects. I entered localhost\TABULAR2012, and also click Test Connection. Any failure of the connection must be resolved before you can continue. Connection failures mostly indicate permission problems with your Analysis Services instance. To keep things easy on my own laptop, I’ve chosen to use my own user ID to start the Analysis Services tabular model instance, instead of the default service account (again not recommended for your-know-what).

dax2 AS instance - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.      Be sure to check the existing connection to the relational database

What I need to make sure is that the underneath relation database is pointing to my AdventureWorksDW2012EE database on localhost.

Go to menu MODEL – Existing Connections – Edit.

dax2 AS deploy - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After modify the database name, click Test Connection again to test it. At this point, I also want to change the credential for Impersonation to use my own user ID, instead of the service account.

dax2 Impersonation - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Be sure to modify the tabular project property page

I also need to make sure I have the right Analysis Services instance for the deployment.

Go to the menu PROJECT – Properties…. I modified the Server name to be localhost\TABULAR2012, and accepted the default database name “AdventureWorks Tabular Model SQL 2012”.

dax2 proj property - Copy

 

 

 

 

 

 

 

 

 

 

 

4. Process a table

Assume that you have successfully opened the sample project, and both the connections to the relational database and the tabular database are correct. Now you should have all the dimension tables and fact tables imported into the project. Before you deploy the project, try to process one table. With one table highlighted in the model, go to MODEL à Process à Process Table. All is good, the processing will complete without error.

Processing data means getting the latest data from external sources. The test is to make sure that your source connection works as planned.

A bit more information about processing data. When authoring your model project in SSDT, you can only manually initiate the process actions. After a model has been deployed, process operations can be performed by using SQL Server Management Studio or scheduled by using a script.

Now deploy the tabular project by right-clicking on the project, and choosing Deploy. If all is good, you should see this dialog when the deployment is complete.

dax2 deploy success

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Validate the deployment in SSMS

Now go to SSMS, you should see two tabular model databases as shown in the screenshot in the previous section.

Let’s open up a MDX query window by right-clicking on AdventureWorks Tabular Model SQL 2012, and choose Mew Query – MDX.

dax2 Query

 

 

 

 

 

 

The DAX function EVALUATE is kind of like the SELECT * FROM command in T-SQL. It can be used to get the data in result set from a table.

Try this and you will see all the customers returned:

evaluate Customer

If you are familiar with MDX, the following MDX query should also work.

select
{} on 0,
[Customer].[First Name].[First Name].members on 1
from
[Internet Operation]

Now that the installation of the sample Adventure Works tabular model database is done, you are all set to learn DAX.

SQL #53 – Keyboard shortcut Alt + F1 works now in SSMS 2012

April 30, 2014 Leave a comment

Keyboard shortcut Alt + F1 works now

SSMS has a rich set of keyboard shortcuts that works by default. But in SSMS 2008 I had one complain about the default Alt+F1 shortcut. The Alt+F1 shortcut is assigned to sp_help by default.  It worked for tables, sort of. If you are a good designer and had followed the best practice of putting your tables in the appropriate schemas, using the Alt+F1 shortcut is quite a pain. You will need to wrap your schemanized table as the following for the shortcut to work.

[HR.Empoyees]

Check out this older blog.

SQL #49 – Retrieve table structure with keyboard shortcut in SQL Management Studio (SSMS)

Not any more in SSMS 2012. The fix is finally done. Either of the following will work by default. The old way of wrapping with [ ] will no longer work.

  • [HR].[Empoyees]
  • HR.Empoyees

This shortcut is equivalent to execute the sp_help procedure.

sp_help ‘[HR].[Empoyees]’

Assign keyboard shortcut to sp_helptext

The system procedure sp_helptext is a great helper for developers who work on SQL codes day in and out. I used to have Ctrl+F1 mapped to it. Unfortunately, by default, in SSMS 2012, the shortcut Ctrl+F1 is now mapped to Display SQL Server Books Online.

SQL Server Management Studio Keyboard Shortcuts

There are people who tried to re-map it. But it just seems a bit too much to re-map it. So I decided to map the Ctrl+3 key to sp_helptext.

Highlight both of the following text and press Ctrl+3, you will get the procedure definition.

  • [sp_helpfile]
  • sp_helpfile

 

It is equivalent to executing sp_helptext.

sp_helptext ‘[sys].[sp_helpfile]’

DAX #1 – Learning DAX

April 25, 2014 3 comments

I recently attended a virtual training of Tabular and Power Pivot for Developers taught by Kathy Vick at Pragmatic Works. Kathy had a comment (also an inside joke for developers I think) that for those who already have multidimensional cube and MDX skills, learning about the tabular (and PowerPivot, DAX) model doesn’t make any sense.

Putting the inside joke aside, I think the comment did raise a question in my mind. Why do I need to learn the tabular model? After some thinking, I came up with this answer, to learn DAX.

After 4 days training, and many slides and many demos, I realized that in the back of mind, what I really wanted to learn is another query language, DAX (Data Analysis Expressions).

Yes, this is on top of two query languages, SQL and MDX, most SQL Server developers are already familiar with.

A simple Googling and Bing, the search results are quite interesting.

  • SQL Server 2012 SQL language: over 15 million results.
  • SSAS MDX language: adequately 163 thousand results
  • SSAS DAX language: barely 52 thousand results.

MDX # 40 – CROSS JOIN does not return Cartesian product of two hierarchies from the same dimension

April 15, 2014 2 comments

Correction

4/25/2014

Thanks to Chris Webb’s comment (see the comment section). I am making a correction on this blog.

The title of this blog contains a few important words, “from the same dimension”. You might have guessed the opposite side of it is hierarchies “from different dimensions”.

But my correction is not about this. In trying to explain why CROSS JOIN in MDX does not return Cartesian product of two hierarchies from the same dimension, I originally stated:

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best. 

I highlighted “fact table” above because that is what made my statement incorrect in the context of two hierarchies from the same dimension. When we crossjoin two sets of members from hierarchies from the same dimension, the fact table(s) does not come into play, rather, it’s the validate combinations from the dimension table(s) that will determine the resulting tuples.  In this case, the Multi color is the only color for the Bib-Shorts. This can be verified by joining the two dimension tables, DimProduct and DimProductSubcategory. Here is the SQL query and the result.

Multi-Bib-shorts-correction

 

 

 

 

 

Here is the original post.

Cartesian product can be illustrated by a Cartesian square

Most SQL developers are familiar with the concept of CROSS JOIN and Cartesian product. Cartesian product is the result that is produced from a CROSS JOIN.

In this Wikipedia article, Cartesian product, a Cartesian product is illustrated by a Cartesian square as shown below.

Cartesian Square

 

 

 

 

 

 

 

In SQL Server, CROSS JOIN returns the Cartesian product of rows from tables in the join

Both of the following articles claimed that the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

The above definition of the SQL CROSS JOIN can be illustrated with the following SQL script using the AdventureWorksDW database.

use AdventureWorksDW
go

with color as (
select      distinct Color
from  dbo.DimProduct
),

sub as (
select      distinct EnglishProductSubcategoryName
from  dbo.DimProductSubcategory
whereEnglishProductSubcategoryName = ‘Bib-Shorts’
)

select      *
from  sub CROSS JOIN color
order by
EnglishProductSubcategoryName, Color
–10

To simplify the result set, I am using only one product subcateogry ‘Bib-Shorts’. The above SQL script generates the results shown in the screenshot below. With 1 product category and 10 product colors, the result set is the every possible combination of these two lists, which is 10 (1 * 10) rows.

Multi-Bib-shorts

 

 

 

 

 

 

 

 

 

 

Keep in mind that the above 10 combinations of the colors and the subcategory do not necessarily have any sales. In order to find witch combinations have sales, we will need to do an INNER JOIN on these 3 tables:

dbo.FactResellerSales or dbo.FactInternetSales
dbo.DimProductSubcategory
dbo.DimProduct

The following 2 SQL queries can be used. It turned out that there is no internet sales for multi color bib-shorts, but there are reseller sales for them.

— There is no Internet Sales for Multi color Bib-Shorts
select      *
from  dbo.FactInternetSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–0

— There is 756 Reseller Sales for Multi color Bib-Shorts
select      *
from  dbo.FactResellerSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–756

In MDX, CROSS JOIN does not return the Cartesian product members from hierarchies

Enter the world of MDX. CROSS JOIN is an important operation in MDX, with some twists and turns.

The MDX query below is the equivalent of the SQL CROSS JOIN in the previous section. However, the result set contains only one row.

select
{ } on 0,
{ CROSSJOIN(
[Product].[Subcategory].[Subcategory].[Bib-Shorts],
[Product].[Color].[Color].members
)
} on 1
from
[Adventure Works]

Multi-Bib-shorts Crossjoin

 

 

 

 

 

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best.

Please refer to the section ‘Finding related members in the same dimension“ in Chapter 5 of our book, MDX with SSAS 2012, to gain more knowledge on this topic and how it is related to the concept of auto-exists and the EXISTS and EXISTING function.

Three different ways to do crossjoining

In MDX, you can cross join different hierarchies using three different ways. Chris Webb has a blog here, The rather-too-many ways to crossjoin in MDX, which is better than anything I can write about CROSS JOIN.

 

MDX # 39 – Find Default Measure using MDX Query

April 15, 2014 Leave a comment

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.

For more information about how to define a default member for hierarchies and whether  you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:

Define a Default Member

MDX script is an easy place to maintain the default member definition

As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.

Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.

/*– Set default member for the Destination Currency cube dimension —-*/

Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

MDX query is also an easy place to find what the default members are for each hierarchy

As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:

DefaultMember (MDX)

The general syntax is:

Hierarchy_Expression.DefaultMember

Three Sample MDX queries to find default members for hierarchies and cube default measure

The following are three examples to find default members for hierarchies and the cube’s default measure.

  •  Find Default Member of Destination Currency hierarchy

WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Destination Currency hierarchy: US Dollar

  • Find Default Member of Geography hierarchy

WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Geography hierarchy: All Geographies

  • Find Default Measure of the Adventure Works cube

WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name

SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
— Default cube measure: Reseller Sales Amount

Showing default measure and the value in one query

By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.

SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]

Reseller Sales Amount
$80,450,596.98

 

 

%d bloggers like this: