All posts by Dustin Ryan

Dustin is a Data Platform Solutions Architect on the Education team at Microsoft. You can find Dustin blogging and speaking at events like SQL Saturday, Code Camp, and SQL Rally. Follow Dustin on Twitter @SQLDusty.

Top 3 Simplest Ways To Improve Your MDX Query

Learning to write MDX is difficult enough, but learning to write efficient MDX and performance tune an MDX query can be even more of a challenge. With that thought, I wanted to put together a few tips that can help you improve the performance of your MDX calculations.

1. Subdivide your calculations

For example, imagine you have an MDX query that looks like this one found in the AW cube:

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance]

As Case
        When IsEmpty
             (
                (
                  [Measures].[Amount],
                  [Scenario].[Scenario].[Budget]
                )
             )

        Then Null

        When [Account].[Accounts].CurrentMember.Properties(“Account Type”) = “Expenditures”
             Or
             [Account].[Accounts].CurrentMember.Properties(“Account Type”) = “Liabilities”

        Then ( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
             –
             ( [Measures].[Amount],[Scenario].[Scenario].[Actual] )

        Else ( [Measures].[Amount],[Scenario].[Scenario].[Actual] )
             –
             ( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
    End,
 
Format_String = “Currency”

You’ll notice the expressions ([Measures].[Amount],[Scenario].[Scenario].[Budget]), as well as ([Measures].[Amount],[Scenario].[Scenario].[Actual]), appear multiple times in the above calculation. Because a part of a calculation cannot be cached, each time this expression appears in the calculation, it has to be recalculated. We can subdivide this calculation into multiple calculations that can be individually cached the first time they are run.

Create Member CurrentCube.[Scenario].[Scenario].[Budget Amount] as
    ( [Measures].[Amount],[Scenario].[Scenario].[Budget] );

Create Member CurrentCube.[Scenario].[Scenario].[Actual Amount] as
    ( [Measures].[Amount],[Scenario].[Scenario].[Actual] );

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance]

As Case
        When IsEmpty
             (
                [Scenario].[Scenario].[Budget Amount]
             )

        Then Null

        When [Account].[Accounts].CurrentMember.Properties(“Account Type”) = “Expenditures”
             Or
             [Account].[Accounts].CurrentMember.Properties(“Account Type”) = “Liabilities”

        Then [Scenario].[Scenario].[Budget Amount]
             –
             [Scenario].[Scenario].[Actual Amount]

        Else [Scenario].[Scenario].[Actual Amount]
             –
             [Scenario].[Scenario].[Budget Amount]
    End,
 
Format_String = “Currency”;

Now after the first time the measures Budget Amount and Actual Amount are calculated, they can be cached instead of having to be recalculated all over again.

2. Replace IIF functions with MDX scripting

If your calculation uses an IIF function to test for a specific location in the cube space, chances are it can replaced with better performing MDX scripting. Examples:

a. If the Current Member is in a specific level
b. If the Current Member is a certain member
c. If the Current Member has a certain parent

Here we have a calculation from the Adventure Works cube:

CREATE
  MEMBER CurrentCube.[Measures].[Ratio to Parent Product] AS
    IIF(
        [Product].[Product Categories].CurrentMember.Level.Ordinal = 0
      ,1
      ,[Measures].[Sales Amount]
        /
          (
            [Product].[Product Categories].CurrentMember.Parent
           ,[Measures].[Sales Amount]
          ))
   ,Format_String = “Percent”
   ,Associated_Measure_Group = ‘Sales Summary’ ;

The IIF function is testing for the very top level of the hierarchy. We can rewrite this query to eliminate the IIF function:

CREATE
  MEMBER CurrentCube.[Measures].[Ratio to Parent Product] AS
    [Measures].[Sales Amount]
        /
          (
            [Product].[Product Categories].CurrentMember.Parent
           ,[Measures].[Sales Amount]
          )
   ,Format_String = “Percent”
   ,Associated_Measure_Group = ‘Sales Summary’ ;
  
SCOPE ([Measures].[Ratio to Parent Product],[Product].[Product Categories]);

    THIS=1;
    FORMAT_STRING(THIS)=”Percent”;

END SCOPE;

By using the SCOPE statement, we can still set the top level of the of the Product Categories hierarchy to 1 and eliminate the IIF statement.

3. Don’t use Set Aliases in your calculations

Set Aliases are when you assign a set a name by creating a named set. Named sets are handy when you must define a set multiple times. But there’s a catch when using named sets: Using a named set in a calculation disables block computation. So take this query for example:

with set [SE States] AS

{[Geography].[State-Province].&[FL]&[US],
[Geography].[State-Province].&[GA]&[US],
[Geography].[State-Province].&[SC]&[US],
[Geography].[State-Province].&[TN]&[US]}

member [Measures].[SE States Sales] as

SUM([SE States],[Measures].[Reseller Sales Amount]),
format_string=”currency”

Select [Measures].[SE States Sales] on 0,

[Date].[Calendar Year].Members on 1

From [Adventure Works]

This calculation is calculated cell by cell because of the named set. If you “Analyze” this query in Mosha’s tool, MDXStudio (which is awesome and you should download now), you will see the warning, “Applying aggregation function Sum over named set [SE States] – this disables block computation mode.” Because its always possible to remove a named set, we should rewrite this query to use block computation:

with member [Measures].[SE States Sales] as

SUM({[Geography].[State-Province].&[FL]&[US],
[Geography].[State-Province].&[GA]&[US],
[Geography].[State-Province].&[SC]&[US],
[Geography].[State-Province].&[TN]&[US]},[Measures].[Reseller Sales Amount]),
format_string=”currency”

Select [Measures].[SE States Sales] on 0,

[Date].[Calendar Year].Members on 1

From [Adventure Works]

I hope you found this few simple tips useful. These tips are simple and easy to implement but can save you tons of query time.

Creating Your First Tabular Model (part 1)

With this post I begin a series of blog posts covering one of the most talked about features of SQL Server 2012: Tabular Modeling. Being new to this like most of us are, I hope to learn much about Tabular Modeling as we walk through the basics of building your first Tabular Model. In this installment, we’ll talk about what a Tabular Model is, when a Tabular Model is the right choice, and of course how to create a Tabular Model.

What is a Tabular Model?

With the release of SQL 2012, we are (re)introduced to tabular modeling. If you’re familiar with Power Pivot, you’re going to notice many similarities and will most likely pick up the tabular modeling aspect of SSAS pretty quickly. Basically a Tabular Model is an in-memory database in SQL Server Analysis Services. The Vertipag engine that was previously only used in Power Pivot is now utilized within Power Pivot and SSAS 2012 Tabular as xVelocity. The xVelocity technology allows you to perform complex analytics of your data all in-memory while making use of column store indexes. This eliminates expensive IO unlike SSAS Multi Dimensional Modeling where IO is a viable concern.

The Tabular Model also allows us to bring together multiple data source types very easily, similarly to Power Pivot. Bringing together data stored in a SQL Server Database, Oracle, Excel, and Access is not only possible but straight forward.

1 Data Source Types

Once you’ve imported your data from whatever sources you need, defining relationships is very easy. Simply dragging and clicking an arrow from one object to another is all that is required here.

2 Creating Relationships

When Do I Choose Tabular Over Multidimensional Modeling?

You might be asking yourself, “Self, if Tabular is so fast and great, why would I ever use Multidimensional Modeling?” That’s a valid question, so lets go over some of the perks of each and when one or the other would the optimal choice.

1. If you need access to many different external data sources, choose Tabular. Multidimensional can do this to an extent, but if you need to relate an Excel spread sheet, a text file, an SSRS Report Feed, and your database data, Tabular is the way to go here.
2. If you need complex calculations, scoping, and named sets, choose Multidimensional.
3. If you need mind numbing speed and consistently fast query time, choose Tabular.
4. If you need Many-to-Many relationships, choose Multidimensional. You can model this relationship type in Tabular, but Multidimensional is still easier to create and manage these more complex relationships.
5. If you are planning on using Power View, choose Tabular. At this time its impossible to build Power View reports against a Multidimensional model, but that could change in the future.
6. If you don’t know DAX and want to use Tabular, either take the time to learn or use Multidimensional ; ) .
7. If your solution requires complex modeling, choose Multidimensional.

Take these points into consideration when choosing Tabular vs. Multidimensional. This isn’t every single consideration to think about, but should at least get you started in understanding the differences between Tabular and Multidimensional.

How Do I Create a Tabular Model?

So now that we have a general understanding of what the Tabular Model is and what are some of scenarios we should choose to use the Tabular Model, lets start creating our first Tabular Model.

For you to play along with my example, you’re going to SSAS 2012 installed in Tabular Mode.

Select Tabular Mode

If you’re not sure if your instance of AS is in Tabular Mode, just connect to Analysis Services in SQL Server Management Studio and look at the icons next to your instance of SSAS.

Tabular and Multidimensional Instances

The Tabular SSAS instance has the nifty little blue icon and the Multidimensional instance has the same icon as before in 2008.

You will also need SQL Server Data Tools and the AdventureWorksDW2012 sample database.

So first things first. Open SQL Server Data Tools.

3 Open SSDT

Then go to File, select New, then click Project.

4 Create project

Under Business Intelligence, highlight Analysis Services, and select Analysis Services Tabular Project. I’m naming my project FirstTabularProject. Then click OK.

5 Create SSAS Tab Project

After clicking OK you can see the new project in the Solution Explorer with an empty model, Model.bim.

6 Model in Solution Explorer

With the project created, your empty model should be open in the Designer Window. So now its time to create a connection to our data source(s). In the top left of the menu tool bar, click the Import From Data Source icon.

7 Import From Data Source Icon

Then select the type of data source you want to connect to. In my case, I’m connecting to a SQL Server database. Select Microsoft SQL Server and click Next. Specify the Server name, the credentials, and the Database.

8 Connect to SQL Server

After clicking Next, we must specify the Impersonation Information. These are the credentials that Analysis Services will use to access the data source when importing and processing the data. We can either specify specific credentials or tell it to you use the AS Service Account. I’m specifying credentials.

9 Impersonation Info

On the next screen, we need to choose how to import the data. We have two options: We can either select from a list of the tables and views which objects we’d like to import or we can write a query to specify the data to import. I’m selecting from the list of tables.

On the Select Tables and Views screen, you’ll see a list of the Tables and Views in your database. I can browse through this list and places checks next to all the tables and views I’d like to import. Or I can select a table and then click the button Select Related Tables. This will use the referential integrity of the database to determine which tables to check for you. Be careful clicking Select Related Tables. If you accidently click the button and the wizard selects 20 other tables, there’s no easy way to unselect the newly selected tables. I’ve selected FactInternetSales and allowed the wizard to select the dimensions based on the referential integrity.

10 Select Tables

Important:

Before clicking Finish, you’ll want to make sure that you highlight each table you want to import and then click the Preview & Filter button. This will allow you to not only preview the data, but also uncheck any fields that you do not wish to import into your model. This is important since the database will be stored in memory. We do not want to store any unnecessary data that we do not have to. You can see that I’ve gone through the FactInternetSales table and unchecked the fields I don’t want to import.

11 Uncheck unwanted fields

After filtering out the unnecessary fields, click Finish. The importing of the data will begin.

12 Importing Data

Once it is finished, click Close. You’ll notice the data has been imported and is now viewable in the Designer Window.

If after importing your data you decide you need to bring in another table from the same data source, click the Existing Connections icon.

13 Existing Connections icon

Then click Open and you are able to add new tables, views, or named queries to your model.

In the Designer Window we have two views. The Grid view allows us to see the imported data, with each table on an individual tab.

14 Grid View

We can also switch to the Diagram View by clicking the Diagram View icon at the bottom right of the Designer Window. The Diagram View is ideal for viewing all the imported tables and their relationships at one time.

15 Diagram View

So now that we’ve imported in our data, we need to add some measures to our model. Switch back to the Grid view and click over to the tab for the fact table, FactInternetSales. Select the first text box in the Measures Grid directly below the Sales Amount field. If the Measures Grid is not visible, just click the Show Measures Grid icon to toggle it back on.

17 Measures Grid

After highlighting the text box beneath the Sales Amount field, click the Sum (Sigma) icon. This will automatically create a measure with an aggregation type of Sum. Then go into the properties of your new measure and give it a friendly name since this is the name that your users will see when browsing the cube.

16 Add a measure

Then do the same for the Order Quantity field.

Now lets deploy and process our model. By default, the model will be deployed to default instance of SSAS on the local machine. We can change the server we want to deploy to by right-clicking the project in the Solution Explorer and selecting properties. You can also change the name of the database that will be created when you deploy the Model.

18 Project Properties

In the Menu bar, click Build, then click Deploy ;.

19 Deploy Project

This will begin the deployment and processing steps. If you specified specific credentials to use for impersonation, you’ll need to enter the user’s password during this step.

20 Deploying

Once the model is deployed, we can now view our model deployed to the AS server and browse it with Excel. Click the Analyze in Excel icon and your model will open in Excel so you can browse it.

22 Analyze in Excel

We’ve created our first Tabular Model. I hope this gives you a good introduction on what Tabular is, when Tabular is the right choice, and the basics of creating a Tabular Model.

In the next article, we’ll get more into modifying our model by building hierarchies in our dimensions and other more advanced topics so stay tuned for the next article. And as always, post any questions or comments here and I’ll answer them as best I can.

Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!

With the release of SQL Server 2012 comes a whole host of improvements to Integration Services that makes development and administration of your SSIS packages much easier. And it also looks new and shiny, so you can’t beat that.

After you’ve upgraded to SQL Server 2012, you’ll have to start converting your SSIS packages to 2012, which isn’t all that difficult. There are, however, some fundamental changes to the way packages are built, configured, and managed that are important to know. In this article, we’re going to walk through upgrading an SSIS 2008 project to 2012.

First, lets open SQL Server Data Tools.

0 open SQL Server Data Tools

Then we’ll need to open our existing SQL Server 2008 Integration Services project.

1 open project

Once the navigate to the project and open it, the Visual Studio Conversion Wizard will open. This will walk you through converting the 2008 IS Project to 2012.

2 VS conversion wizard

After clicking Next, you’ll be asked if you want to create a Backup. I opted to create the Backup in case something went horribly wrong. Hopefully you’re packages are in source control so you have something to fall back to, but I didn’t convert the original packages anyways. I created a copy of the project and then converted the copy in order to preserve the original project.

3 backup packages

Click Next a couple times and surprisingly, or not, another wizard will open. The SSIS Package Upgrade Wizard is where we’ll select which packages we want to upgrade to 2012.

4 ssis package upgrade wizard

Place a check mark next to the packages you wish to upgrade. In my case, I only have one package in this project, but if I have multiple packages I would see those packages displayed in the window below.

Also, this is where you will specify a password if the packages are encrypted with one.

5 select packages give pw

On the next screen of the wizard, there are a couple of important things to point out here.

6 select ssis package mngmt options

The first option, “Update connection strings to use new provider names”, allows the upgrade wizard to update any connection strings to use the SQL Server 2012 Native Client Library. This will upgrade any connections stored in your package. What this won’t upgrade are package configurations or any expressions that may be altering a connection manager. Those will need to updated manually.

Also, the last option, “Ignore configurations”, basically allows you to tell the wizard to not warn you about potential problems with package configurations on connection managers. I left this option unchecked because I want to know which packages have configurations on the connections that could potentially cause problems.

In the Upgrade Report, you’ll notice the first informational message indicates that the provider was upgraded to SQLNCLI11. Also, the upgrade wizard detected a package configuration attempting to configure the connection. We either need to upgrade the package configuration to use the new provider name or remove the configuration.

7 view upgrade report

If I open the package in Data Tools, you’ll see the following error due to the lack of support for the old provider.

8 package config provider error

So at this point we have two options. The first option is to update the package configuration and continue using our package configuration with our 2012 packages. Or we can take advantage of a brand new feature in SSIS 2012: Parameters! With your package open in Data Tools, you’ll notice the Parameters tab. This is where you’ll manage your parameters that are scoped to the package. Parameters that are scoped to the project will appear in a seperate .params file that can be seen in the Solution Explorer. Parameters are designed to replace package configurations and they function like you’d imagine a parameter would. Parameters can be assigned values and the parameters can be shared between packages in the project.

9 create parameter for package config

Before we can start using parameters in our packages, we must first convert our entire SSIS project to use the Project Deployment Model. The Project Deployment Model is also new to SSIS. Basically, with the Project Deployment Model, an SSIS project is the unit of deployment for the project, whereas with the legacy deployment model, Package Deployment Model, a package was the unit of deployment. The Project Deployment Model is how we will be able to share parameters between packages. Read this article to learn more about the Project Deployment Model. If your SSIS project is currently set to the legacy Package Deployment Model, the project icon should look like this:

16 package deployment model icon

Once you’ve converted to the Project Deployment Model, the icon should look like this:

17 project deployment model icon

To upgrade your convert to the Project Deployment Model, right-click the project in the Solution Explorer and click Convert to Project Deployment Model.

10 convert project to project deployment model

This will open, you guess it, another wizard. You’ll first select the packages to include in the project. You’ll also need to specify a few project properties, like the name of the project and the encryption level. If you are upgrading any packages with Execute Package Tasks in them, you should upgrade those to use a project based reference.

The important part of the Project Conversion Wizard is converting the package configurations to Project Parameters. On the Select Configurations screen, check the package configurations that you wish to convert to parameters.

11 convert package configs to parameters

Next, you’ll need to create the parameter that will replace the configuration. Any configurations properties in the configuration file(s) will be shown here. Specify the Parameter Name and the Scope.

The Scope can either be at the Project or Package level. If you create the Parameter at the Project level, the parameter will be viewable in the Project.params file, which you can find in the Solution Explorer. If the scope is the Package level, the parameter will be found in the Parameters tab of the package.

12 create parameter

On the Configure Parameters screen, you can alter the details of the parameter. If you click the ellipses button next to the parameter, you can view the parameter details. At the bottom of this window is a check box that says “Required”. A parameter that is Required does not have a default value and must be specified at run. If the parameter is not Required, the parameter does not need to be set for the package to execute.

13 set parameter details

Because I select the Project as the scope of my parameter, I now have a .params file that houses all my project level parameters.

14 project params

If I open this file, I can see the new parameter that was created based on my package configuration.

15 parameter

And if I look at the expression on my connection, I can see the reference to the parameter.

18 parameter referenced by expression

One other really cool thing about 2012 is that objects that have an expression on them now have a little icon indicating the expression on a property of the object. Its about time.

19 expression flag

I hope you found this helpful and that you now have a basic understanding of converting your legacy SSIS packages to 2012. Check out this white paper that has some great information on upgrading to 2012. And if you have any questions or comments, please post them!

SQL Rally, Here I Come!


On May 10th and 11th, all SQL hell is breaking loose in Dallas, Texas at SQL Rally! It’s sure to be an incredible event, with tons of experts presenting on an amazingly wide range of SQL-y topics. There will be everything from PowerShell to development to Business Intelligence to SQL 2012 stuff. If you’re not signed up for this great even, make sure you do that soon so you still get the early bird pricing, which ends March 15.

As if attending this great even wasn’t enough, I’ll also be presenting at SQL Rally with Adam Jorgensen! We’ll be presenting a session called, “Zero to Cube – Fast Track to Analytics”. This session is basically 100% demo and walks you through getting up and running on Analysis Services 2008 R2 and 2012 right away.

So head over SQLRally.com and get signed up if you aren’t already. And if you are, make sure you attend our session, “Zero to Cube – Fast Track to Analytics”!

Ten MDX Calculations For Your Cube (part 2)

In this post I’ll conclude what I began in an earlier blog post covering ten of the most common cube calculations implemented for some of my clients. You can read the first blog post in this 2 part series here, which covers the first five calculations (in no particular order). As before, these calculations will be written against the Adventure Works 2008 R2 sample AS project.

5. Percent of a Parent

This is very common calculation that I’ve seen used countless times to determine what percentage of a measurement makes up the total measurement for a larger body. In this example, I’m calculating what percent of Internet Sales for a product make up the total Internet Sales for the product’s Sub Category.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percent of Parent]
AS
Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Internet Sales Amount]
)
Then Null

// Test for current coordinate being on the (All) member.
When [Product].[Product Categories].CurrentMember.Level Is
[Product].[Product Categories].[(All)]
Then 1

Else ( [Product].[Product Categories].CurrentMember,
[Measures].[Internet Sales Amount]  )
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )

End
,
FORMAT_STRING = “Percent”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

4. Rolling Average Calculation

Another common calculation used is one that calculates an average over the period of time, such as the monthly average of the past 12 months.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Monthly Average Over Year]
AS

    Avg
(
[Date].[Month Name].CurrentMember.Lag(11) :
[Date].[Month Name].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

3. Percentage Growth From Previous Period

If you’re wanting to measure the percentage of growth of a measure from a previous period, this is the calculation to use. A couple things to keep in mind: This calculation is hierarchy specific and is currently set up to measure the percentage of growth from the previous calendar year.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Growth From Previous Period]
AS Case
// Test for current coordinate being on (All) member.
When [Date].[Calendar].CurrentMember.Level Is
[Date].[Calendar].[(All)]

Then “NA”

// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
Then Null

Else (
( [Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount])

(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
/
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
End,
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

2. Period to Date Average

This calculation is very similar to #4 up above but instead of giving you the average Internet Sales for the past 12 months, the calculation will return the average Internet Sales for the current period. For example, if you view this calculation at the day level, you will see the average daily Internet Sales for the current month.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Over Current Period]
AS

Avg
(
[Date].[Calendar].CurrentMember.FirstSibling :
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

1. Top Products Percent of Sales

The last calculation I have to show you utilizes a named set to return to the top 10 best selling products. Many clients like to monitor the sales of their best selling products this way.

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Internet Products]
AS TopCount
(
(

         [Product].[Product].Children
)
,10
,[Measures].[Internet sales amount]
), DISPLAY_FOLDER = ‘Sets’  ;
   

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount]),
FORMAT_STRING = “CURRENCY”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

You can also add a calculated measure to show the percentage of sales of those 10 best selling products.

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Percent of Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount])/
[Measures].[Internet Sales Amount],
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

If you have any other calculations which you think should be on this list and would love to share them, please send them my way. I’d love to see ‘em!

If you don’t consider yourself an MDX expert or are looking to eliminate some of the calculation development time, I’d highly suggest checking out BI xPress. BI xPress has a great feature built in that allows you to quickly and efficiently add calculations and named sets to your cube, all without the knowledge of MDX. Heck, most of the calculations in this blog post were written in about 10 seconds with the BI xPress Calculation Builder.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

2-Click-BI-xPress-icon_thumb

This will open the MDX Calculation Builder Wizard. Select the Set template you’d like to use. I’m selecting the Top 10 Count template.

image

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

image

Lastly, select the measure.

image

And its really that easy.

image

To download the free trial of BI xPress, head over to PragmaticWorks.com and check out BI xPress. You won’t regret it after you see how much time it will save you with your SSAS and SSIS development.

Using the Descendants Function

The MDX function, Descendants, is a very versatile and commonly used function that returns the members at, below, or even above a specified level.

The first argument of the descendants function is a member or set expression. The second argument can either be a specific level expression or a number indicating the number of levels to travel. The following queries return the same results:

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    2) 
    on 1
From [Adventure Works]

GO

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter]
    ) 
    on 1
From [Adventure Works]

 

Results:
image

The Descendants also accepts a third optional argument: a string expression to distinguish between possible descendants to return in the results. This query uses the SELF_AND_AFTER flag to returns members at the specified level as well as members subordinate to the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    SELF_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image 

And this query uses the BEFORE_AND_AFTER flag to return all members except for the members of the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    BEFORE_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image

Notice that the specified level is excluded.

Also, if I use the LEAVES flag, I can return all the leaf members between the specified member and the specified level. Alternatively, if you don’t specify a level, the members at the lowest level (the leaf members) will be returned.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    ,
    LEAVES
    ) 
    on 1
From [Adventure Works]

Results:
image

Check out MSDN for more information on the Descendants function.

SQLRally Voting Opens to the Community

PASS SQLRally 2012 voting is now open! There’s tons of great sessions out there and many of them have been submitted by some of my good friends here at Pragmatic Works. While voting look for great sessions presented by Pragmatic Works experts like

  • Adam Jorgensen
  • Chris Albrektson
  • Gareth Swanepol
  • Dan Clark
  • Brad Schacht
  • and me, too!

Make sure you head over to SQLRally.com and cast your vote! You can vote for as many sessions as you want but you can only vote once, so make your selections wisely! Yours truly has also submitted a few sessions that are currently up for vote! Here are my sessions up for vote and their descriptions.

Zero to Cube – Fast Track to Analytics
This session has been updated for SQL Server 2012. It is consistently a top session from previous events like SQLRally and PASS Summit. Come interact with your speaker and audience like you never have before. This session is 100% demo, packed full of fun and will get you up and running on Analysis Services 2008R2 and 2012 right away!

SSIS For Your Data Warehouse
The ability of SQL Server Integration Services to easily bring together multiple data sources makes it the perfect ETL tool for loading your data warehouse. Loading your data warehouse quickly and efficiently, however, is not always easy. In this session, we’ll explore some advanced techniques for loading your data warehouse and help you overcome some of the learning curve associated with SSIS. The topics discussed will include handling late arriving facts, loading slowly changing dimensions efficiently, and more.

SSIS Unleashed: Expressions and Variables Edition
One of the most powerful aspects of SSIS is the ability to create dynamic and powerful packages utilizing package variables and the SSIS expression language. Unfortunately, the SSIS expression language is also one of the more difficult features to learn regarding SSIS. In this session we will walk through the syntax of the SSIS expression language, common uses for SSIS expressions, as well as some more advanced ways to fully realize the power of SSIS.

Tackling the SSRS Expression Language
The SSRS expression language is one of the most powerful pieces when it comes to creating dynamic reports. Unfortunately, for reporting developers the SSRS expression language is also one of the more difficult features to use regarding SSRS. During this session we will walk through the syntax of the SSRS expression language, how and where to use the expressions, as well as some advanced ways to truly reveal the true power of your reports.

Now that you’re informed, go cast your vote!

BI Documenter 4.2 Released

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.

BI xPress Version 3.2.1 Released

Earlier this month on the 14th, version 3.2.1 of BI xPress was released. With this release comes a few nice improvements that increase the ease of use.

One of the major improvements with this release (and to be honest its about time) is the ability to view packages that have been stopped by some external source (i.e. the user, the operating system, etc.). The stopped packages will now appear highlighted in blue in the Package Execution viewer. Previously it was impossible to tell which packages had been stopped by the end user, but with this improvement it’s not possible to tell which package has been manually stopped by the end user.

Packages that have not started execution are white, packages currently executing appear in yellow, packages that have successfully executed appear in green, packages that have failed are red, and packages that have been stopped are now blue.

One of the other nice improvements is the ability to detect invalid file paths for configuration files. If the file path for a configuration file cannot be found, the configuration will appear underlined and highlighted in red in the Configuration Manager.

There’s a host of other improvements and bug fixes in this release. To read the full release notes, go here. To download the trial version of BI xPress, head over to PragmaticWorks.com.

Adventure Works Sample Databases for SQL 2012 RC0 Released

Early last week Microsoft released the Adventure Works samples for SQL Server 2012 RC0. Head over here to get the download. These sample databases only work with SQL Server 2012 RC0.

I love using these sample databases in my blog posts since they’re available to download for free and it makes it easy for those trying to follow along. The Adventure Works samples are also great for trying to recreate difficult issues my clients may be facing so I can have a better understanding of how to tackle the problem. If you’re serious about learning MS BI and SQL Server, you should definitely have some kind of sample database to play around with and test, so why not the AW sample since everyone has access to it.

The AW samples released for SQL Server 2012 RC0 use the AdventureWorks2008R2 and AdventureWorksDWDenali databases. There are no schema or data changes for in this release of the samples, except for additional date data in the DimDate table and an extra fact table, FactProductInventory, which contains inventory table. Download the sample databases here.