Category Archives: Pragmatic Works

What #SQLFamily Means to Me

December 12, 2011 Dustin Ryan 3 Comments

If you haven’t heard about some of the great things my company, Pragmatic Works, is doing to give back to our veterans integrating back into civilian life, check out what Microsoft is doing to get involved with this. In light of this, Microsoft wants to hear more about #SQLFamily and other amazing instances where someone in the SQL community has lent a helping hand. Microsoft has pledged to donate $50 for the first 400 stories submitted illustrating #SQLFamily. I thought it’d be appropriate, therefore, for me to share what #SQLFamily means to me. But first let me share how I ended up as a business intelligence consultant.

In 2008 I was loading trucks at UPS and had been for the previous six years as I progressed through college. I wasn’t happy there but it had great benefits so I was content to stick out and try and make something happen. I’ve always believed that a person can make anything happen if that person has the desire, the drive, and the determination, so I kept my head down and worked hard while hoping that some kind of management position would open up and give me the break I’d been looking for.

Then in August that year I learned through a buddy from high school, Devin Knight (blog | twitter), that his brother, Brian Knight (blog | twitter), was offering free training in something called “SQL”. Devin gave me a preview of the language and it seemed straight forward enough. I told Devin I was interested.

Devin put me in touch with Brian, whom I’d met a couple times before and we scheduled some time for me to start my training learning SQL and SQL Server Reporting Services. I went through three days of training with Brian and Mike Davis (blog | twitter) and learned about Select statements, data sources, reports, and tablixes. It was a lot to take in in just three days time, but I did my best. Brian and Mike were patient with me and answered the many questions I had.

Then one day I was driving home from work and Brian called me up. He was offering me a job as a Junior Business Intelligence Consultant. He said he saw a lot of upside in me. Brian wanted to take me in, mentor me, and train me. And as they say, the rest is history.

Since that day my life has never been the same. I won’t ever be able to thank Brian enough for giving me my shot. Because of Brian’s generosity, optimism, and faith in me, my life has changed for the better. And it’s because of Brian’s tremendous example that you can find me speaking at SQL Saturday and Code Camp events or answering questions on my blogs or the forums of BIDN.com. If I can have a fraction of the impact on someone else that Brian had in my life, then maybe in that way I can say thanks.

So that’s what #SQLFamily means to me. It means helping others because someone somewhere at some point gave you a helping hand. Whether it was a coworker who answered a question for you or someone who proofread your resume, take the time to say thanks and pay it forward to someone else who just needs a little faith, optimism, and a helping hand.

Pragmatic Works, SSIS

Find SSIS Variable Dependencies with BI xPress

November 30, 2011 Dustin Ryan

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.

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.

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.

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

November 26, 2011 Dustin Ryan

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.

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.

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.

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.

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.

Then select the measure that you want to use to rank the selected attribute. I’m selecting the Internet Sales Amount 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

November 20, 2011 Dustin Ryan 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).

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.

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.

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

After clicking Next we then applying the Auditing Framework.

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.

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.

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

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

November 16, 2011 Dustin Ryan 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.

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.

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.

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.

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!

Pragmatic Works, SSRS

Documenting Your SSRS Reports and Data Sources

November 9, 2011 Dustin Ryan 2 Comments

If the organizations you’ve worked with are anything like the ones I’ve had the pleasure of working with, then they probably had or have thousands of SSRS reports spread out all over the place. And back around the time a majority of companies were gearing up to migrate to SQL 2008 from 2005, getting a grasp on the number and complexity of these reports was quite a challenge.

This is just the scenario where BI Documenter really shines. Besides being able to document your SQL databases, SSIS packages, and SSAS cube, BI Documenter can also document your SSRS reports and shared data sources, all the way down to the XML behind the scenes in case your reports are lost.

Let’s walk through creating the documentation we need to understand how many and how complex the reports are that are being considered for migration. For the sake of this example, just pretend my SSRS 2008 reports are actually 2005 ;).

Open BI Documenter, click Add a New Solution. Give the solution a name and description and then click Create Solution.

After clicking Next, click Add Reporting Services Server on the next screen.

The reports you wish to document can exist either on the SSRS server or on a SharePoint Server. In my case, my reports are on my local SSRS instance so I’ll select Native for my Server Mode. Key in the server and any credentials. Click the hyperlink at the bottom of the screen to verify BI Documenter can reach the SSRS server. Click Next.

Give a name and description of the Report Server and click Finish.

Now that we’ve add the Report Server to our documentation solution, we need to create a snapshot. On the Filter tab, check the boxes next to Reports and RS Data Sources.

Click Next a couple times. All we need to do now is finalize the documentation. You can output the documentation in HTML format or in a .CHM file. Select the output destination.

On the last tab, Reporting Objects, make sure the box is checked to document the Report Definition. This is a great tool if you reports are lost. With a little work, the documentation of the Report Definition can be used to recreate the reports. Click Next. Once the process to create the documentation is finished, click Finish.

Now we have a precise and detailed document outlining all the reports living on our SSRS server. I can view the .rdl’s for my reports, the parameters, data sources, and data sets. I can even view the data set queries.

The great thing about this feature of BI Documenter is that I can easily document all my SSRS reports as long as I know the Report Server. Head here to download the free trial.

Pragmatic Works, SSIS

Data Cleansing with Regular Expressions in SSIS

November 6, 2011 Dustin Ryan 2 Comments

Regular Expressions are a powerful way for you to search for patterns in strings of text. In SSIS, we can leverage Regular Expressions to assist us in cleansing data. I’m going to show you a couple different ways we can accomplish this task.

The first method involved using a Script Component within a Data Flow Task to remove unwanted characters from our data. In this example, I’ve created some junk data that contains a mixture of letter and numbers.

I want to eliminate the letters from each value. To accomplish this, I will use a Script Component as a Transformation.

First, drag the Script Component into your Data Flow. Make sure you select the Transformation option and click OK.

Next, open the Script Component Editor and go to the Input Columns tab. Select the field you wish you cleanse and set the Usage Type to ReadWrite.

Then go back to the Script tab and click the Edit Script button near the bottom.

Add the following code to your Script Component:

using System;
using System.Data;
using System.Text.RegularExpressions; //<<— Added to allow the use of regular expressions
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
//TestJunk is the field I am cleansing. Make sure you use the correct field name.
Row.TestJunk = RegExCleanse(Row.TestJunk);
}

public String RegExCleanse(String field)
{
// This is the RegEx pattern we are looking for
String RegExPattern = “[a-z]”;

Regex objRegExp = new Regex(RegExPattern);
field = objRegExp.Replace(field, String.Empty);
return field;
}
}

Once you’ve pasted the script into the Script Component and changed the name of the field and are using the desired RegEx, click OK a couple times to close the script editor and the Script Component Editor. We are now ready to test our script.

Here are the results of our RegEx data cleanse:

All the letters have been removed from our text strings.

But there’s actually an easier way to handle data cleansing with Regular Expressions. It’s called the RegEx Replace Transform and its included in Task Factory developed by Pragmatic Works. So lets take a look at how easy it is to use RegEx to cleanse our data without a bit of scripting.

I’ve already added the RegEx Replace Transform to my Data Flow Task and connected it up to my OLE DB Source. Double-click the RegEx Replace Transform to open the editor.

Next, click the drop down arrow next to the field you wish you modify with RegEx. Now we can configure the transform to perform the desired cleansing. For the Action, select “Replace matching regular expression pattern with user defined value.” You can also choose to extract a string of text from your field based on the RegEx patten you specify.

For the Output Action, select “Replace the column data with fixed data.”

Lastly, specify the RegEx patten next to “Search RegX Pattern.” Then click OK. Your configuration should closely resemble mine, seen here.

Now when I run my Data Flow Task, all letters are removed from my data. The RegEx Replace Transform from Task Factory is an explosive tool that easily allows you to utilize Regular Expressions to scrub your data without the need to write complicated scripts.

Also, just an FYI. If you’re interested in learning more about RegEx or need some helping writing Regular Expressions, check out http://gskinner.com/RegExr/. This site is an amazing learning tool when it comes to RegEx.

Pragmatic Works, SSIS

SSIS Batch Update without Staging the Data

October 31, 2011 Dustin Ryan

The native SSIS functionality currently prevent you from performing a batch update from a Data Flow Task without staging the data. If you wish to update records from within the Data Flow Task, you’re going to have to use the OLE DB Command transform, which is very slow. If you’re update more than just a few records, I’d highly recommend first staging the data and then using an Execute SQL Task to perform the batch update.

Or you could just use the Task Factory Update Batch Transform from Pragmatic Works and avoid staging the data at all or having to use the performance-devouring OLE DB Command transform.

The Update Batch transform included with Task Factory utilizes an ADO.Net connection to perform a batch update of a SQL Server table quickly and efficiently. Configuring the Update Batch transform is very easy.

After dragging the Update Batch transform into your Data Flow design area and connecting a Data Flow Path to it, you can then double-click the transform to open the Update Batch Transform editor.

First you’ll need to either create a new ADO.Net connection or select an existing connection. In my case, I’m going to select a previously existing ADO.Net connection, ADO NET AW.

Next I’ll select the Table I would like to update. After selecting the table, I need to specify which column(s) should be used as key values in determining if a record in the table is a match and meets the criteria for updating the record. I can either select a single column or multiple columns to create a composite key.

Lastly, any fields in the table that I do not wish to update should be marked as ignore. Fields that need to be updated should mapped to the corresponding field in the Destination Column.

Now we’re ready to update our records in batch without the need for staging. One of the neat advantages to using the Update Batch Transform from Task Factory is the ability to use this transform as a destination or a transformation. If I choose to do so, I can allow the records moving through the data flow to move on to another transformation or destination, as you can see here.

I’ve found the Update Batch Transform to be very useful when performing mass updates. I’ve seen tremendous performance gains in my SSIS packages since I no longer need to perform additional reads/writes to first stage the data and then perform the update.

Head to PragmaticWorks.com to download the free Task Factory trial and check out some of the other useful task and transformations included with Task Factory.

Pragmatic Works, SSIS

Quickly Edit Package Configuration Values from BIDS

October 31, 2011 Dustin Ryan

If you’ve followed my blog at least somewhat, you’ve probably read a blog post or two where I cover one or several of the really cool features of BI xPress. That’s because, simply put, BI xPress is a very versatile and useful tool that no SSIS developer should be without. It pains me to think about all the time I have to waste whenever I go work for a client that does not have BI xPress.

With that said, one of the smaller but more useful features of BI xPress that I found myself making use of is the Quick Config Edit feature. With Quick Config Edit, you can easily view the configurations being used in your package and edit the values of those configurations very easily, all without leaving BI xPress.

To open Quick Config Edit, go to the BI xPress menu at the top of BIDS and select Quick Config Edit.

Once Configuration Editor is open, you’ll need to specify where your configurations are stored, whether it be on the File System or in a SQL Server table. In my case, I’m using a SQL Server table to configure this particular package. Specify the server, the credentials used, the database, and the table.

You’ll then be able to view the Configurations in your package, the package properties being configured, the values used to configure the package, and also make changes to those configurable values. It even becomes possible to edit multiple package configurations at one time.

Here you can see a configuration I have for a connection string. From this window I can easily adjust the connection string stored in the SQL Server table. Once I make a change to the Value I can just click Save to make the change final.

This is an example of a configuration for a Package Variable. From this view I can easily see the scope of the variable (Foreach Loop Container, in this case), the variable being configured, and the value of the package configuration.

Quick Config Edit also has a very handy feature that makes is easy for you to do a quick Find and Replace of your package configurations, allowing you to update multiple package configurations in the blink of an eye.

Quick Config Edit is a very simple but extremely useful feature. It may seem small, but these are the kinds of features that increase the speed of package development by allowing you to stay in the BIDS environment. This is hands down one of my favorite features of BI xPress because it is so convenient.

Check out this page if you’d like to read more about the features of BI xPress.

MDX, Pragmatic Works

MDX Calculation Builder Automatically Builds MDX Calculations for You

October 30, 2011 Dustin Ryan

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.

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.

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.

Then we’ll select our Year level attribute.

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.

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.

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

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

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.

Data and Analytics with Dustin Ryan

Category Archives: Pragmatic Works

What #SQLFamily Means to Me

Like this:

Find SSIS Variable Dependencies with BI xPress

Like this:

Creating Named Sets In Your Cube

Like this:

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

Like this:

Ten MDX Calculations For Your Cube

10. YTD, QTD, and MTD Calculations

9. Period over Period Growth

8. Profit Margin Calculation

7. Percent of Total

6. Count Leaf Members of a Hierarchy

Like this:

Documenting Your SSRS Reports and Data Sources

Like this:

Data Cleansing with Regular Expressions in SSIS

Like this:

SSIS Batch Update without Staging the Data

Like this:

Quickly Edit Package Configuration Values from BIDS

Like this:

MDX Calculation Builder Automatically Builds MDX Calculations for You

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

10. YTD, QTD, and MTD Calculations

9. Period over Period Growth

8. Profit Margin Calculation

7. Percent of Total

6. Count Leaf Members of a Hierarchy

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.