SSAS

SSAS 2008 Storage Modes

In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). There are advantages and disadvantages to each, so I figured I’d take a few minutes to give a quick overview describing the storage modes and laying out some of the pros and cons of each.

Relational Online Analytical Processing (ROLAP)

The ROLAP storage mode allows the detail data and aggregations to be stored in the relational database. If you plan on using ROLAP, you need to make sure that your database is carefully designed or you’ll run into some bad performance issues.

Pros:

  • Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
  • Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
  • Low latency.

Cons:

  • With all the data being stored in the relational database, query performance is going to be much slower than MOLAP.
  • You must maintain a permanent connection with the relational database to use ROLAP.

Multidimensional Online Analytical Processing (MOLAP)

MOLAP is the default and thus most frequently used storage mode. With MOLAP storage, the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database.

Pros:

  • Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
  • The data is compressed so it takes up less space.
  • And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
  • Cube browsing is fastest using MOLAP.

Cons:

  • Because you don’t have a real time connection to the relational database, you need to frequently process the cube to update your data.
  • If there’s a large amount of data, processing is going to take longer.
  • There’s also an additional amount of storage since a copy of the relational database is kept on the OLAP server.
  • High latency.

Hybrid Online Analytical Processing (HOLAP)

HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Pros:

  • HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
  • Cubes are smaller than MOLAP since the detail data is kept in the relational database.
  • Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
  • Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Cons:

  • Query performance can head downhill fast when more detail data is queried from the relational database.

This is by no means an exhaustive list of the details in regards to each storage mode, but I hope this has given you a good general understanding of the differences between ROLAP, MOLAP, and HOLAP. Feel free to post any comments or questions. Thanks, guys!

SSIS

Check IsNumeric() with Derived Column Transform in SSIS Package

At some point or another, you’ve probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While you can’t use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn’t mean you can’t check to see if a field is numeric using the SSIS expression language (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy, another Pragmatic Works consultant, covers that in his blog here).

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric. After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name and use this expression:

(DT_I4)CheckForNumeric == (DT_I4)CheckForNumeric ? 1 : 0

check for numeric with derived column

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to Ignore failure on Error, as seen here:

Ignore failure

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

Now when we run the Data Flow Task, we should see results like this:

IsNumeric results with Derived Column

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field are numeric and which are not numeric by checking for NULLs in our field called IsNumeric.

SSRS

Creating Stepped Reports with SSRS

10 Comments

One of the nice things about a stepped report is that the child groups are displayed beneath the parent groups but within the same column as the parent group. In the next several steps, I’m going to walk through creating a basic stepped report using the Tablix data region. I used the Matrix data region template for this example.

First I created a report and dragged in a Matrix data region into the design area. I used the Adventure Works Cube and a simple data set with the Product hierarchy, Reseller Sales Amount and Calendar Year. Here is the query I used for the main dataset in case you would like to follow along.

SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS * [Date].[Calendar].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I put Product in the row group, Calendar Year on the column group, and Reseller Sales Amount in the details.

Capture1

Next we need to create a parent group for to display our Subcategory. To create a parent group, in the Row Groups pane, right-click the Product group, select Add Group, then select Parent Group.

Capture2

Select Subcategory as the field you want to group by. Make sure to check the Add group header check box.

Capture3

After creating the new group for Subcategory, right-click the Subcategory group in the Row Groups pane, and create a new parent group for the Category field. Again, check the box next to Add group header.

At this point, you matrix should look like this.

SSRS Stepped report layout

But since we are creating a stepped style report, we want our parent groups in the same column as our detail group. So we can delete the columns that contain Category and Subcategory.

Customize your SSRS stepped report layout

Then add Category and Subcategory to the rows above Product so that your Matrix looks like what you see below. Also, don’t forget to add Reseller Sales Amount in the cells next to Category and Subcategory.

Consolidate the groups in your SSRS stepped report

Now we’re almost done. To create the stepped formatting, right-click the cell containing Subcategory and select Text Box properties. In the Alignment pane under Padding options, set the Left padding to 15pt. Then click OK.

Create dynamic padding for your SSRS stepped report

Then right-click the cell containing the Product field and click Text Box properties. This time in the Alignment pane, set the Left Padding to 30pt.

Modify the SSRS stepped report group properties

The last thing we have to do is create that nice, fancy, collapsible drill-down action. To do that, right click the Product row group in the Row Groups pane and navigate to the Visibility pane. When the report is initially run, we want the Product row group set to Hide. Check the check box next to Display can be toggled by this report item. Since we want the Product row group to be toggled by its parent group, select the Subcategory text box in the drop down list.

Modify the SSRS stepped report group properties

We also should set the visibility settings of the Subcategory row group so that it is also hid when the report is initially run. It should be toggled by the Category row group.

And after adding some nifty back ground colors to make our report a little easier to read and some bold text on the header row, you are done! We now have a simple, yet easy to read, stepped report that will allow the end user to drill down into the child group rows to view the data at a more granular level.

The final Reporting Services SSRS stepped report example

Free Training, Pragmatic Works, SSAS, SSRS

Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Training, Pragmatic Works, SSAS, SSRS

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

Pragmatic Works, SSIS

Encrypting Sensitive Information in SSIS Package Configurations using BI xPress

One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress, SSIS developers can easily and automatically encrypt any configured property of an SSIS package. So lets walk through using the BI xPress Package Configuration Organizer and look at a couple of the different options we have when encrypting an SSIS package configurations.

There’s a couple ways you can access the BI xPress Package Configuration Organizer. Just right-click on some empty space in your Control Flow design area and select Package Configurations (BI xPress).

Capture1

You can also click the BI xPress menu at the top of BIDS and then select Package Configurations (BI xPress).

Capture1a

This should open the BI xPress SSIS Configuration Wizard. This is the wizard that will allow you to select which package properties you would like to configure and encrypt. Make sure you check the box to Enable package configurations.

Capture4

On the next screen of the wizard, you’ll need to set a few options. The first is to decide what type of Configuration you want to use. You can choose from several options seen below, but I’ll be using an XML configuration file.

Capture4 

After you specify the Configuration Name, click Next.

On the next screen specify which property you want to configure/encrypt. At the top of the window, I can select an encryption mode, I can choose to encrypt only the sensitive data or the full value of the property. In this example, I’m encrypting the connection string for the connection manager OLEDB_SSISOPS_1, so I’ve checked the check box next to the ConnectionString property under OLEDB_SSISOPS_1. We can also select an algorithm type. I’m using the Advanced algorithm to encrypt the connection string. Using these settings, the entire connection string for this connection will be stored in the configuration file in an encrypted format.

 

Capture6

Click Next and on the next screen click Start to generate the configuration file. Once the wizard is finished a summary screen will appear. Click Finish to close the wizard.

And you’re done! Here is a screen shot of the configuration file. You can clearly see that connection string is encrypted and unreadable.

Capture7

Finally, it’s a piece of cake to encrypt sensitive information stored in package configurations!

Pragmatic Works

The Best SQL Documentation Tool on the Market

If you read my blog you know from time to time I will blog about an exceptional third party SQL tool. Previously I’ve blogged about Task Factory and BI xPress, two amazing SSIS development tools. Well this week I figured I would talk about a killer SQL documentation tool called BI Documenter.

image

BI Documenter allows you to quickly and easily document your entire BI environment. So unlike the other popular tools out there, BI Documenter will document SQL Server databases (2000, 2005, 2008 & R2), SSIS packages, SSAS cubes, and SSRS reports. Because all of these objects can be documented in a single snap shot, you are then able to easily perform impact analysis across all objects, which has a pretty slick UI and is very intuitive. You can also do snapshot comparison across your whole stack, so you can see how any object in a snapshot differs from a previous snapshot.

image

One other thing to mention about documenting SSIS packages is that BI Documenter is the only documentation tool that not only documents the SSIS packages textually, but also visually!

screen30

This means that after you document you SSIS packages, you can view the SSIS packages (Including the Data Flow Tasks!) just like you would in BIDS. Very cool!

BI Documenter is a pretty cool documentation tool that is very well rounded and since it’s the only tool on the market that can document the entire BI stack all together, its basically you’re only choice if you want to do impact analysis across the stack. BI Documenter also has a lot of awesome features coming up in the next release such as documenting server level objects, like SQL Agent Jobs, Linked Servers, Backup Locations, and others.

If you’re in the market for a rock solid documentation tool with the kind of support you can count on, I’d highly recommend that you check out BI Documenter and download the free trial. It’s at the very least worth a look.

image

Errors, MDX, SSAS

MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

2 Comments

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=“Currency“Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

Pragmatic Works, SSAS

SSAS Workshop in Phoenix, AZ 6/21 – 6/22

Capture

Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

Pragmatic Works, SSIS

Add 26 High Powered Tasks to your SSIS Toolbox

As a Business Intelligence Consultant with Pragmatic Works, I have the pleasure of being able to take for a spin any of the sports cars in the garage of Pragmatic Works software. And if you’ve frequented my blog before, you’ve heard about how much I love using BI xPress to speed up my SSIS package development. One of the coolest pieces of software out of Pragmatic Works is Task Factory.

Task Factory is a collection of high performance SSIS tasks that allows you to add and extend layers of functionality to your SSIS packages. One of the most powerful transforms that is included with Task Factory is the Dimension Merge SCD, formerly known as the Kimball SCD transform.

Dim Merge SCD

The Dimension Merge SCD transform is 100 X faster at handling Type 1 and Type 2 slowly changing dimensions than the native Microsoft SCD component which uses the slow-as-mess OLE DB Command transform. Being able to handle very large dimensions up to several million rows makes the Dimension Merge SCD included with Task Factory one of the most powerful transforms included with Task Factory. The Dimension Merge SCD offers the power and flexibility of the Kimball SCD with the support of the expert staff at Pragmatic Works.

One of my personal favorite new transformations just recently added to Task Factory is the Regex Replace transform.

Regex Replace

Previous to the development of the Regex Replace transform, I would have to use a Script Component and write some code to use regular expressions. But with the Regex Replace transform, I can easily and quickly use reg ex to either replace reg ex matched data or extract the reg ex matched data to a new column or the existing column.

The newest version of Task Factory includes 26 SSIS tasks and transforms that definitely are worth taking a look at. And if you’re even a little bit familiar with Pragmatic Works, you know of our dedication to the SQL community. With that said, Pragmatic Works offers a completely free Community Edition version of Task Factory, which includes five tasks and transforms such as the File Properties Task and the Data Validation Transform. Go here to download the latest version of Task Factory.

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

%d bloggers like this: