Kevin S. Goff: Stages of Data

12/2/2016: An introduction to Columnstore indexes and SQL Query Store in SQL Server 2016

Most people are familiar with the phrase, "this will kill two birds with one stone". If you're not, the phase refers to an approach that addresses two objectives in one action. (Unfortunately, the expression itself is rather unpleasant, as most of us don't want to throw stones at innocent animals!)

Today I'm going to cover some basics on two great features in SQL Server: the Columnstore index (available only in SQL Server Enterprise) and the SQL Query Store. Microsoft actually implemented the Columnstore index in SQL 2012 Enterprise, though they've enhanced it in the last two releases of SQL Server. Microsoft introduced the Query Store in SQL Server 2016. So, what are these features and why are they important? Well, I have a demo that will introduce both features and show how they can help us.

Before I go any further, I also cover this (and other SQL 2016 features) in my CODE Magazine article on new features SQL 2016.

As a basic introduction, the Columnstore index can help speed up queries that scan/aggregate over large amounts of data, and the Query Store tracks query executions, execution plans, and runtime statistics that you'd normally need to collect manually. Trust me when I say, these are great features.

For this demo, I'll be using the Microsoft Contoso Retail Data Warehouse demo database. Loosely speaking, Contoso DW is like "a really big AdventureWorks", with tables containing millions of rows. (The largest AdventureWorks table contains roughly 100,000 rows at most). You can download the Contoso DW database here: https://www.microsoft.com/en-us/download/details.aspx?id=18279. Contoso DW works very well when you want to test performance on queries against larger tables.

Contoso DW contains a standard data warehouse Fact table called FactOnLineSales, with 12.6 million rows. That's certainly not the largest data warehouse table in the world, but it's not child's play either.

Suppose I want to summarize product sales amount for 2009, and rank the products. I might query the fact table and join to the Product Dimension table and use a RANK function, like so:

 

Here's a partial result set of the top 10 rows, by Total Sales.

 

On my laptop (i7, 16 GB of RAM), the query takes anywhere from 3-4 seconds to run. That might not seem like the end of the world, but some users might expect near-instant results (the way you might see near-instant results when using Excel against an OLAP cube).

The only index I currently have on this table is a clustered index on a sales key. If I look at the execution plan, SQL Server makes a suggestion to add a covering index to the table:

 

 

Now, just because SQL Server suggests an index, doesn't mean you should blindly create indexes on every "missing index" message. However, in this instance, SQL Server detects that we are filtering based on year, and using the Product Key and Sales Amount. So, SQL Server suggests a covering index, with the DateKey as the index key field. The reason we call this a "covering" index is because SQL Server will "bring along the non-key fields" we used in the query, "for the ride". That way, SQL Server doesn't need to use the table or the clustered index at all – the database engine can simply use the covering index for the query. Covering indexes are popular in certain data warehousing and reporting database scenarios, though they do come at a cost of the database engine maintaining them.

Note: Covering indexes have been around for a long time, so I haven't yet covered the Columnstore index and the Query Store.

So, I will add the covering index:

If I re-execute the same query I ran a moment ago (the one that aggregated the sales amount for each product), the query sometimes seems to run about a second faster, and I get a different execution plan, one that uses an Index Seek instead of an Index Scan (using the date key on the covering index to retrieve sales for 2009).

So, prior to the Columnstore Index, this could be one way to optimize this query in much older versions of SQL Server. It runs a little faster than the first one, and I get an execution plan with an Index Seek instead of an Index Scan.

However, there are some issues:

1. The two execution operators "Index Seek" and "Hash Match (Aggregate)" both essentially operate "row by row". Imagine this in a table with hundreds of millions of rows.
2. Related, think about the contents of a fact table: in this case, a single date key value and/or a single product key value might be repeated across hundreds of thousands of rows (remember, the fact table also has keys for geography, promotion, salesman, etc.) So, when the "Index Seek" and "Hash Match" work row by row, they are doing so over values that might be repeated across many other rows.

This is normally where I'd segue to the SQL Server Columnstore index, which offers a scenario to improve the performance of this query in amazing ways. But before I do that, let's go back in time. Let's go back to the year 2010, when Microsoft introduced an add-in for Excel known as PowerPivot. Many people probably remember seeing demos of PowerPivot for Excel, where a user could read millions of rows from an outside data source into Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed "VertiPaq". This in-memory technology in PowerPivot would basically take duplicate business key/foreign key values and compress them down to a single vector. The in-memory technology would also scan/aggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box.

Why am I taking this little stroll down memory lane? Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains.

So, I'm going to create a columnstore index on the table, and I'll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I'll create a duplicate copy of FactOnlineSales (I'll call it FactOnlineSalesDetail_NCCS), and I'll create a columnstore index on the duplicated table – that way I won't interfere with the original table and the covering index in any way.

 

Next, I'll create a columnstore index on the new table:

Note several things:

• I've specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no "key". We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore.
• To use the analogy of PowerPivot for Excel – when we create a columnstore index, we're telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot

So, I'll re-run the query, this time using the duplicated FactOnlineSalesDetail_NCCS table that contains the columnstore index.

This query runs instantly – in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial "bat of an eyelash". We could look at the execution plan (and in a few moments, we will), but now it's time to cover the Query Store feature.

Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we'd like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we'd like to see the same statistics that we'd be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query.

Well, that's what the Query Store allows us to do – we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later.

So, I'm going to enable the Query Store on the Contoso database with the following command (and I'll also clear out any caching):

Then I'll run the two queries (and "pretend" that I ran them hours ago):

 

Now let's pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them? Fortunately, that's quite easy. If I expand the Contoso DW database, I'll see a Query Store folder.

The Query Store has tremendous functionality and I'll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index.

So I'll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don't lie – the columnstore index outperformed the original table/covering index by a factor of almost 7 to 1!!!

 

I can change the metric to look at memory consumption instead. In this case, note that query #2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents "in-memory" technology – SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index.

 

OK, so we have some graphs to view execution statistics – can we see the execution plan (and execution operators) associated with each execution? Yes, we can! If you click on the vertical bar for the query that used the columnstore index, you'll see the execution plan below.

The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100% of the cost of the query. You might be saying, "Wait a minute, the first query used a covering index and performed an index seek – so how can a columnstore index scan be faster?"

 

 

That's a legitimate question, and fortunately there's an answer. Even when the first query performed an index seek, it still executed "row by row". If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW, which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently.

 

 

Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query)

 

Finally, you might be asking, "OK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel – but my query only wanted data for 2009. So is SQL Server scanning over the entire set of data?" Again, a good question. The answer is, "Not really". Fortunately for us, the new columnstore index buffer pool performs another function called "segment elimination". Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009.

I'll stop here. In subsequent blog posts I'll cover both the columnstore index and Query Store in more detail. Essentially, what we've seen here today is that the Columnstore index can significantly speed up queries that scan/aggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later.

2/19/2014 – New Presentation later today on Power BI for Office 365

Later today/this evening I'll be posting a new presentation – Introduction to Power BI for Office 365

This is now generally available from Microsoft and has some interesting and valuable functionality, especially for those who are interested in publishing BI content to SharePoint but don't want to deal with all the complexities of SharePoint.

11/10/2013: SQL Server Topic of the Week: Getting Started with Windows Azure SQL Databases

Getting started with Windows Azure and creating SQL Databases "in the cloud" can be a bit daunting, especially if you've never tried out any of Microsoft's cloud offerings.

Fortunately, I've created a webcast to help people get started. This is an absolute beginner's guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure.

You can go to the BDBI Webcasts of this website and check out my webcast (dated 11/10/2013).

Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here.

KG

11/3/2013: SQL Server Topic of the Week: Snapshot Isolation Level

Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005.

To this day, there are still many SQL developers, many good SQL developers who either aren't aware of this feature, or haven't had time to look at it.

Hopefully this information will help.

Slide deck here

Companion webcast will be uploaded in the next day – look for it in the BDBI Webcasts section of this blog.

KG

10/26/2013: Transact-SQL Tip of the Week: Passing Tables as Parameters

I'm going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years

Here's a challenge many developers face. I'll whittle it down to a very simple example, but one where the pattern applies to many situations.

Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id.

create procedure dbo.UpdateVendorOrders

@VendorID int

As

Begin

update Purchasing.PurchaseOrderHeader set Freight = Freight + 1

where VendorID = @VendorID

end

go

Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If you've worked with SQL Server, you can probably guess where I'm going with this. The big question is – how do we pass a variable number of Vendor IDs? Or, stated more generally, how do we pass an array, or a table of keys, to a procedure? Something along the lines of…

exec dbo.UpdateVendorOrders @SomeListOfVendors

Over the years, developers have come up with different methods:

• Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (http://code-magazine.com/articleprint.aspx?quickid=0503071&printmode=true, Tip #3)
  
  
• In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (http://code-magazine.com/articleprint.aspx?quickid=0703041&printmode=true, Tip #12)
  
  
• Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure.

All of these certainly work, and developers have had to use these techniques before – because for years there was NO WAY to directly pass a table to a SQL Server stored procedure.

Until SQL Server 2008 – when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure.

Now, it does require a few steps. We can't just pass "any old table" to a procedure. It has to be a pre-defined type (a template). So let's suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc.

 

CREATE TYPE IntKeysTT AS TABLE

( [IntKey] [int] NOT NULL )

GO

 

So I've created a Table Type…called IntKeysTT. It's defined to have one column – an IntKey.

Now…suppose I want to load it with Vendors who have a Credit Rating of 1…..and then take that "list" of Vendor keys and pass it to a procedure:

DECLARE @VendorList IntKeysTT

 

INSERT INTO @VendorList

SELECT BusinessEntityID from Purchasing.Vendor

WHERE CreditRating = 1

So, I now have a table type variable – not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). It's in server memory (unless it needs to spill to tempDB) and is therefore "private" to the connection/process.

OK, can I pass it to the stored procedure now? Well, not yet – we need to modify the procedure to receive a table type. Here's the code:

create procedure dbo.UpdateVendorOrdersFromTT

@IntKeysTT IntKeysTT READONLY

As

Begin

update Purchasing.PurchaseOrderHeader set Freight = Freight + 1

     FROM Purchasing.PurchaseOrderHeader

             JOIN @IntKeysTT TempVendorList

ON PurchaseOrderHeader.VendorID = TempVendorList.IntKey

End

go

 

Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you won't want to – you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable.

So there you have it. A bit of work to set up the table type, but in my view, definitely worth it.

Additionally, if you pass values from .NET, you're in luck. You can pass an ADO.NET data table (with the same tablename property as the name of the Table Type) to the procedure. For .NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit!!!

Finally – I want to talk about another approach people have used over the years….SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way:

 

set nocount on

DECLARE @VendorID int

DECLARE db_cursor CURSOR FAST_FORWARD FOR

SELECT BusinessEntityID from Purchasing.Vendor

where CreditRating = 1

 

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @VendorID

 

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC dbo.UpdateVendorOrders @VendorID

FETCH NEXT FROM db_cursor INTO @VendorID

END

CLOSE db_cursor

DEALLOCATE db_cursor

 

The best thing I'll say about this is that it "works". And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. That's not a good idea in a large production environment. Additionally, the more the # of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be.

When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger # of vendors (84 vendors), the different was staggering – 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster.

Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature that's more closely modeled to the way developers are thinking – specifically, "how do we pass a table to a procedure?" Now we have an answer!

Hope you find this feature help. Feel free to post a comment.

KG

Special announcement - new weekly webcast in January 2013

To anyone interested in Microsoft SQL/BI Topics 
(and especially for former SetFocus students)...

I'm starting a new weekly online webcast/radio show in January.   

http://www.bdbiradio.com

For more information, here's a link to a PowerPoint slide deck: 

More details to follow...Happy Holidays to all!
KG 

3/27/2012 – Video recordings from yesterday’s SQL Server 2012 Webinar For SetFocus

Here are the videos from our SetFocus webinar on new features in SQL Server 2012:

Day 1 (3/27/2012) covered the new Columnstore Index, new T-SQL Features, and new SSIS Features
Day 2 (3/28/2012) covered the new features in Analysis Services tabular model, PowerPivot 2.0, and Power View

Day	Early morning	Mid-morning	Early afternoon	Late afternoon
3/27/2012	Video 1 of 4	Video 2 of 4	Video 3 of 4	Video 4 of 4
3/28/2012	Video 1 of 4	Video 2 of 4	Video 3 of 4	Video 4 of 4

Each video is anywhere from 60 to 90 minutes long. Each download is a zip file with a WMV file inside that can be played with Windows Media Player.

Again, we invite everyone to check out our SetFocus Free Training portal, with over 100 hours of free video training on .NET, SQL Server/BI, SharePoint, and other topics.

Feel free to email me at [email protected] if you have any questions. 

10/10/2011 – Free Training from SetFocus

Whether you're a seasoned veteran or a rookie, everyone in this industry eventually finds themselves back at square one, trying to wrap their minds around a particular technology. You could be a mainframe or an AS/400 programmer who now needs to learn SQL Server….or you could be a VB programmer who needs to ascend the object-oriented programming learning curve….or you could find yourself in a situation where you need to learn the basics of SQL Server Integration Services very quickly.

Maybe you need to learn this content as a step towards getting a job, or just looking to strengthen your skills.

Yes, there's a large amount of content on the web – often scattered all over the place. Once in a great while you might find a single website or article that covers exactly what you need. Other times (quoting a famous musician) it can feel like "wading through a cesspool in search of a water lily".

Well, the company where I work as an instructor and a practice manager (www.setfocus.com) has done something rather amazing about this.

We've made some of our training content available on the web, FOR FREE.

Our CEO (Mike Landau) and our president (Jerry Masin) talk about this free training initiative in an article in NJBIZ: Mike Landau also talks about this during an interview. (Note: on this interview link, you'll need to right-click and open in a new browser window).

If you're interested, here's how to get started:

First, the site is http://freetraining.setfocus.com. The free registration process is simple. You create an account (near the upper right corner), provide a valid email address and some general information (name, city, reason for joining). You will NOT be prompted to enter a credit card. (Many websites claim to offer free content, but still ask for a credit card). Free means free.

You'll receive a confirmation email, and then you can enroll in any of our free training courses. Here's a list of what we offer for free:

• An online course on Object Oriented Programming with C#. There are slides, videos, and labs on OOP basics, Class design, inheritance, interfaces, abstract classes, and more. Some of the content comes from our .NET Master's Program.
  
  
• An online course on Relational Database Management concepts. There are slides and videos on relational database models, data modeling, normalization, data integrity, and DBA concepts. Some of the content comes from our SQL Server Master's Program.
  
  
• Video content from our Business Intelligence Master's Program. There are videos on SQL Server Integration Services, MDX Programming, plus a link to some online articles and blog entries on different BI topics. This content comes from our Business Intelligence Master's Program.
  
  
• Video content from our SharePoint Master's Program. These videos cover SharePoint 2010 Forms-based authentication, creating Silverlight applications in SharePoint, creating workflows, creating custom ASP.NET pages, and creating input forms using InfoPath. This content comes from our SharePoint Master's Program.
  
  
• Video content on how to get the most out of LinkedIn. You might be thinking, "videos on how to use LinkedIn?" That's right – LinkedIn has a number of videos on getting the most out of LinkedIn, and getting your name "out there". At first, I was a bit skeptical about LinkedIn – the old "quantity vs quality argument". But I've come to see the value. With a job market that gets more competitive every day, business-related social networking sites can be a valuable tool. You never know when a connection might lead to something!

In addition, back in July I did a 3+ hour webinar for SetFocus on the entire Microsoft Business Intelligence platform. The webinar contains segments from our Business Intelligence Master's Program. (The same program mentioned in point 3 above). The link for this 3+ hour webinar is http://www.setfocus.com/onlinecourses/bi/bi.aspx.

While we have a substantial amount of free content, we'll continue to evolve this free training portal over time.

Finally, because I believe in telling people the whole truth, SetFocus might contact you via email to see if you have any further training or career services needs. But there's no obligation, no strings attached. Having said that, I'd be remiss if I didn't also mention the unique strengths and competitive advantages that SetFocus offers (both in training and career services), which I blogged about a while back.

SetFocus is a Microsoft Gold Partner for Learning Solutions with Microsoft technologies. They have a long history of direct involvement in software community efforts. For years, they've hosted meetings for the Northern New Jersey .NET User Group, the New Jersey SQL Server User Group, and the New Jersey SharePoint User Group at their facility in Parsippany, NJ. They're also supportive of my community efforts as a speaker at different CodeCamp, SQL Server, and SharePoint events (not all companies are supportive of community efforts). This free training initiative is further proof.

So, if you're interested, take a few minutes and sign up. You have nothing to lose, and possibly things to gain!

11-07-2010 - SetFocus BI/SQL Master's Program Tip: SSRS 2008/SSRS 2008 R2 OLAP reports and overwriting MDX parameters

In the SetFocus Business Intelligence Master's Program, I show many examples of building SSRS reports against Analysis Services (OLAP) databases. I've actually been showing this going back to SQL Server 2005.

SSRS 2008 (and SSRS 2008R2) have some tremendous reporting enhancements, but unfortunately introduced a feature that caught some people by surprise. The issue is a rather intricate one, and goes as follows:

Suppose you create a report against an OLAP cube. You start by creating a report dataset, using the OLAP query designer. You drag some columns from the cube metadata into the result set area, and you also define one of the OLAP dimensions as a parameter. SSRS creates a second dataset with MDX code that retrieves member values from the dimension attribute you defined as a parameter in the primary dataset. (This allows SSRS to display the parameter values in a report dropdown).

With me so far?

Now suppose you want to modify the MDX code for the 2^nd dataset (which SSRS builds as a hidden dataset), to implement some custom behavior for the dropdown. For example, instead of showing all cities in a dropdown, you only want to show those cities with sales (or sales in the last 6 months, etc.) So you retrieve the MDX code for the 2^nd (hidden) dataset, and you modify the MDX code. You run the report again, and the dropdown shows values the way you want to show them.

OK, so life is good…but this story isn't over.

Now go back to the original (primary) dataset and add a column/measure. (For example, maybe initially the report had 4 measures, and you have to add a fifth). So you add a measure to the primary dataset, you save it, you modify the report layout to add the new measure/column, and then you run the report…

And then you discover something – the dropdown parameter isn't showing the values in the dropdown according to the logic you implemented when you manually modified the MDX code. As a matter of fact, the dropdown is showing the available parameter values as if you'd never modified the MDX code at all!!!

Here's what happened – when we changed the primary dataset to add a new column, SSRS (well, specifically, Business Intelligence Development Studio) overwrote our custom MDX code with the original MDX code for the dimension parameter!!!

OUCH! This is actual behavior in SSRS 2008 and 2008 R2. As for a workaround, there's good news and bad news. The good news is that there's a workaround in the form of a setting. The bad news (well, more like "not great news") is that BIDS doesn't expose the setting – we need to modify the actual XML code in the RDL file.

So let's look at an actual example:

Suppose we create a simple OLAP report against AdventureWorks 2008. We want to show Internet Sales Amount by Product Category, and we want the user to select a country from the Customer Dimension/Country Attribute (Figure 1)

Figure 1:

   

After we save this dataset, SSRS creates a 2^nd dataset that retrieves the countries from the Customer dimension. By default this 2^nd dataset doesn't show in the DataSet window pane. However, we can right-click and show Hidden Datasets, which will wind up displaying the 2^nd dataset (which SSRS called CustomerCountry, in Figure 2):

   

Figure 2:

   

If we right-click on this 2^nd dataset and modify the query, SSRS loads the query designer with the generated MDX code (Figure 3). The code retrieves all countries in alphabetic order, plus the All Country (Customer) total.

   

Figure 3:

   

Now suppose we want to modify the query (and therefore the contents of the dropdown) to show the countries in order of sales for the country. So we modify the MDX query to use the MDX ORDER function, to show the countries in order based on sales, in Figure 4: (i.e. country with the highest sales will be first in the dropdown)

Figure 4:

   

Now we run the report (which shows Product Category and Internet Sales amount, and the prompt for Country with the correct sort order), in Figure 5:

   

Figure 5:

   

OK, now let's go back to the original main dataset, and add a new column (Internet Gross Profit), in Figure 6:

Figure 6:

We add the column to the designer as well, and then go run the report (Figure 7). Notice how the dropdown appears to have "lost" the behavior we implemented with our custom MDX code. This is the root of the behavior in SSRS 2008. What happened was that when we modified the dataset in the previous step, SSRS overwrote our custom MDX changes (without warning us!) To prevent this behavior, we need to modify the RDL file manually, to add a new setting (next step).

   

Figure 7:

   

We need to open the RDL file manually, which you can do by right-clicking on the report in Solution Explorer and taking the option "View Code". At the end of the XML content for the hidden dataset, add the following line: <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> (Figure 8).

Figure 8:

Now to test, go back and repeat step 4 (to put the custom MDX code back into the hidden dataset), and then pull up the main dataset and add another column, such as Internet Freight Cost (Figure 9). Previously, this would overwrite our custom MDX code. However, because of the SuppressAutoUpdate setting, SSRS will not update the MDX code.

Figure 9:

   

So if we go run the report after making the dataset change, our MDX code now stays!

Figure 10:

05-24-2010 - SetFocus BI/SQL Master's Program Tip: Change Data Capture in SQL Server 2008

One of the important new features in SQL Server 2008 is Change Data Capture (also known as CDC). CDC implements change tracking into the database management system. (Change Data Capture, as a concept, has been around for many years. You can search on the term and find a good history of information on it.)

Before I dive into CDC in SQL Server, a little history: until SQL Server 2008, if we wanted to implement audit trail history (i.e. log insertions, updates, changes), the most effective method was through database triggers. Even with the new OUTPUT/OUTPUT INTO feature in SQL Server 2005, triggers were still the most reliable and effective way to detect and log the "before/after" values the audit logs require. And with any form of audit trail processing, reliability is ABSOLUTELY CRITICAL. Many applications have strict requirements for audit trail processing, to view historical changes (along with who made the changes, and when).

While database triggers are great (I've used them for years and I love them), critics make a valid point that the database management system should provide some sort of audit log "out of the box". And so Microsoft implemented Change Data Capture in SQL Server 2008. In both the SetFocus SQL Server Master's Program and Business Intelligence Master's Program, I cover Change Data Capture in SQL Server 2008. Here's an overview and a brief example of CDC.

First, I need to make sure SQL Server Agent is running. CDC in SQL Server 2008 relies on SQL Server Agent jobs.

Second, I'll create a new test database called CDCTest.

Third, I'll run a new system stored procedure that enables CDC on a database. I only need to run this one time.

Fourth, I'll create a very simple database table called TestEmployees. (Eventually I'll insert/update/delete rows, and will expect SQL Server to log all the changes).

Fifth, I need to tell SQL Server to log any insertions/updates/deletes for this table. So I'll call another new system stored procedure, to activate CDC for the table:

Note that SQL Server will create a new system table called cdc.dbo_TestEmployees_CT. This is the change tracking table for TestEmployees. (So as you can see, CDC maintains a separate log table for each "regular" database table).

Sixth, I'll insert and update and delete a few rows (to give the log file something to work with):

OK, step 7: let's look at the change tracking log for the TestEmployees table (the name of the change tracking log is derived from the base table name):

The CDC log takes some time to get accustomed to: the _$operation column isn't intuitive (2 is an Insert, 3 and 4 are Old Value/New Value combinations for updates, and a 1 is a deletion), and we need to join the Log Sequence Number column to the system table cdc.lsn_time_mapping in order to retrieve the date and time and transaction number of each operation.

Now for the $64,000 question – suppose I add a column to the original TestEmployees table – will the underlying CDC processes automatically adjust and log any new columns?

There's bad news and there's good news. The bad news is that CDC will not automatically adjust the changing tracking log table, if I add a column to the original table. (This is rather unfortunate, as new columns are a fact of life). The good news is that we can write a little bit of database code to get around this. This involves 4 steps:

First, I need to backup the change tracking log into a temporary database. (The reason is that step 2 will erase the current change tracking log).

Second, I'll add a column to the TestEmployees table (a column for each employee's department):

Third, I'll disable (and then re-enable) change tracking for the TestEmployees table. (This will delete the change tracking table and then recreate it with the new structure that utilizes the new Department column:

Fourth, I'll insert the temporary log back into the newly created log (but before that, I'll add the same new department to the temporary log, to make the insert back into the new log clean). And then after that, I'll drop the temporary log.

Finally, I'll update the department on one of the existing rows, I'll insert a new row, and then I'll read the change tracking table again (which now contains a reference to the new department column).

I hope you enjoyed this walkthrough on CDC in SQL Server 2008. Again, in both the SetFocus SQL Server Master's Program and Business Intelligence Master's Program, I cover CDC in greater detail.