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.
MDX, Pragmatic Works

MDX Calculation Builder Automatically Builds MDX Calculations for You

One of the most powerful aspects of Analysis Services is the ability to easily view your facts over time. What’s not always so easily is writing those calculations that make time analysis possible. MDX can have a steep learning curve, which is where the BI xPress MDX Calculation Builder by Pragmatic Works steps in.

After installing BI xPress, you will very quickly and easily add calculations to your cube without having to write a lick of MDX. To access the MDX Calculation Builder, open BIDS and open your SSAS project. After opening the cube you would like create the calculations in, navigate to the Calculations tab of the cube designer. There you’ll see the Calculation Builder icon.

10-30-2011 10-22-07 PM

After you click the Calculation Builder icon, the Calculation Builder wizard will open. The first thing you’ll need to do is select a calculation.

10-30-2011 10-29-37 PM

You’ll have about a dozen different calculations to choose from, including Year to Date, Month to Date, and Year Over Year Growth, just to name a few. In this example, we’re going to create a Year Over Year Growth calculation. Next we’ll need to select our fact.

10-30-2011 10-36-48 PM

Then we’ll select our Year level attribute.

10-30-2011 10-41-19 PM

The third step is optional. We can apply conditional formatting to our calculation. In this example, if the measure is less than 0, the font will display red. Greater than zero, the font of the measure will display as green.

10-30-2011 10-46-34 PM

Lastly, we’ll give our measure a name, select the measure group we’d like the measure to be a part of, select the formatting, and preview the calculation that was written by the tool.

10-30-2011 10-49-13 PM

Then we’ll click Finish. The calculation has been added to our calculation script within our cube.

10-30-2011 10-50-58 PM

After processing the cube, we can test out our Year Over Year Growth.

10-30-2011 10-58-48 PM

And its that easy. In just four simple steps we’ve created our calculation without writing a bit of MDX.

The MDX Calculation Builder also allows you to build named sets just as easily. Of course, as you learn MDX, the MDX Calculation Builder won’t always be necessary. But it sure will accelerate your learning and make writing MDX calculations easier in the mean time.

Free Training, MDX, SSRS

SQL Saturday #86: BI Edition in Tampa, FL – Reporting on a Cube w/ SSRS 2008

Next month on November 5th is SQL Saturday #86 in Tampa, Florida. But this is no ordinary SQL Saturday. This is SQL Saturday: BI Edition! Which means this SQL Saturday is going to be even more amazing, hard core, and in your face than usual! If you’ve never been to a SQL Saturday event, let me just say that you are missing out. And if you have been to one before, then you know exactly how awesome these events are. Whether you show up for the incredible FREE training from qualified industry pro’s, the networking with fellow SQL Server fiends, or the great lunch and snacks, you’re sure to have a good time.

So before you waste anymore time, head on over to their site and get registered. Check out the extensive list of sessions and speakers and plan your day.

Yours truly will be giving a session covering Reporting on a Cube w/ SSRS 2008 at 10 a.m.  I’ll be covering some of the advantages the cube presents as a data source and how you can leverage the power of the cube with SQL Server Reporting Services 2008. So head on over to SQLSaturday.com and get registered before it’s completely full! We’re going to have a blast!

MDX, SSAS

MDX VBA Functions

The Visual Basic for Applications library functions are a great way to extend the functionality of your MDX  statements, allowing you to do some very handy stuff. Irina Gorbach has created a nice little document outlining the VBA functions available, so definitely check that out. There’s an important performance difference between the VBA functions and native MDX functions, which Irina points out.

One of the VBA functions I’ve been asked about by a couple people is the IsNumeric function. The IsNumeric functions returns a Boolean value indicating whether an expression can be evaluated as a numeric value. Here is an example (using the AW example cube) of how one might utilize IsNumeric:

With Member [Measures].[LastOrderYear] as

IsNumeric([Reseller].[Reseller].CurrentMember.Properties("Last Order Year"))

Select [Measures].[LastOrderYear] on 0,

[Reseller].[Reseller].children on 1

From [Adventure Works]

And here is what the results look like:

image

So check out the Irina’s document on the SSAS VBA functions. I think you’ll find it very informative and useful.

MDX, SSAS, SSRS

Showing an SSAS Hierarchy as Delimited String

I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors.

Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make this possible. Here an example I put together with the Adventure Works 2008 R2 cube so you can play along.

WITH MEMBER [Measures].[Management Structure] AS
            GENERATE(
            EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All]),
            [Employee].[Employees].CURRENTMEMBER.Name, "  >  ")

Select {[Measures].[Management Structure],
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Order Quantity]} on 0,

NON EMPTY(DESCENDANTS([Employee].[Employees],10,LEAVES)) ON 1

From [Adventure Works]

Where [Employee].[Title].&[Sales Representative]

Pay attention to the calculate member [Measures].[Management Structure]. The way the Generate function works is that it takes one set and applies that set to each Member in another set. In this case, our first set is the ancestor from each level in our hierarchy of the current member, excluding the All member.

So this our first set:

EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All])

Our second set is the Name property of the CurrentMember of the Employee hierarchy being displayed:
 
[Employee].[Employees].CURRENTMEMBER.Name
 
The Generate function actually also excepts a third argument: A delimiter. We can specify a delimiter. In my example, I use “  >  “ to make the string a bit easier to read. If we execute the query we can see the results of our calculated member.

10-10-2011 9-47-39 PM

Pretty cool, huh? So now that we have each employees management hierarchy in sweet, little string, we can easily display this in a report as a tool tip.

I added the tool tip to the cell in my report’s tablix that contains the employee name. Here’s the expression I used:

=REPLACE(Fields!Management_Structure.Value,">",VBCRLF)

This expression replaces the “>” with a carriage return, line feed. Here’s what our tool tip looks like:

10-10-2011 10-02-24 PM

Using this method, we can easily display the managers for each employee in a tooltip without having to waste any real estate with additional columns.

I hope somebody finds this useful. I’m out!

Pragmatic Works, SSIS

Manage, Save, and Share SSIS Expressions

If you’re even slightly familiar with SSIS you know that one of the incredible strengths of SSIS is the ability to make packages dynamic in countless different ways. One of the ways SSIS accomplishes this is through the use of SSIS expressions and the expression language.

BI xPress has a really useful feature called Expression Manager. The Expression Manager is a very cool feature that allows an SSIS developer to create, share, and manage custom SSIS expressions.

Creating your own SSIS expressions is very easy. Simply open BI xPress and click the Expression Manager button in the top right of the window.

Capture 2

Or if you’re already developing a package in BIDS, go to your variables window and click the Expression List icon.

Capture 3

Now that the Expression Manager is open, we can browse the canned SSIS expression or create our own. Let’s create our own custom SSIS expression for us to use later on or share with our development team.

First, click the Create New Expression icon.

Capture 4

Then give your expression a name and a description. My expression is going to append the user’s name and the date to create a file name. Here you can see the expression.

Capture 5

And in case you want to copy it for use:

"C:\\DataOut\\Archive\\"  + RIGHT(@[System::UserName],FINDSTRING(REVERSE(@[System::UserName]), "\\", 1)-1)
+ (DT_WSTR,4)YEAR(GETDATE()) + "-"
   + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
   + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"

In my expression you can see that I leverage the UserName system variable to get the name of the user executing the package. I use FindString and Reverse to eliminate the domain and \ from the UserName, as well. Once you’re done, click Save and then click OK to close the Expression Manager.

Now that our expression is saved, we can use our expression in our SSIS packages. To utilize our expression in the package all we need to do is the use the BI xPress Expression Editor. We can clearly see our new expression in the My Expressions folder.

Capture 6

Besides allowing a user to manage their own custom SSIS expression, the Expression Manager features a host of canned, commonly used SSIS expressions. This drastically reduces the learning curve of the expression language. The Expression Manager comes with expressions that can create a file path and name with the date appended, create a dynamic SQL statement, or calculate the beginning of the previous month, just to name a few.

Capture 1

As you can see, the Expression Manager is a very powerful tool that allows you to fully realize the power of the SSIS expression language. To download a free trial version of BI xPress, check out PragmaticWorks.com.

Uncategorized

Welcome the New Addition to the Ryan Family

2 Comments

If you’re wondering why you haven’t seen much activity on my blog lately, there’s a very good reason for that. On September 1st at 1:03 a.m., my wife gave birth to our first son and second child, Bradley James. Bradley weighed 6 lbs and 11 oz and is perfectly healthy. Being a father is an amazing experience, a lot of work, and an incredible blessing. Once we get the little guy on a good sleeping routine and we’re not up every couple hours to feed him, I hope to get back to blogging regularly. So stay tuned for some more great content. Now I’m gonna go take a nap.

20110924-120802.jpg

Pragmatic Works, SSIS

Quickly Apply SSIS Package Configurations to Multiple Packages with BI xPress

One of the most powerful features of BI xPress, in my opinion, is the Package Builder Wizard. The Package Builder Wizard allows an SSIS developer to create a template from a package or a from a few components of a package. The developer can then use those templates to create new packages or alter existing packages. In this short post, I’m going to walk through creating a template that contains a package configuration and then show you how to apply that template to all the packages in my SSIS project.

Recently I was consulting for a client that required me to add multiple package configurations to dozens of existing packages. Using the follow method, I completed that work in less than an hour. Needless to say, the client was very impressed and has since bought many copies of BI xPress.

If you’d like to follow along with me, visit http://www.PragmaticWorks.com and download the free trial of BI xPress.

I’ve got an SSIS projects that has a few packages in.

Capture1

My requirements are that I need to put package configurations on each of these packages so that way I can manage the package’s connections outside of BIDS.

The first thing I need to do is create package configurations for the properties I’d like to configure from outside the package. In my case, I’m creating two package configurations: One for my connection to my destination database and another for the connection to my auditing database.

Capture2

To create a package configuration, right-click in a blank area of the Control Flow and select Package Configurations.

Capture3

With the Package Configuration Organizer, make sure to check the check box to Enable Package Configurations. Then click the Add button. Next select the type of Configuration. In this example, I’m creating an XML configuration file (.dtsConfig). Specify the file path and name of the dtsConfig file. Then click Next.

Capture4

On the next screen, select the property that you want to configure. I’m going to select the ConnectionString property for the CashForClunkersDW Connection Manager. Click Next.

Capture5

On the final screen, give the Configuration a name and click Finish. I then created an additional configuration file for my other connection. Here you can see the two configurations I created.

Capture6

Now that I’ve created the package configurations I want to apply to all my packages. I need to create a template. To create a new template, right-click in an empty area of the Control Flow and select Package Builder Wizard (BI xPress).

Capture7

When the wizard opens, select the option on the top left to create a New Template (From Selection). Specify the Template Name and select the folder to store the template. Click Next.

Capture8

On the next screen, select the package objects to include in the template. Scroll all the way to the bottom of the Configurable Objects window and select the Package Configurations. Then click the Next button.

Capture9

Then click Start on the next screen to create the template. Now that the template is created, now we can apply our template to the rest of our packages. In the Solution Explorer, select the packages you want to apply the new template to by holding Ctrl and clicking. Then right-click one of the selected packages and select Package Builder Wizard (BI xPress).

Capture10

Make sure the option to Modify Packages (From Template) is selected. Click Next.

Capture11

Then I’m going to select the template I just created.

Capture12

On the next screen you should see the packages you selected in the Solution Explorer. You can select other packages or deselect. Click Next again. On the next screen, click Next again.

If a warning message appears letting you know that the package layouts will be modified, click Yes to continue. Then click Start to apply the template to the packages.

After applying the template to the other packages, make sure you open each package and check the configurations just to make sure everything went as planned before you start testing. And that should be it! You just added package configurations to all of your packages in a matter of minutes using BI xPress.

SSAS

Defining Member Properties for an Attribute in SSAS 2008

2 Comments

I was working with a client the other day designing a couple cubes. These cubes used two pretty large dimensions, each containing many attributes. In order to increase the performance of these large dimensions, I worked with the client to identify attributes that the end users would not necessarily need to slice and dice with, but would still be useful to view the attributes. Once those attributes were identified, I displayed them as what was known as members properties. So lets walk through how we can accomplish this.

Here’s the Customer dimension in the Adventure Works 2008 R2. As you can see, there’s a whole lot of attributes, many of which probably would provide little value by allowing the end users to slice with.

Capture1

Attributes with high cardinality are prime candidates to become a member property. A good example would be AddressLine1.

The first thing we need to do is set the AttributeHierarchyEnabled property to False for the AddressLine1 attribute.

Capture2

This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

To allow the users to view this attribute as a member property, we need to create an attribute relationship between AddressLine1 and the attribute we would like to view AddressLine1 from. By default, all attributes are related to the key column in the dimension. But I would like our end users to be able to view AddressLine1 when the hover over the Name field of our customer.

To accomplish this, go to the Attribute Relationships tab in the dimension designer. In my Customer dimension, I’ve got a calculated column called FullName but you could use FirstName for this example. Right-click the FirstName and click New Attribute Relationship. Then in the Create Attribute Relationship window, find Address Line1 in the Related Attribute drop down list. Click OK.

Capture4

After I processed the cube and connected to the AS database in Excel, I can now view the member properties for the Name attribute. Right-click the Name of a customer in your pivot table, click Show Properties in Tooltips, and click Show All Properties or select the individual attribute you’d like to view as a tooltip.

Capture6

Now when I hover over the name of a customer, I see the properties of that customer. In the Adventure Works cube, you can see how many other attributes have been created as member properties of the Name attribute.

Capture5

Alternatively, you can choose to display the member properties as a column in the pivot table by right-clicking the name of a customer, clicking show properties in report, and then select the field to display in the report.

Capture7

This is a great way to increase performance of large, unwieldy dimensions and still give the end user their wildest dreams Winking smile.

Pragmatic Works, SSIS

Audit and Watch SSIS Packages Execute on the Server

1 Comment

Among the many cool features of BI xPress is the extensive and robust Auditing Framework available right out of the box. With the BI xPress Auditing Framework, a user can track errors, warnings, row counts, variable, connections, source queries, and many other package properties. Whether you’re applying this incredible Auditing Framework to a single package, a dozen packages, or 100 packages, stepping through the wizard only takes a few moments. In this post, I’m going to walk you through applying the Auditing Framework to an SSIS package.

We’ve got a simple package that loads a dimension in a small data warehouse based on the Cash For Clunkers data.

Capture1

Now lets add the BI xPress Auditing Framework. To apply the Auditing Framework, I can simple right-click the package in the Solution Explorer and select Add/Remove Auditing Framework (BI xPress).

Capture2

This will launch the Auditing Framework Wizard. After clicking Next on the opening screen of the wizard, select the option to apply the Auditing Framework. Likewise, you can remove the Auditing Framework by selecting the other option. Click Next.

Capture3

Select which packages you’d like to apply the Auditing Framework to. You can select packages in a File System, on a SQL Server, or in an SSIS project to name a few locations.

Capture4

Click Next. Now you’ll have to create the Auditing database or select the Auditing database if it already exists. If you need to create the database, specify the server you want to create the database on and click the Create New Database button.  Make sure you click the Create new connection button to create the Connection Manager in the SSIS Package that points to the Auditing database. You can also check the check box to create an XML Package Configuration file.

Capture5

Then click the Logging Options tab. On this tab, you can specify DataFlow Logging, Variable, Connection, and other logging properties. Using these options, we can specify to log dataflow source and destinations row counts, real time data flow monitoring, connections, variables, and other properties. All of these options are selected by default so we’ll leave these selected.

Capture6

On the Advanced tab, you can specify if there are any other customer user variables that you would like to log. And on the Alert Filtering tab, you can specify which warnings you do not want to log, for example any truncation warnings using the Warning Editor.

Capture7

Click Next. Then click Start to apply the Auditing Framework to the package. When the process is complete, click Finish. Now that we’ve successfully added the Auditing Framework to the package, lets take a look at it.

The first thing you’ll probably notice is the new Connection Manager connecting to our Auditing database where all our auditing data is kept. Then if you open any data flow tasks, you’ll notice that they appear “jumbled up” after adding the Row Count Transforms. Simply reformat the data flow. Notice the Row Count counting rows out of the source and the second Row Count transform counting rows into the destination. The row count info is stored in variable.

Capture8

This information is then written to our database using tasks located in our SSIS package’s Event Handlers, such as OnError, OnWarning, OnPreExecute, OnPostExecute, and possibly in the OnVariableValueChanged Event Handler if you specified to track variable change history.

Now that we’ve applied the Auditing Framework, I’ve deployed the package to my server and executed it a few times in order to create a history of executions. One of the really cool things about BI xPress is that after applying the Auditing Frameworks we can watch our SSIS packages execute on the server as if we were watching them in BIDS! To open the BI xPress Monitoring Console, click the BI xPress menu in BIDS and select SSIS Monitoring Console.

Capture10

In the SSIS Monitoring Console, one of many reports you can use to troubleshoot your SSIS package executions is the Execution Control Flow Diagram. We can watch the Control Flow and Data Flow while the package executes on the server. Any Errors and Warnings that occur will be instantly visible and we can also see the values of logged package variables. To see a full list of all the reports available right out of the box, click this link.

The BI xPress Auditing Framework is unlike any other out of the box framework available. It’s complete and thorough and gives us, as SSIS developers, more than Microsoft ever dreamed in regards to SSIS troubleshooting. To download a free trial of BI xPress, visit PragmaticWorks.com.

MDX, SSAS

Daily Average Orders Calculation at Any Level of Date Hierarchy

1 Comment

Today I was helping a fellow BIDN.com user come up with a calculation to figure out the average numbers of orders on day at any level of a date hierarchy. So I figured I’d post the calculation because 1) maybe it will be helpful to you and 2) I’m going to forget this calculation so I’ll at least be able to use this post as a reference.

WITH 
    //Gets a Summation of the Orders in the Fiscal period
    MEMBER [Measures].[Sum] AS 
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])
    //Counts the days in the Fiscal period
    MEMBER [Measures].[Count] AS 
    
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
    //Divides the sum of orders by the count of days to calculate the average daily 
    //orders in that fiscal period
    MEMBER [Measures].[AvgDailyIntOrdCount] AS  
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])/
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
        
SELECT     {[Measures].[Sum],[Measures].[Count],[Measures].[AvgDailyIntOrdCount]} on 0,    
{[Date].[Fiscal].Members} ON ROWS 
FROM
      [Adventure Works]

In the calculation, I use the SUM function to get an aggregations of the Internet Order Count for any level of the Fiscal Date hierarchy. I also used the Count function with the Descendants function to get a count of days beneath a member of any level of the Fiscal Date hierarchy.

It seems to work pretty good, but if you have an alternative way to calculate this, please post it in the comments section!