Tag Archives: BI xPress

Pragmatic Works, SSIS

Adding/Copying SSIS XML Package Configurations

SSIS Package Configurations allow us to modify SSIS package properties from outside of the packages. This gives us a seamless way to dynamically point our packages to our development, QA, and production environments without needing to manually open and modify our connection managers within each SSIS package. Package Configurations can be created using a SQL Server table, environment variable, XML files, registry entry, or parent SSIS package, but this post will focus on creating an package configuration using an XML file.

To create the Package Configuration, open an SSIS package in SSDT or BIDS and right-click in the blank area of the control flow and select Package Configurations.

image

Check the checkbox to enable package configurations and then click Add to add a package configuration to the package.

image

In the drop down list next to Configuration type select XML configuration file. Browse to the location where you wish to store the XML configuration file and type in the name which you’d like to give your configuration file and hit enter.

image

And now time for a quick note on best practices and my thoughts on using XML configuration files. SSIS package configurations are most often used to configure Connection Managers from outside the SSIS packages. With this in mind, I prefer to create one package configuration per Connection Manager. I also prefer to name the configuration file the same name as the Connection Manager. In my experience, the 1:1 ratio of XML configuration files to Connection Managers eases maintenance and management of the XML configuration files. If you’re creating XML configuration files to configure other properties of the package such as package variables or task properties, you may find another solution works better for you. That’s just my .02 cents on the matter.

Now we need to specify the property which we wish to configure. In this example, I’m selecting the ConnectionString property of a Connection Manager. Check the checkbox next to the ConnectionString property under the Connection Manager. Click Next. On the final screen you’ll need to give the Package Configuration a name. I give the same name as the XML Configuration File to help things stay nice and neat.

image

We can see our package configuration has been created. Click close.

image

Yay! Now your package has an XML configuration file set up to dynamically configure your connection string of the connection manager you selected at run time. All that is left to do is to copy the XML configuration file to each environment the package will be run on: developer machines, development, QA, and production environments. Then you must also update the XML configuration file with the environment’s connection string. Just open the XML file in note pad and update the Connection String to point to the appropriate server and database.

image

So we’ve set up the package configuration on a package. But what if we need to copy the package configuration to many other packages? Sure, we could complete the previous steps manually on each package, but that would take a lot of time. We can, however, easily accomplish this in a few clicks with BI xPress. In order to complete the following steps you will need to have BI xPress installed. You can download and install a free trial version of BI xPress here.

image

Shift select all of the packages within your SSIS project including the package that has the package configuration applied as well as the packages you wish to modify. Then right-click the packages in the Solution Explorer and select Package Configurations (BI xPress) to open the BI xPress Configuration Wizard.

image

Click Next pass the first screen of the Configuration Wizard.

image

You should see all of the packages highlighted and in the Select Items window. Click Next.

image

In the following screen you should see your packages listed and the lone package configuration displayed beneath the SSIS package we added the configuration to.

image

Right-click the package configuration and select copy or simply highlight the package configuration and hit Ctrl+c to copy the package configuration.

image

Then select each package in the list individually and hit Ctrl+p to add the package configuration to each package.

image

Click the disk icon to save your changes and we’re done! Our package configuration has been added to each package with much less work than we initially went though.

image

Just be aware that if package configurations have not been enabled for the packages you added the package configuration to you will still need to complete that step for each package.

For more information on BI xPress just head over to PragmaticWorks.com or head here to download the free trial version of BI xPress. Enjoy!

Pragmatic Works, SSIS

Unit Testing Your SSIS Packages

One of the challenges SSIS developers often face is being able to meaningfully test our SSIS packages or components within the packages. Pragmatic Works has released a new component to BI xPress that allows you to easily create a suite of tests to perform on an SSIS package.

Unit Tests can be performed on entire packages or individual components in a package. And those Unit Tests are grouped into Test Suites, so think of a Test Suite as a series of unit tests designed to test the functionality  of a set of components in a package or a series of packages.

Set up steps can be configured to make retesting very easy. If a file needs to be created or a T-SQL command executed before a test can be performed, those steps can be configured within the BI xPress unit testing interface. And these set up steps can be performed once for the Test Suite or once for each Unit Test within the Test Suite.

Once the packages have been added to the Test Suite, creating the Unit Test is as simple as selecting the package or task within a package to execute. Then specify the expected results.

Then its time to create the Unit Test Assertions, which are the steps to check to execution of the packages or package components. An Assertion could be anything from checking a table for a certain number of rows, checking for the existence of a file, or checking a property within the package, among many others.

The entire Test Suite can be executed or an individual Unit Test can be fire. The test results are displayed in an easy to read format.

Take a look at SSIS Unit Tests with BI xPress if you’re looking for a way to streamline testing of your SSIS packages. I’ve personally used this tool during client engagements and its a great way to standardize testing of your SSIS packages.

Pragmatic Works, SSIS

Import Native SSIS Performance Data With BI xPress

imageOne of the most powerful features of BI xPress is the Auditing Framework, which allows you to apply a standardized and robust auditing framework to multiple packages in just a few clicks. I’ve blogged about the BI xPress Auditing Framework before because its an extremely impressive tool that has saved me and the teams I’ve worked with countless hours. But now that SQL Server 2012 has been released, we have a whole slew of execution and performance data available to us natively within the 2012 SSIS Catalog. Wouldn’t it be great if we could view the native execution and performance data within the BI xPress Monitoring Console even if the BI xPress Auditing Framework has not been applied?

Your prayers have been answered. If you’re running SSIS 2012, you can now easily import the native execution and performance data into the BI xPress database for even more in depth reporting on the execution of your SSIS packages. Of course, this feature only works if you’re running 2012 :).

To import the SSIS Catalog data into the BI xPress database, click the SSIS Catalog Import button located on the Pragmatic Workbench home screen.

image

Next specify the location of the BI xPress database that is your target for the import. Then add the SSIS Catalog as the source for the import.

image

imageAnd the really nice part is that you can turn on Auto Importing so that the native performance and execution data will be automatically imported into the BI xPress database!

Now that the data has been imported into the BI xPress database, any native SSIS 2012 package stored in the SSIS Catalog can now monitored and measured in the BI xPress Monitoring Console. Head over to PragmaticWorks.com for more information and a trial download.

Pragmatic Works, SSRS

SSRS Performance Monitoring With BI xPress 3.5

1 Comment

Earlier this month the latest version of BI xPress was released. There are several new features in this latest release of BI xPress, but one of the coolest is the SSRS Performance Monitoring.

The BI xPress SSRS Performance Monitor has four performance monitoring panels.

  • Average Run Time

    The Average Run Time panel displays the average run times of your report. This is a great place to figure out which reports are taking the longest to execute. This is the place to start if you’re trying to figure out why a report is taking a long time to run. There are three different very helpful metrics displayed in the panel:

    • Avg. Data Retrieval Time: This number shows how long it took to retrieve the data from the source system. If the Avg. Data Retrieval time is the majority of the report run time, take a look at the source query and find ways to optimize it.
    • Avg. Processing Time: This is the time the SSRS server takes to calculate and process the retrieved source data. Any formulas or expressions in the report are calculated during this time.
    • Avg. Rendering Time: This is the amount of time it takes to paint the graphics, charts, text, etc.

  • Longest Running Reports on Average


    This panel provides you a chart that can be read quickly and easily to allow you to see if a report’s execution is taking too long, which could negatively effect the performance of the entire SSRS server.

  • Frequent Reports


    The Frequent Reports panel displays the reports that are most frequently run. If your reports are being executed more than expected, this could lead to scalability issues. Tracking the number of executions is a good way for you to begin to determine if its time to scale up or out.

  • Active Users

    The Active Users panel displays report executions by users.

This newest feature is one I’ve been waiting for a long time since I first saw it during the development phase. To read more about BI xPress and the numerous other features included, head over to PragmaticWorks.com and check it out.

Pragmatic Works, SSIS

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.

Pragmatic Works, SSIS

Find SSIS Variable Dependencies with BI xPress

One of the difficult aspects of SSIS package development is keeping track of where a variable has been used in an expression or whether a variable is being used at all. If you’re developing your packages with BI xPress, you can easily discover where your variables are being used if they’re being used at all.

Open your package in BIDS, and go to the Variables windows. Highlight the variables you want to check for dependencies and then click the Scan Variable Dependency icon.

1 Scan for variable dependencies icon

This will open the Variable Scanning Options. You can specify to scan all variables, which could take a considerable amount of time. In my case, I’m going to choose to scan for only the variables I have selected.

2 scan for selected variables

When the Variable Usage windows opens up, click the Scan all objects for variable usage button at the bottom. This is going to actually search our package to discover if and where the variables could be used.

3 View variable dependencies

Once the scan is complete, we can see on the right side of the window the tasks where our variables are used. In my case, my selected variable is used in an Execute SQL Task and a Script Task. If I highlight the Script Task, I can even view the script and see where the variable is used.

Any variables that are not being used in the package will show up under the Unused Variables node. At the bottom of the window you’ll notice that we have the option to either rename or delete these unused variables.

Head to PragmaticWorks.com to checkout BI xPress and download the free trial version.

MDX, Pragmatic Works, SSAS

Creating Named Sets In Your Cube

Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work.

To create a named set, open your SSAS project and head to the Calculations tabs. Find the icon with the curly brackets and click it.

1 Click icon

Give your named set a name. For this example, I’m using the Adventure Works 2008 R2 cube and I’m creating a named set to return the top ten products with the highest Internet Sales Amount.

The MDX to return the top ten products with the highest Internet Sales Amount is very simple. We will use the TopCount function, which accepts three arguments.

The first argument is the dimension attribute we wish to return. The second argument is how many members will be returned in the set, and the last argument is the measure we would like to use to rank the members returned.

If I wanted to create a named set to return the Bottom 10 Products with the lowest Internet Sales Amount, I could write the same expression seen below except in place of the TopCount function I would use the BottomCount function.

1a Create Top 10 Count

When we create our named set, we can specify it as a Dynamic Named Set or a Static Named Set. A Dynamic Named Set respects the context of the subcube and the where clause and is evaluated at the time the query is executed. A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in your where clause.

Here is the calculation script for the named set:

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Products]
AS
    TopCount
    (
    [Product].[Product].Members,
    10,
    [Measures].[Internet Sales Amount]
    )
     ;

The next time we process our cube, our named set will be available for use in our calculations and reports.

To use our named set in an MDX query, all I have to do is use the Named Set’s alias, [Top 10 Products].

Named Sets are powerful and very useful, but they can sometimes take a long time to write, especially if the expression is complex or if you are new to MDX. That’s where BI xpress steps in. BI xPress is a powerful tool used to speed up and augment SSAS and SSIS development. BI xPress also recently won the Gold Editors Choice Award from SQL Server Magazine.

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

This will open the MDX Calculation Builder Wizard. Select the calculation or named set you would like to build. Scroll to the bottom to find the Sets templates. I’m selecting the Top 10 Count template.

3 Select Top 10 Count BI xPress

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.

4 Select attribute

Then select the measure that you want to use to rank the selected attribute. I’m selecting the Internet Sales Amount measure.

4 Select measure

After I click next and give my calculation a name, the named set has been added to my calculation script in my cube.

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.

Pragmatic Works, SSIS

BI xPress wins “Best Business Intelligence and Reporting Tool” from SQL Server Magazine

1 Comment

Last week it was announced that Pragmatic Works’ tool, BI xPress, won the SQL Server Magazine Gold Editors Choice Award for Best Business Intelligence and Reporting Tool and the Silver Community Choice Award. 

BI xPress allows for real time monitoring of all your SSIS packages executing on your servers. The way that BI xPress can allow you to monitor your SSIS package in real time is by applying a standardize auditing framework across all of your SSIS package using the BI xPress Auditing Framework Wizard. The Auditing Framework captures errors, warnings, and runtime details to create an execution log that can viewed in real time. This information makes it very easy to debug and track down problems with your SSIS packages.

So lets walk through applying the Auditing Framework to several packages a coworker developed to load a data warehouse.

To apply the Auditing Framework, I will select my packages in the Solution Explorer, right-click one of the packages and select Add/Remove Auditing Framework (BI xPress).

1 Select Packages

After selecting the packages, we need to set up the auditing database where all our package’s execution information will be stored. I can also choose to generate an configuration file for the connection to the auditing database.

3 Connection Information

On the next tab we select our logging options. We can enable real time monitoring, variable change tracking, row counts, connection information, and even SQL statements.

4 Logging Options

If we have any user variables we wish to track, we can specify those on the Advanced User Defined Logging tab.

5 Advanced User Defined Logging

After clicking Next we then applying the Auditing Framework.

7 Processing Summary

With the Auditing Framework applied, we can see the Row Count Transforms that are added to our Data Flow Tasks to track the rows from sources and into destinations.

8 Row Counts

Scripts tasks have also been added to Event Handlers to write to our Auditing Database when the packages execute or if an error or warning occurs.

9 Event Handler Script Tasks

With the Auditing Framework applied, we can now watch our packages execute in real time on the server.

10 Real Time Monitoring

As powerful as the Auditing Frame is, it’s only one of the features of BI xPress. Head over to PragmaticWorks.com to read more about the other features of BI xPress and download the trial version.

MDX, Pragmatic Works, SSAS

Ten MDX Calculations For Your Cube

8 Comments

I get lots of questions about how to implement various types of calculations in a cube so I figured I’d cover some of the more commonly used calculations I run into on a regular basis. All of these example calculations I’ll be providing were written against the Adventure Works 2008 R2 example cube but these should still work against later versions of the AW cube.

10. YTD, QTD, and MTD Calculations

These kinds of calculations are pretty common and I see these in a lot of cubes. They’re pretty easy to wire up since there are only a couple simple MDX functions necessary to make this work.

CREATE
MEMBER CurrentCube.[Measures].[YTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING = “Currency”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

Let’s start with the PeriodsToDate function. The PeriodsToDate function is going to return all the sibling members at the specified level up to the current member. The outer function, the Aggregate function, calculates the value based on the aggregation type specified in the cube for the measure. In our case, the Internet Sales Amount aggregation type is Sum, so the Aggregate function could be exchanged for the Sum function.

If I wanted to calculate the Month to Date for Internet Sales, I would simply exchange the reference for the Calendar Year level of the Calendar hierarchy with the Month level, as seen here:

CREATE
MEMBER CurrentCube.[Measures].[MTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Month]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING =
Currency
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

9. Period over Period Growth

This is also a pretty common calculation I find and implement for my clients. It’s also pretty straight forward. In this example, we’re comparing this year’s Internet Sales Amount to the previous year’s Internet Sales Amount.

Create Member CurrentCube.[Measures].[Yearly Growth Internet Sales Amount]

As
([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Calendar Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 ,  DISPLAY_FOLDER = ’10 MDX Calculations’ ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

The function here to pay attention to is the PrevMember function. The PrevMember function returns the previous member at a given level based on the supplied member. For example, if the supplied member was 2011, the previous member would be 2010.

Create Member CurrentCube.[Measures].[Monthly Growth Internet Sales Amount]

As
([Date].[Month of Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Month of Year].PrevMember,[Measures].[Internet Sales Amount]),

FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

Similar to our last calculation, if we wish to calculate the growth from month to month, we should reference the Month attribute of our Date dimension.

8. Profit Margin Calculation

Another popular calculation for obvious reasons is the calculation for profit margin. Basically all you need to understand for this calculation is the logic of a Case statement. We use the Case statement to check for a zero value in the denominator.

CREATE
MEMBER CurrentCube.[Measures].[Internet Profit Margin] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
([Measures].[Internet Sales Amount]
–
[Measures].[Internet Total Product Cost])
/
[Measures].[Internet Sales Amount]
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

7. Percent of Total

More often then not, my clients want to be able to calculate the percent of a total amount for a hierarchy. In this example, I’m once again leveraging the Case statement.

CREATE
MEMBER CurrentCube.[Measures].[Percent of Internet Product Sales] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
(
[Product].[Product Categories]
,[Measures].[Internet Sales Amount]
)
/
(
[Product].[Product Categories].[All]
,[Measures].[Internet Sales Amount]
)
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

To adapt this calculation to your cube, just replace [Product].[Product Categories] with your dimension and hierarchy.

6. Count Leaf Members of a Hierarchy

I’ve seen this kind of calculation used a lot in combinations with other measures and/or calculation. This calculation counts the Leaves, which are products, of the Product Categories hierarchy.

CREATE
MEMBER CurrentCube.[Measures].[Product Count] AS
Count
(
Descendants
(
[Product].[Product Categories].CurrentMember,
,LEAVES
)
)
,FORMAT_STRING = “#,##0.00;-#,##0.00”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

There’s not much to this calculation. First we use the Descendants function with the Leaves function as the third argument to get all the Leaf members of the Product Categories hierarchy. Then we use the Count function to count those members.

While these calculations are not extremely complex, MDX can be a bit overwhelming. A very helpful tool that I’ve found useful, even as someone experienced with MDX, is the MDX Calculation Builder included with BI xPress. Adding calculations, like those seen above, is very easy and only take a few click with the wizard.

To open the MDX Calculation Builder, click the icon seen on the Calculations tab of your Cube Designer in BIDS.

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. At these step, we can also choose to create a named set, which the wizard will help us do.

10-30-2011 10-29-37 PM

After selecting the calculation, you’ll be taken through a couple more steps, such as selecting the Measure you wish to perform the calculation with or specifying which attribute to use as the Year attribute for the calculation.

You’ll also have the opportunity to apply conditional formatting to your measure values, which is a nice touch the end users like to see.

10-30-2011 10-46-34 PM

Finally, 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.

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

10-30-2011 10-50-58 PM

BI xPress has made adding calculations to your cube simply and fast and I take advantage of the tool anytime I’m implementing calculations into my client’s cube. Head to PragmaticWorks.com to check out BI xPress and to check out some of the great training videos hosted there covering the in’s and out’s of MDX.

Stay tuned for my next blog post where we will cover the remaining 5 MDX calculations to add to your cube!

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.