January 2010

You are currently browsing the monthly archive for January 2010.

Introduction

This article is aimed at report developers who are used to develop reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.

It demonstrates how to build a report using SQL Server Reporting Services 2008 with data coming from an OLAP cube running on SQL Server Analysis Services 2008.

The OLAP database used in the article is called “Adventure Works DW 2008”, available for download at CodePlex.

If you’re fairly new to Reporting Services (aka SSRS) and you find that this article is going a bit too fast, I’d like to point you to my other article which explains how to build a report that’s retrieving data using regular stored procedures.

OLAP <> OLTP

When people are talking about databases, what they are usually referring to are “regular” relational OLTP databases.  OLTP stands for Online Transaction Processing.  As the name implies, these types of databases are built to handle many simultaneous transactions (consisting of actions such as inserts, updates, deletes) in real-time.  I’m sure you’re familiar with these types of database so I won’t go further into them.

OLAP (Online Analytical Processing) on the other hand is a totally different story.  OLAP cubes are built to answer multi-dimensional analytical queries as fast as possible.  For that purpose, what you can find in such a database are measures (these are the numbers) stored in cubes, and dimensions which allow filtering the measures.  This filtering is often referred to as slicing and dicing.  Furthermore, OLAP cubes contain pre-aggregated data, again to be able to answer queries as fast as possible.

Let’s make this clear with an example.  Imagine the following request:

“Give me the sum of all sales of product X for period Y in country Z.”

Three dimensions can be recognized in that request: “product X” is found in the Product dimension, “period Y” in the Date dimension and “country Z” in the Geography dimension.  (I’ve used the actual dimension names as they are called in the Adventure Works OLAP database.)

Each dimension consists of attributes and attribute hierarchies and it’s those attributes that you’re actually referring to when building an MDX query.  MDX stands for Multidimensional Expressions and that is the language used to query an OLAP database, just like you use SQL to query a relational database.

Looking at our example, what we need is for the Product attribute in the Product dimension to be equal to X.  An attribute in a dimension can also be written as [Dimension].[Attribute], thus we also want [Date].[Date] to be equal to Y and [Geography].[Country] equal to Z.

As for the measure part, that’s what “the sum of all sales” is referring to.  When looking at the measures available in the Adventure Works cube, one of the measures that would fulfill the request is the Reseller Sales Amount in the Reseller Sales measure group.  The Analysis Services engine searches the cube and retrieves the aggregated number for [Measures].[Reseller Sales Amount] available at the intersection of [Product].[Product] X, [Date].[Date] Y and [Geography].[Country] Z.

OLAP cubes are usually, although not necessarily, build on top of a data warehouse.  In SQL Server, a data warehouse is still a relational database, unlike an OLAP cube, but the table structure is different from an OLTP database.  A data warehouse contains tables that represent dimensions and other tables that contain the facts.  The facts are the numbers, so the measures that were mentioned earlier.  This is called a dimensional model.  Dimensional modeling was invented by Ralph Kimball, one of the pioneers in data warehousing.  For completeness I’d like to mention that another data warehousing approach was described by Bill Inmon.  I’ll leave it up to you to do some research on both approaches and decide for yourself which one you prefer, possibly even a mix of both.

As far as the “Adventure Works DW 2008” OLAP database is concerned, it’s built on top of the AdventureWorksDW2008 dimensional database.

Okay, I believe this theoretical explanation was sufficient for now, let’s start with the report!

Your First Report

Business Requirements

You’ve gotten the assignment to create a report that shows the reseller sales numbers by region.  The highest level to be shown is Country, with drilldown through State/Province to City.

Creating The Shared Data Source

Just like when building reports on OLTP databases, we’re not going anywhere without a Data Source.  I’m going to create a Shared Data Source called OLAP_AdventureWorks.rds:

Shared Data Source connecting to Adventure Works OLAP Database

The Type that we need is Microsoft SQL Server Analysis Services, which is the SQL Server service that’s running the OLAP databases.  Furthermore I’ve selected the “Adventure Works DW 2008” database.

Connection Properties specifying the Adventure Works DW 2008 OLAP database

There’s no need to type the database name yourself.  After you’ve provided sufficient credentials in the Credentials page, you can just select it from the dropdown in the Connection Properties screen.  This screen is opened by clicking that Edit button on the Shared Data Source Properties window.

Your First OLAP Dataset

I’ve created a new report called FirstOLAPReport.rdl.  In that report I’ve specified that I’ll be using the Shared Data Source created earlier.  This source is known as srcAdventureWorksOLAP in my report.

Next step is to create the dataset.  I’m calling it dsResellerSalesByRegion.  As this is our first OLAP report, we’re not going to write the MDX ourselves but we will use the Query Designer which is opened by clicking the button that has the words Query Designer printed on them, how difficult can that be?!

How to open the MDX Query Designer

The BIDS knows that it should open the MDX Query Designer because our data source is connecting to an Analysis Services server.  All we need to do now is to drag the measures and dimension attributes that we require into the area marked with “Drag levels or measures here to add to the query.”.

Let’s start by dragging our measures into that area.  We need two measures, both located in the Reseller Sales measure group.  They are called Reseller Order Quantity and Reseller Sales Amount.  Following screenshot shows the situation after the first measure has been added.  The second measure was being dragged into it as well.  When dragging items into the area, a vertical blue line appears to indicate where the item can be added.

MDX Query Designer: dragging a measure into the query

Next I’m going to drag the Geography hierarchy, located in the Geography dimension, into the design area.

MDX Query Designer: dragging a hierarchy into the query

Now we’ve got all the data we need for our report.

As you have noticed, the Query Designer automatically executes the query each time it gets modified when you’re dragging an item into the design area.  If you don’t want this behaviour, it can be switched off by clicking the Auto Execute button in the toolbar (indicated by a red 1 in the screenshot below).

Query Designer toolbar

Another interesting button is the Design Mode button (indicated by a green 2).  This one allows you to toggle between the graphical designer and the text editor.  By clicking it you can see the actual MDX query that the designer has prepared for you.

As you can see, the query is nicely formatted using capitals for the keywords and so on.  Well, no, actually it’s the worst editor around!  No syntax coloring, no multi-line formatting, nothing.  So if you are going to take a close look at the query, I recommend you to use the Management Studio.  Connect to your Analysis Services server, locate your database and right-click it in the Object Explorer.  Then choose New Query > MDX and paste the query into that new window.  You’ll still need to manually break it down into different lines but at least you get syntax coloring.  Furthermore, if you’re going to make manual modifications to it, you’ve got some command completion and error indicators as well.

Please take into account that once you’ve made manual changes to your query, you cannot switch back to the graphical designer.  Well, you can, but you will lose all manual modifications.  Don’t worry about doing it accidentally though, a nice pop-up will warn you:

Warning message when switching back to design mode.

Something else that you’ll also notice is that the results displayed in the Query Designer and those displayed in the Management Studio are not exactly the same.  That’s because both environments interpret the results differently.  Remember, you’re not retrieving two-dimensional row/column data like with a SQL query.  You’re retrieving multi-dimensional data!

If you take a closer look at the query that we’ve produced above, it’s similar to this:

SELECT something ON COLUMNS,
    something_else ON ROWS
FROM [Adventure Works]
That query is selecting data on two axes: COLUMNS and ROWS.  But in fact, MDX supports up to 128 axes.  However, the client tools that we are using here are not able to visualize that kind of cellset (as the result set of an MDX query is also called).
 
Okay, enough about our dataset.  We’ve got the data, let’s put it on the report!
 

Displaying The Result Set

As a reference, these are the fields available in our dataset:

Fields available in OLAP dataset

Without going into too much detail – there’s no difference compared to reporting off a relational database – I’ve set up a table with three grouping levels on the rows.  I’ve also added some makeup like background colors and font modifications.

As shown in following screenshot, the highest-level group is Country, followed by State_Province and City to conclude, just as specified in the requirements mentioned at the start of this chapter.

Table with three groupings defined

Rendering the report in preview gives us something like this:

Report without any numeric formatting applied

What is still missing at this point is decent formatting for those numbers!  And here’s where we can take advantage of the fact that we’re retrieving data from an OLAP cube.  A cube developer has the possibility to define the format for the measures in the cube itself.  Doing that ensures that the same formatting is applied no matter what OLAP client tool is used.  Any client that supports this way of formatting will show the numbers using the same format.

As you’ve seen in that last screenshot, there’s no formatting applied at all.  Does this mean that there was no format defined in the cube?  Let’s find out!

A Little Walk Into The Analysis Project

We are going to open up the Analysis Services project that contains the cube definition.  If you don’t have any experience with SSAS, don’t worry!  We will just have a look at a couple of properties and that’s it, plus I’ll explain each step as needed.  In case you’ve forgotten where the sources are located, this is the default location: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\.  I’m opening the project located under the \enterprise subfolder by double-clicking the Adventure Works.sln file.

Once the project is loaded into the BIDS, locate and open the Adventure Works.cube in the Solution Explorer.  You can find it in the Cubes folder of the Adventure Works DW project.

By default it will open the cube Design showing the first page called Cube Structure.  At the top-left, we’ve got the Measures pane.  The measures are shown in measure groups.  Open the group called Reseller Sales.  Now locate the measure called Reseller Sales Amount and select it.

Cube in Design with Reseller Sales Amount selected

Now that we’ve selected one of the measures that we are retrieving in our report, have a look at the Properties window.  In case it’s not open yet you can right-click the measure and select Properties.  The property that we’re interested in is called FormatString.

Properties of the Reseller Sales Amount measure showing Currency as format string

The cube developer has specified that this measure should be shown as being a Currency.

Now that you’re in the cube, have a look at the properties for our other measure, the Reseller Order Quantity.  This one is being formatted as #,#.

The FormattedValue Field Property

So why are we not seeing those formats in our report?  Because by default they are not applied in an SSRS report!  When dragging fields from the Report Data window onto the design area, what the BIDS is retrieving is the Value property of the field.  However, there’s also a property called FormattedValue.

(You may want to make a copy of your report before applying the following changes.)

Now, change the six table cells that are showing the numbers (so including the ones showing the totals) to retrieve the FormattedValue property instead of the Value property.  The expression for the totals of the Reseller Sales Amount looks like this:

=Sum(Fields!Reseller_Sales_Amount.FormattedValue)

Once you’ve done that, have a look at the Preview:

Report Preview showing no numbers after retrieving the FormattedValue property

That doesn’t look right, does it?  We’ve lost our numbers!

Now hit the Refresh button: Refresh button in Report Preview

This time we’ve got some numbers:

Report Preview showing formatted numbers, and errors!

But we’ve also got some errors for free!  Looking at the Output window we get some extra details on the reason for the error.  Here’s one of them:

[rsAggregateOfNonNumericData] The Value expression for the textrun ‘Reseller_Order_Quantity1.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric.  Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

In short, what it says is that our data is not numeric.  And this poses an issue when it tries to apply the SUM() aggregate function.  Right, as our data now contains formatting, it became a string instead of a number, and strings can’t be added together using SUM().

So that’s not a good way to apply the formatting, not in this case anyway.  Luckily there’s another method to do that.

But first, undo those last changes and replace the FormattedValue with the Value property.

(Or switch back to the original report if you took a copy earlier.)

The Cell Properties

What exactly is our MDX query doing?  I’m taking a closer look at it by taking it from the Dataset Properties window and pasting it into a MDX query window in the Management Studio:

SELECT
NON EMPTY { [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Quantity] }
ON COLUMNS,
NON EMPTY { ([Geography].[Geography].[Postal Code].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

Besides retrieving the requested measures and dimension attributes, it’s retrieving several Cell Properties, including FORMATTED_VALUE and FORMAT_STRING.  I believe that the first one rings a bell by now.  What we’re going to do is to retrieve the second one and apply it as Format property for our numeric table cells.

In the report’s Design, select one of the table cells containing a number.  In the Properties window, one of the properties is called Format.  Click to select it, then in the dropdown choose Expression….  For each of the six numeric cells, create an expression similar to the following:

=Fields!Reseller_Order_Quantity("FORMAT_STRING")

The example above tells the BIDS to retrieve the FORMAT_STRING cell property from the Reseller_Order_Quantity field.

Tip: you don’t need to open up the Expression builder for each of the six cells.  You can just copy/paste the string from the Format field.  Just ensure that you’re retrieving the format from the same field as the one that the cell is displaying.

Now let’s have a look at the Preview again:

Format is working for the quantity amounts but not for Currency!

Hmm,  the quantities are fine now, but the currencies are not!  So, let’s try out yet another method for those cells.

For the three cells containing a currency measure, remove the Format property – it’s not working anyway!

Next, change the expression that’s retrieving the Value property to something similar as this one:

=Format(Sum(Fields!Reseller_Sales_Amount.Value),
    Fields!Reseller_Sales_Amount("FORMAT_STRING"))

This expression applies the value of the FORMAT_STRING property using the Format() function.  In this particular case it’s the expression used to produce the Reseller Sales Amount total.

Having modified all three currency cells, here’s another Preview look:

Both Currency and regular numeric cells are showing formatted values!

That certainly looks better doesn’t it?!

Okay, to conclude, let’s activate drilldown by setting the subgroup levels to a collapsed state by default.

I will not go into full detail on this.  To start, make sure that the cells that are going to contain the +/- toggle have gotten a decent name, such as txtCountry for the cell that shows the Country name.  Then edit the properties of the subgroups by setting Visibility to Hide.  Also, activate the Display can be toggled by this report item checkbox and select the textbox showing the label one level higher.  Shown below is how to configure the group on State_Province.

Group Properties showing how to activate drilldown

 

Let’s have another look at the report Preview:

Fully working drilldown report

By default all nodes were collapsed.  I’ve expanded a couple of them just to show that it’s all working.

The InitialToggleState Property

Okay, I will not let you go just yet.  To really conclude I’ll let you in on a little feature related to the drilldown.  Open up the group properties for the State_Province group and set the initial visibility to Show (leave the “Display can be toggled by this report item” checked!).  Then checkout Preview:

Visibility toggle is broken!

Wow, that’s weird, the country level is expanded and yet there’s a plus icon in front of the country’s name.  Clicking it will collapse the states and change the icon to minus.  If that isn’t mixed up then I don’t know what is!

Well, the solution to this problem is simple.  Select the textbox showing the country name and locate the InitialToggleState property.  By default this is set to False, which means collapsed or in other words, False shows the plus icon.  Change it to True and now your initial state icon will be a minus!

Conclusion

With this article I believe to have shown you how to get started with reporting off an OLAP cube while throwing in a couple of tips in the process.

Have a look at another article that I wrote earlier, it explains an issue which you may run into when taking OLAP reporting a step further: SSRS and MDX: Detecting Missing Fields

Happy Reporting!

Valentino.

References

BOL 2008: The Basic MDX Query

BOL 2008: Using Cell Properties (MDX)

MDX: Retrieving Cell Properties by Greg Galloway

Share

Tags: , , , , , ,

If you’ve got some experience building Data Flows in your Integration Services packages, you probably already know that SSIS has its own representation of the different data types.  The names of these data types start with DT_, followed by the actual type such as BOOL for boolean and STR for string.

Some data types you use every day and others only once in a while.  So some you already know by heart and others you don’t.  A nice example of one that I certainly don’t need to look up anymore is the following cast as used in a Derived Column Transformation: (DT_STR, 100, 1252)YourField.  It converts YourField to a string field of length 100 using the ANSI – Latin 1 code page.

But the reason for this post are the other types, the ones not used daily.  Because each time that I need info on one of those, I find myself ploughing through several BOL pages before finding that page of which I know it exists but where, oh where?!

So, on the following Books Online page you can find a list of all SSIS data types and their definition: Integration Services Data Types

And at the bottom of that same page there’s an interesting table showing the mapping between the SSIS types and those of several RDBMS, including SQL Server of course.

Another interesting page is this one: Working with Data Types in the Data Flow

The bottom half of this page contains a mapping table between the SSIS data types and their corresponding managed type in .NET.  This is especially useful when you’re doing some custom development like a custom data flow component.  But you don’t need to take it that far, even when you’re just using the built-in Script Component task for some complex data conversions, you’ll find this useful.  After all, you’re also programming in a managed .NET language in that component.

Okay, that’s it for now, hopefully this will save you some time searching for those reference pages.  I already know that I’ll be coming back to this post once in a while :-)

Share

Tags: ,

If you’re interested in seeing the next Service Pack for SQL Server 2008 released, vote for it at this Microsoft Connect page!  Now that the release date for R2 has been announced, hopefully the next one is for SQL 2008 Service Pack 2.

If you’re still on 2005 and thus more interested in an SP4 for that version, it has also been posted at Connect.

How did I find out?  Through the following blog posts at SSQA.NET:

SQL Server 2008 SP2

SQL Server 2005 SP4

Update (27 Feb 2010):

SQL Server 2008 SP2 is scheduled for Q3 2010.

SQL Server 2005 SP4 is scheduled for Q4 2010.

See this post on the Microsoft SQL Server Release Services blog for details.

Share

Tags: , ,

What?

image

Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances.

Within a single SQL Server instance, Service Broker provides a robust asynchronous programming model. Database applications typically use asynchronous programming to shorten interactive response time and increase overall application throughput.

Service Broker also provides reliable messaging between SQL Server instances. Service Broker helps developers compose applications from independent, self-contained components called services. Applications that require the functionality exposed in these services use messages to interact with the services. Service Broker uses TCP/IP to exchange messages between instances. Service Broker includes features to help prevent unauthorized access from the network and to encrypt messages sent over the network.

(The above was quoted from the BOL.)

Who?

Dr. Nico Jacobs, PhD in Data Mining, trainer/consultant at U2U focusing on SQL Server/BI.

When?

February 11, 2010.

Where?

Avenade, Vilvoorde.

Register here: http://sqlug.be/nextevent/

More info at the SQLUG site.

See you there!

Share

Tags: , ,

The LNCP

Before you read what I’ve written below, I suggest that you have a look at this blog post by Tim Ford first.  Then it will become clear why this post is not related to SQL Server or even IT :-)

Let me tell you this little story about Luke, a bloke living down the street not far from the city center.  Well, it was a Friday night and he was going to the pub, just like he usually does on Friday nights.  It’s his favorite pub, the LNCP, and he has a habit of meeting his mates there.  The LNCP, some people say it stands for the Live Nude Cats Pub because that’s what they’ve seen there after they’d had several drinks too many.  Or at least, that’s what they think and claim to have seen.  Luke wouldn’t know and he doesn’t mind, he’s allergic to cats anyway, nude or not!

LNCP, the real meaning is Local Natural Coffee Pub, which is a beverage that you can get there 24/7.  It’s known for its high level of several substances, including caffeine obviously. But also some other rather undefined ingredients.  The locals have come to appreciate this beverage – just like Luke – and especially in the weekend they consume quite a lot of it.  The pub owner doesn’t want to give away her secret, no one knows how this magical drink is created.  In fact, all that she’s told them about it so far is that the brewing process is done by simply using some magical tool, library or bong.  Which isn’t of much use to those who’ve tried to reproduce it at home.  They’ve searched all local libraries for books containing its formula, nothing.  They’ve asked around if there were any wizards who knew of magical tools that could produce such a drink, no result either.  And let’s not mention the bong – that’s what they’d use after yet another unsuccessful search to temporarily forget about the fact that once again they hadn’t succeeded.

Anyway, Carabella – which is the pub owner’s name – she was quite good-looking.  (Isn’t that always the case with female pub owners in a fantasy story?)  And Luke and his mates, as they were regular customers, they knew her quite well.  And it happened to be that on this Friday night she showed them a special little hidden door at the back of her pub.  She told them, if you drink three of my magical LNCs in less than five minutes time and then walk (or better, crawl, as the door was quite low) through this door, you’ll experience the most magical moment in your life!

So the guys – they didn’t have any other plans that night and they were quite curious after what she’d told them – they accepted the offer.  They went through the door and came in a sort of stable.  Looked a bit like a horse stable, only, it was different.  They didn’t really know why, figured it was probably just some magic playing around with them.  Then suddenly a little white horse came out from under the hay.  It spoke to Luke!  The guys first wondered about what was happening and then they saw: the pony had wings, it was a Pegasus!  And as everyone knows, Pegasus can communicate with humans.

The Pegasus said to Luke:

Could I assist you in any way?

Luke thought about it for a while but answered negatively.  Although they are strong creatures, he wouldn’t know why he would need assistance from a Pegasus.  But the little pony insisted and said:

Are you sure?

But yeah, Luke was sure.

One of his mates shouted out that there was a door at the other end of the stable and suggested they should go and have a look.

Behind this door there was a magical landscape: a huge valley filled with large fruit trees singing all kinds of song, birds in many colors looking like rainbows all around, animals in all kinds of shapes.  But there’s one problem.  A huge problem.  They are located at the top of a very steep mountain!  When Luke will look out the door the first time, he will appreciate the gorgeous landscape but as soon as he looks down he’s going to be very sad about the pony, or better, about the fact that he refused the pony’s help!  Because after all – as everyone knows – Pegasus can fly, even the small ones!

Then Luke woke up, all sweaty and reeking of something, he didn’t immediately know what it was.  When he looked down he noticed that he was in the middle of some horse dung in a stable he didn’t recognize.  He heard something move behind his back and turned around to face a small horse.  He vaguely seemed to know this animal.  Suddenly it spoke to him:

As I’ve started, so I’ll finish!  Would you require my help?

Luke thought about it for a moment and replied:

Sure, I’ll take your help.

Then the horse said:

Well done young Skywalker!  Just ask George “Let’s Have Padme Die Of A Broken Heart Instead Of Anakin Crushing Her To Death” Lucas what on earth LNCP stands for next time you see him.

And the little white horse took off its mask, or better, her mask.  Luke didn’t know what was going on but heard her say:

Shall we have a shower?  You really need to get rid of that smell!

Luke didn’t need to think twice this time!

Morale to the story: any man given a second chance should think twice about it before answering!

Share

Tags:

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved