Tag Archives: Levels

Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

image thumb 3 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

The Pivot Pictured Above Acts as if We’ve Swapped Out Fields on Rows – in Response to a Slicer Click!

First off…my first post! Being one of the newest (and youngest) members of the PowerPivotPro family has been very exciting so far. As a way of introducing myself, I’d like to share a creative solution to a problem I’m sure many of you have encountered when building a report or dashboard.

Typical Client Report Requests:

  • Simple but complex
  • High-level yet detailed
  • Compact yet containing everything…

Quite understandably, clients love to channel their inner Marie Antoinette by basically asking to have their cake and eat it too. I actually relish those scenarios, they allow me to flex my “think outside the box” muscles!  And hey, it’s great to be working with a toolset that truly CAN accommodate the special demands posed by real-world situations.

Well one such scenario had a customer wanting a summary Pivot Table with about five columns (fields) worth of details in it. No problem, done and done! The problem we were encountering however…was that 90% of the real-estate space on our reporting page had already been used for other visuals, none of which this client wanted eliminated or reduced to make room. So I’m left with the predicament of figuring out how to fit all this data onto this dashboard…Tetris mode engage!

Unfortunately despite my best efforts to rearrange the dashboard (accompanied by my 80’s Rush Mixtape) I simply could not find any way to display a wide Pivot Table on this dashboard. So I circled back to the drawing board and asked myself what variables I could manipulate to achieve the desired outcome.

I realized that I had an assumption that the PivotTable had to be fixed, meaning that it always has to show all levels of the data. However I LOVE to design visuals for clients that are dynamic, only showing the relevant data to them (often based on slicer selections). So I politely asked my previous assumption to leave and invited over my good friend paradigm shift. After some long conversations and extensive Google searches I actually ran across a PowerPivotPro Blog Post written by Rob that inspired my eventual solution.

Discovering this post almost felt like a relay race and I was being passed the baton to cross the finish line. Using the idea from this post that a slicer could change the axis of a chart, I realized the same would work in a PivotTable. All five columns in my table were part of a hierarchy…so why not use this technique to display a single column that would DYNAMICALLY switch between any of the five levels of this hierarchy based on slicer selections. I would now be able to create a table that is both compact and would display all the data the client needed.

Time to break out the cake forks!

Now for the fun part as I share this recipe for success (that was the last cake joke I promise). The general idea will be to create a hierarchy in the data model, and then reference those in an Excel set to be used in my Pivot Table. I’ll be using tables from the publicly available Northwind DW data set for this example.

Download Completed Example Workbook

X

Get Your Files

FIRST, create a Customer Geography Hierarchy in the data model on the DimCustomer Table.

Hierarchy in the Data Model table:

Customer Geo Hierarchy with box thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

SECOND, create a new DAX Measure called “Distinct Count of Country.” This will be used in our set to indicate whether or not a selection was made on our country Slicer.

=DISTINCTCOUNT( DimCustomer[Country] )

Now some of you technically savvy readers may be thinking “why didn’t he use the DAX Function HASONEVALUE?” I’ll explain more on this later when I explain how to write the set using MDX.

THIRD, create a new set for your pivot table referencing our recently created Hierarchy and DAX Measure. Note that the only way to access your sets is through a conditional ribbon that is displayed only when a cell selection is on a Pivot Table.

Opening the Set Manager window:

Sets with boxes thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

Creating a new set using MDX:

Set Manager with boxes thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

Writing the MDX Code:

MDX with boxes thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

This MDX query works by utilizing an IIF statement, which operates the same was as in DAX or Excel. It checks to see if our (Distinct Count of Country) DAX Measure is greater than 1 (indicating no slicer selection). If TRUE it returns the Country column from our hierarchy, if FALSE (slicer selection has been made) then it returns the City column. It’s important to note that I must reference the columns in the hierarchy, if you were to put just the column names in this query it would not run. It’s also important that the “Recalculate set with every update” box is checked, this makes sure the MDX statement is calculated every time someone uses a slicer, otherwise it’ll appear like the set isn’t working.

Keen observers take note! Here’s where I explain WHY I used a DISTINCTCOUNT rather than HASONEVALUE in my DAX Measure. Let’s say a client would like to multi-select countries in the slicer and still have it display the City column on rows in the Pivot Table. If I were to use HASONEVALUE in my DAX Measure I would only switch to the city column when a single value was selected.

The way I’ve designed it we can change the value in the MDX query from 1 to any number we’d like (E.g. 2, 3, etc.) which gives us the flexibility to allow multiple slicer selections and still have it switch to the City column.

“Clever Girl…”

Clever Girl thumb Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

I’m not actually sure if I’m supposed to be the hunter or dinosaur in this analogy from Jurassic Park…but either way I felt clever for that last step.

FOURTH, we can now use our newly created set in a Pivot Table. You’ll notice that a new folder called Sets is now nested in our DimCustomer Table.

Placing the Customer Geo set on rows in our Pivot Table:

PivotTable with boxes thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

Making a slicer selection to observe the dynamic switch from Country to City. Pretty cool!

PivotTable with slicer selection AND Boxes thumb 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

My client’s reaction could be summed up in a single word spoken by the immortal Keanu Reeves…

”Whoa…”

Now some of you may have noticed that I have a Total Sales value at the top of my Pivot Table. Now’s my chance to point out one unfortunate drawback of using sets on rows, it eliminates the totals row at the bottom of the Pivot Table. All is not lost though my friends, for every every problem a solution can always be found! In this case I created an artificial “Total’s row” at the top of the Pivot Table. I did this using the tried and true CUBEVALUE function to call the measure I’m already using in my PivotTable. NOTE that you need to make sure you connect all slicers (via the slicer_name) in the cube string for them to slice the CUBEVALUE as well. Finally, just a bit of dash formatting and some elbow grease and we have ourselves a Totals row!

CUBEVALUE Formula used in the cell for totals:

Totals Row with box thumb Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

There you have it, your very own Mighty Morphing Pivot Table. wlEmoticon smile 1 Mighty Morphing Pivot Tables or: How I learned to automatically change hierarchy levels on rows

Let’s block ads! (Why?)

PowerPivotPro

B2B CRM maturity levels

Deploying a B2B CRM system is just like launching a spaceship into the outer space. While advancing, you will need ever more resources to break from the gravitational forces and get to the point of maximum impact from your activities. Here, we’ll look at 4 stages of the CRM ascent to define where a B2B enterprise can take this ‘spaceship’ from its current coordinates.

crm maturity levels B2B CRM maturity levels

Troposphere: customer bookkeeping

The voyage has just begun, and all you have is your B2B CRM software that was deployed enterprise-wide. You use it to keep a nice database of your customers, to view the history of your communication (retroactively), and to register your outbound sales activities that are, however, not aligned with or measured against your specified sales objectives. When needed, you can even discover old contacts, such as partners and advertisers, or track organizational connections between your leads.

At this stage, having a CRM solution is frequently confused with having an actionable and measurable CRM strategy in its own right. The system is still focused on the past and the present of your customer relationships with no data-driven foresights into the future.

Sadly, the majority of companies get stuck exactly at this maturity (or rather immaturity) level in their CRM journey.

Mesosphere: communication planning & management

You’ve made a big leap in your approach to B2B CRM. Now, you make your first steps towards forward-thinking and a clearly defined CRM strategy, which requires more administrative resources.

This stage sees the low-key beginnings of analytics that you may use in planning and managing communication to nurture and keep up with your leads and customers. You activate handy CRM alerts and notifications to assist you along the way and probably tweak your system to provide a consolidated view of all the contact interactions.

Termosphere: step-by-step relationship development

At this stage, you learn to develop your customer relationships, not just passively respond to them. This sea change in your attitude to B2B CRM implies that you start stimulating customer conversion not only through lead nurturing but through assigning individual milestones for targeted leads and customers (personal meetings, pilot projects, etc.) for your sales reps to achieve in order to encourage positive movement through the sales pipeline.

This approach should involve integrated efforts across your organization and can probably result in introducing sales productivity goals to effectively manage step-by-step relationship development.

Outer space: strategic relationship management

In the world of CRM, the outer space feels extremely lonely – only a fraction (less than 3% according to our estimates) of all companies that adopt the technology make it to this stage. But those that do surely know how to multiply sales to strategic customers.

This particular stage contains no lead-focused activities, as strategic relationship management is all about your most valuable (including prospective value) customers. At this maturity level, you are likely to measure the current and future value of customers to determine the amount of resources you are ready to invest in retaining strategic accounts. This stage requires leveraging your upselling and cross-selling potential to win a bigger share of your customers’ budgets in the limited market by becoming their preferred supplier. Imagine, one of your top customers considers a dedicated delivery center on another continent. This is an opportunity for you, but how do you prioritize between dozens of such opportunities? And how do you know these opportunities are still up a month later?

At this stage, CRM becomes part of a company’s mindset, and a respectively customized B2B CRM solutionturns into a key tool that enables advanced and truly impactful campaigns to retain top clients.

Conclusion

The upward movement is always a matter of resources and determination, but the CRM logic is simple – the maximum impact can only be achieved at the highest stages of your CRM evolution. Any investments that don’t result in bringing your enterprise up at least to the second maturity level are highly questionable, whereas each next stage has a potentially greater capacity to bring higher ROI from your sales activities and optimize revenues from the customer base. To learn more about profit generation with a B2B CRM, read our previous blog post.

by ScienceSoft

Let’s block ads! (Why?)

CRM Software Blog

Echte Liebe: Pushing Data Management to New Levels for Individualized Customer Communication

June 14, 2016 | MUNICH

Full-service agency streamlines digital communications with the Data Management Platform from Teradata Marketing Applications

Echte Liebe, a full-service digital media agency based in Germany, has selected the Data Management Platform (DMP) from Teradata Marketing Applications, a division* of Teradata Corporation (NYSE: TDC), to help its clients connect all relevant data from multiple sources and channels and then leverage it in near-real-time for individualized marketing campaigns.

The variety of paid, earned and owned channels available to companies to reach and interact with their customers has increased significantly in recent years, as has the volume of data. In today’s non-stop digital environment, being able to track the entire customer journey by combining and analyzing online and offline data has become a make-or-break proposition for marketers implementing individualized customer campaigns. The Teradata DMP enables such capabilities, which influenced Echte Liebe’s decision to use this technology moving forward.

“Our clients require efficient and personalized campaigns, ranging from individualized banner advertising to building lookalike audiences and applying proven campaign tactics. For that, we needed the right technological foundation,” says Siamac Alexander Rahnavard, CEO of Echte Liebe. “We selected Teradata Marketing Applications over two other vendors because they treat our relationship as a partnership, and their mature technology demonstrates their leadership experience in leveraging data for digital marketing.”

“Finding your way through the maze of technological solutions for data management is easier said than done,” explains Dirk Radetzki, managing director DACH, Marketing Applications at Teradata. “If data warehouses can be viewed as the ‘long-term’ memory of a company, then data management platforms are its ‘short-term’ memory. Ideal for handling important information such as preferences and loyalty, these platforms enable individualized real-time customer communications. Using Teradata’s DMP, marketing managers can target their messages not only to known users, but also to unknown users who do not wish to reveal their personal details.”

About Teradata

Teradata (NYSE: TDC) helps companies get more value from data than any other company. Teradata’s leading portfolio of big data analytic solutions, integrated marketing applications, and services can help organizations gain a sustainable competitive advantage with data. Visit teradata.com.

Teradata and the Teradata logo are trademarks or registered trademarks of Teradata Corporation and/or its affiliates in the U.S. and worldwide.

Share

Let’s block ads! (Why?)

Teradata United States

3 Levels of Data Analysis to Revitalize Your Automated Email Programs in 2016

I’ve broken the process down into a three-pronged approach:

  1. Macro-Categorical
  2. Individual Program
  3. Individual Email/Subject Line

Here are examples and definitions of each of the stages:

1. Macro-categorical

Here you categorize your collection of email programs into broad, meaningful categories, such as:

  1. DemandGen
  2. Top of Funnel
  3. Bottom of Funnel

2. Individual programs

Within categories, look at each program. Pay particular attention to the amalgamation of steps and email templates for each. These should be complete, discrete programs, such as:

  1. Anonymous Website Visitors
  2. Top of Funnel Program – USA
  3. Middle of Funnel Program – EMEA

3. Individual email/subject line

Within each program, analyze each individual email and subject line for performance, such as:

  1. DemandGen – 2015 Happy New Year Email
  2. Top of Funnel  – Invitation to View New Video
  3. Customer  – All About the New Feature

Categorical analysis

Hopefully, you’re already using macro organization for your automated programs…that is, you will have already organized your email programs into broad general ideas/buckets.

For example, categories such as Top of Funnel, Middle of Funnel, and Bottom of Funnel sort programs by buyer stage. Categories such as EMEA and ROW sort by geography. Website, Webinar, and DemandGen are also perfect examples of macro-categorical buckets. These broad categories are perfect for this first stage analysis. This is important because not only will you want to see how multiple programs are performing in a single category, but this will give you a great birds-eye view of the bulk of your campaigns.

Programmatical analysis

Next up is the program level analysis. Each program is a combination of email templates, steps, and other programmable actions that make up a program. What we want to investigate at this level are how the different programs within one macro-category are performing. If there are programs that are similar, why is one performing better than others? Additionally, are there any ways for us to condense and consolidate our programs down to a smaller number?

If I were to give my humble opinion as to which of the three stages is the most difficult to assess, I would say that the program level is the most difficult. This is because the success of the program is contingent on the multitude of email templates contained within it. The only way to really look at the success of a program level stage is to either look at a program’s numbers vs another program in the same category (regardless of any other variables), or to examine the programs by what went wrong with individual email templates/subject lines. There could be several rotten apples in certain programs that are affecting the entire operation.

Individual email analysis

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Act-On Marketing Blog

Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask

Every time you access a relational database to make a query, you have an important decision to make: What is the appropriate isolation level for your query? If you get this wrong, the consequences can be serious. Deadlocks, Dirty reads, Non-repeatable reads, or poor performance. We’re in luck, because Robert Sheldon  once more answers those questions that are awkward to ask in public..

  1. “How do I set the transaction isolation level when connecting to a SQL Server database?”
  2. “I still don’t get all this dirty reads and phantom reads business. What do they have to do with concurrency problems?”
  3. “What is the Read Committed Snapshot isolation level and how do I enable it?”
  4. “How do I set the default transaction isolation level on the current database?”
  5. “What transaction isolation levels can I specify on a memory-optimized table?”
  6. “How do I set the default transaction isolation level for write operations?”
  7. “How do I enable the snapshot isolation level on my transactions?”
  8. “The Serializable and Snapshot isolation levels appear to achieve the same results. What are the differences between them?”
  9. “How do I verify which snapshot-related database options are enabled on my database?”
  10. “How do I verify the transaction isolation levels that an application is using when connecting to the database?”
  11. “What’s the difference between using the NOLOCK table hint and the Read Uncommitted transaction level?”
  12. “Why would I ever use the Read Uncommitted isolation level?”

“How do I set the transaction isolation level when connecting to a SQL Server database?”

To set the isolation level, you can issue a SET TRANSACTIONISOLATIONLEVEL statement after you connect to SQL Server. The isolation level will apply to the rest of that session, unless you explicitly change the level again.

Within the SET TRANSACTIONISOLATIONLEVEL statement, you must specify one of the following five isolation levels:

  • READ UNCOMMITTED: A query in the current transaction can read data modified within another transaction but not yet committed. The database engine does not issue shared locks when Read Uncommitted is specified, making this the least restrictive of the isolation levels. As a result, it’s possible that a statement will read rows that have been inserted, updated or deleted, but never committed to the database, a condition known as dirty reads. It’s also possible for data to be modified by another transaction between issuing statements within the current transaction, resulting in nonrepeatable reads or phantom reads.
  • READ COMMITTED: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so nonrepeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.
  • REPEATABLE READ: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating nonrepeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.
  • SERIALIZABLE: A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, nonrepeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.
  • SNAPSHOT: A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, nonrepeatable reads and phantom reads. However, it is susceptible to concurrent update errors. (not  ANSI/ISO SQL standard)

There is much more to the isolation levels than what I’ve covered here, and the differences between them can be quite subtle, so be sure to refer to SQL Server documentation for more information. In the meantime, let’s look at how to actually specify the isolation level after making a connection. As already noted, you must use the SETTRANSACTIONISOLATIONLEVEL statement, as shown in the following T-SQL statement:

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

SELECT* FROM EmployeeInfo

WHERE EmpID = 1;

Notice that we simply specify the isolation level in our SETTRANSACTIONISOLATIONLEVEL statement, in this case, Read Uncommitted. We can then run our query under that isolation level. Afterwards, we can return our session to the default level by issuing the following statement:

SETTRANSACTION ISOLATION LEVEL READ COMMITTED;

 That’s all there is to setting the isolation level. The trick is in understanding how the isolation levels work and the implications of each one. Tread carefully when changing the isolation level. You can easily impact performance if you use a level too restrictive, or create application problems by implementing an isolation level that leads to transactions prone to concurrency issues.

“I still don’t get all this dirty reads and phantom reads business. What do they have to do with concurrency problems?”

In the previous question, we touched upon the issue of dirty reads, nonrepeatable reads and phantom reads and how they’re tied to transaction isolation levels. Perhaps a different take on each of these would be helpful:

  • Dirty read: Session 1 begins a transaction and modifies data. Session 2 reads the modified data before Session 1 commits the transaction. Session 2 is reading data that exists only in theory because the Session 1 transaction might be rolled back. If that occurs, the data read by Session 2 is no longer valid.
  • Nonrepeatable read: Session 1 begins a transaction and retrieves a row of data from a table. Session 2 updates that row. Session 1 tries to retrieve the row once more, still within the original transaction, but receives different results because of the Session 2 update.
  • Phantom read: Session 1 begins a transaction and retrieves several rows of data from a table. Session 2 inserts a row into the same table, and the insert coincides with the search criteria of the Session 1 statement. When Session 1 reruns the query, the new row added by Session 2 is now included in the results.

Notice the similarities between nonrepeatable reads and phantom reads. In fact, you can think of a phantom read as a variation of the nonrepeatable read. The problem with these definitions, however, is that it can be difficult to get agreement on any one being the “correct” one, particularly when it comes to phantom reads and nonrepeatable reads. Even within Microsoft documentation the definitions are not consistent. However, the scenarios described here should at least give you some sense of the differences between these concurrency problems.

The important point to remember is that the possibility of running into any of these issues depends on the isolation levels you’re using when you run your queries. The following table shows the concurrency issues that each isolation level is susceptible to:

Isolation level

Dirty read

Nonrepeatable read

Phantom read

Read uncommitted

Read committed

Repeatable read

Serializable

Snapshot

 Let’s look at a few examples to better illustrate how the isolation levels can help prevent concurrency problems. The examples are based on the test EmployeeInfo table, which includes the EmpID, FirstName, and LastName columns. However, you can use whatever table you like, as long as you can update data in it.

In the first example, we simulate a dirty read against the EmployeeInfo table. (If you want to try this out, you’ll need to establish two different connections to your database.) Let’s start with Session 1. First, we begin a transaction and run an UPDATE statement, as shown in the following T-SQL:

– session 1

BEGINTRANSACTION;

 

UPDATE EmployeeInfo

SET FirstName = ‘Frank’

WHERE EmpID = 1;

 

WAITFORDELAY ’00:00:05′ 

 

ROLLBACKTRANSACTION;

Notice that the transaction includes a WAITFOR DELAY statement, with five seconds specified. This is included to give us time to run our statements in the second session. You can take this approach, adjusting the seconds as necessary, or simply run the BEGINTRANSACTION and UPDATE statements and stop there. You can then run the statements in Session 2, and then return to Session 1 to run the ROLLBACK statement. The goal is to make sure we run the statements in the second session in between the UPDATE and ROLLBACK statements in the first session.

When we hit the delay, we can run the following the following T-SQL statements in Session 2:

–session 2

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

We start by specifying that the Read Uncommitted isolation level be used. We then run the SELECT statement, which immediately returns the value Frank, the new value inserted into the table in Session 1. However, if we run the SELECT statement a second time, after the Session 1 transaction has been rolled back, we’ll receive the value Ken, the original value in the table. The returned value Frank in Session 2 is an example of a dirty read.

Now let’s repeat our experiment, only this time with a different isolation level. In Session 1, we again start our transaction and do our update:

– session 1

BEGINTRANSACTION;

 

UPDATE EmployeeInfo

SET FirstName = ‘Frank’

WHERE EmpID = 1;

 

WAITFORDELAY ’00:00:05′ 

 

ROLLBACKTRANSACTION;

This time in Session 2, we set the transaction level to Read Committed:

–session 2

SETTRANSACTION ISOLATION LEVEL READ COMMITTED;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

When we run the SELECT statement, it waits until the Session 1 transaction has been rolled back and then returns the value Ken. As you can see, no dirty read. But another problem is lurking: the nonrepeatable read.

To demonstrate the nonrepeatable read, we take a different approach. In Session 1, we set the isolation level to Read Committed (which is the default), start a transaction, and retrieve a row from the EmployeeInfo table:

–session 1

SETTRANSACTION ISOLATION LEVEL READ COMMITTED;

 

BEGINTRANSACTION;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

WAITFORDELAY ’00:00:05′ 

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

ROLLBACKTRANSACTION;

 During the delay, we run an UPDATE statement in Session 2:

–session 2

UPDATE EmployeeInfo

SET FirstName = ‘Frank’

WHERE EmpID = 1;

This time around, the first SELECT statement in Session 1 returns the value Ken, the original value. However, the second time the SELECT statement runs—after the five-second delay and the Session 2 update, the statement returns the value Frank, thus demonstrating a nonrepeatable read.

Let’s do our experiment again, only this time specify the Repeatable Read isolation level in Session 1:

–session 1

SETTRANSACTION ISOLATION LEVEL REPEATABLE READ;

 

BEGINTRANSACTION;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

WAITFORDELAY ’00:00:05′ 

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

ROLLBACKTRANSACTION;

In Session 2, during the Session 1 delay, we run the same UPDATE statement as in the previous example:

–session 2

UPDATE EmployeeInfo

SET FirstName = ‘Ken’

WHERE EmpID = 1;

In this case, both executions of the SELECT statement in Session 1 return the value Frank, the last value to be inserted (from the preceding example). The UPDATE statement will not run until the Session 1 transaction has been rolled back. If we were to then run the SELECT statement again, after the update, it would return Ken, as expected.

We could create more examples to demonstrate various scenarios, but you get the picture. The isolation level determines which read concurrency problems can occur. It’s up to you to decide how strictly you want to control access. Just remember, the stricter your isolation policy, the longer the database engine will lock your data and the greater the impact on performance.

“What is the Read Committed Snapshot isolation level and how do I enable it?”

In some SQL Server documentation, you’ll see references to the Read Committed Snapshot isolation level as a sixth type. What this actually refers to is the READ_COMMITTED_SNAPSHOT database option. When a database is first created, this option is set to OFF, so it has no impact on the database or the existing isolation levels. When set to ON, however, the option modifies the Read Committed isolation level to behave more like the Snapshot isolation level, thus the label Read Committed Snapshot.

By default, when the Read Committed transaction level is enabled, the database uses shared locks to prevent other transactions from modifying rows when the current transaction is reading those rows. At the same time, the locks block the current transaction from reading rows modified by other running transactions until those transactions have completed.

When the READ_COMMITTED_SNAPSHOT database option is set to ON, the database engine uses row versioning for Read Committed to ensure transactionally consistency, rather than using shared locks. The engine stores the row versions in the tempdb system database as long as necessary to facilitate this process. In this way, the current read operation does not block other transactions, or vice versa.

To enable the READ_COMMITTED_SNAPSHOT option on a database, you can run an ALTER DATABASE statement similar to the one shown in the following example:

ALTERDATABASE AdventureWorks2014

SETREAD_COMMITTED_SNAPSHOT ON;

Notice that we need only set the option to ON. To disable the option, simply set it to OFF:

ALTERDATABASE AdventureWorks2014

SETREAD_COMMITTED_SNAPSHOT OFF;

The READ_COMMITTED_SNAPSHOT option can help avoid locking contention and subsequently improve query performance by minimizing wait times, but it does not eliminate the risks of non-repeatable reads and phantom reads. For that, you need the Snapshot isolation level. The challenge with Snapshot, however, is that it can result in update conflict errors. With Read Committed Snapshot, the database engine will block a connection until the active connection releases its locks, and then it will update the data.

Also be aware that enabling the READ_COMMITTED_SNAPSHOT option—and the subsequent row versioning that goes with it—incurs additional overhead in maintaining the versioned rows in tempdb, as is the case with the Snapshot isolation level. You must also ensure that tempdb has plenty of room to accommodate all the versioned data.

“How do I set the default transaction isolation level on the current database?”

You can’t. The default isolation level for all SQL Server databases is Read Committed, and your only option is to set the isolation level within a session, if you want to use a level other than the default. Workarounds have been suggested, such as creating a logon trigger that sets the isolation level, but these approaches tend not to deliver the expected results.

That said, SQL Server does support several database options that can impact the isolation levels: READ_COMMITTED_SNAPSHOT, ALLOW_SNAPSHOT_ISOLATION, and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. All of these are disabled by default, but you can easily use T-SQL to turn them on, as shown in the following examples:

ALTERDATABASE AdventureWorks2014

SETREAD_COMMITTED_SNAPSHOT ON;

 

ALTERDATABASE AdventureWorks2014

SETALLOW_SNAPSHOT_ISOLATION ON;

 

ALTERDATABASE AdventureWorks2014

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

The READ_COMMITTED_SNAPSHOT option affects the behavior of the Read Committed isolation level, as previously mentioned, and the ALLOW_SNAPSHOT_ISOLATION option controls whether the Snapshot isolation level can be enabled during a session. When you set either of these options to ON, the database engine maintains row versions in the tempdb database to meet the requirements of transactions running under the Read Committed Snapshot or Snapshot isolation level.

The MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option is specific to memory-optimized tables, new in SQL Server 2014. When the option is enabled, the database engine uses Snapshot isolation for all interpreted T-SQL operations on memory-optimized tables, whether or not the isolation level is set explicitly at the session level. (Interpreted T-SQL refers to batches or stored procedures other than natively compiled procedures.)

You can disable any of these options by setting them to OFF, as shown in the following ALTER DATABASE statements:

ALTERDATABASE AdventureWorks2014

SETREAD_COMMITTED_SNAPSHOT OFF;

 

ALTERDATABASE AdventureWorks2014

SETALLOW_SNAPSHOT_ISOLATION OFF;

 

ALTERDATABASE AdventureWorks2014

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT OFF;

Other than these options, there’s not much else you can do about the default behavior. You’re stuck with Read Committed whether or not row versioning is used. To get around this, you must use the SETTRANSACTIONISOLATIONLEVEL statement at the session level, or use a table hint at the statement level, if you want your change to apply only to that statement. For example, the following SELECT statement specifies the TABLOCK table hint:

SELECT EmpID, FirstName, LastName

FROM EmployeeInfo WITH(TABLOCK)

WHERE EmpID > 99

ORDERBY LastName;

The TABLOCK table hint directs the database engine to lock the data at the table level, rather than the row or page level. The table hint will apply only to the table targeted in this statement and will not impact the rest of the session, as would a SETTRANSACTIONISOLATIONLEVEL statement.

You can also use table hints for a specific table in a join. For example, the following SELECT statement specifies the SERIALIZABLE table hint for the SalesOrderDetail table:

SELECT d.ProductID, d.OrderQty, h.OrderDate

FROM Sales.SalesOrderHeader h INNER JOIN

  Sales.SalesOrderDetail d WITH(SERIALIZABLE)

  ON h.SalesOrderID = d.SalesOrderID

ORDERBY d.ProductID, h.OrderDate DESC;

This tells the database engine to apply the Serializable isolation level only to the SalesOrderDetail table for this query. The Read Committed isolation level (the default) still applies to the SalesOrderHeader table. As a result, a shared lock will be held on the SalesOrderDetail table for the entire transaction, rather than just the initial table read.

“What transaction isolation levels can I specify on a memory-optimized table?”

Memory-optimized tables are part of the new In-Memory OLTP technology introduced in SQL Server 2014 to help improve application performance. Memory-optimized tables support the Read Committed, Repeatable Read, Serializable, and Snapshot isolation levels, with Read Committed the default level, as with disk-based tables. Notice that the Read Uncommitted isolation level is not included.

All isolation levels supported for memory-optimized tables are based on row versioning. The tables rely on multiple row versions to guarantee isolation, providing optimistic concurrency control. As a result, the database engine does not issue shared data locks. Row versions are not stored in tempdb like the Snapshot isolation level for disk-based tables, but are instead part of the memory-optimized table itself.

Isolation levels used for memory-optimized tables provide the same guarantees as those used for disk-based tables; only the processes used to get there are different. Because the isolation levels implement row versioning instead of shared locks, a write conflict will occur when two transactions attempt to update the same row concurrently, rather than one transaction waiting for the other to complete before attempting the update.

If you’re working with memory-optimized tables, there are a number of guidelines you should follow when specifying an isolation level. For example, you should avoid long-running transactions and implement retry logic in your apps to deal with conflicts, validation errors, and commit-dependency failures. You can find details about the various guidelines in the MSDN article “Guidelines for Transaction Isolation Levels with Memory-Optimized Tables.”

Keep in mind, however, that SQL Server’s in-memory technologies are still in their infancy. Consequently, the available information about the technology is often confusing and inconsistent. If you plan to implement in-memory tables, be sure to test your operations thoroughly and compare the behavior for different isolation levels. No doubt we’ll see changes in the technology and guidelines going forward.

“How do I set the default transaction isolation level for write operations?”

It’s best to think of the isolation levels in terms of read operations, rather than write operations. The database engine issues exclusive locks on all write operations and holds the locks until the transactions have completed, regardless of which isolation level is used. This ensures that your write operations are always protected. Concurrency issues such as dirty reads and phantom reads, as their names suggest, apply to read operations, not write. The database engine governs the locking behavior of all write operations, and you cannot change that behavior at the database level.

Even for read operations, you have few options for changing the default behavior, as noted earlier. When a transaction initiates a read option, the database engine uses either shared locks or row versions (to support Snapshot isolation) in order to handle concurrency issues. The way in which the database engine issues shared locks depends on the isolation level. For example, in a transaction running at the Read Uncommitted isolation level, the database engine issues no shared locks. However, if the transaction is running at the Serializable level, the database engine will lock the selected data throughout the transaction.

Multiple shared locks can exist on the selected data at any one time, but only one exclusive lock can exist on data at a time. Exclusive locks are not compatible with shared locks. Once a transaction has taken an exclusive lock, the database engine cannot issue a shared lock on that data until the transaction has completed.

There are a number of other rules that govern locking, but the point is, you cannot change the database’s default behavior when it comes to write operations. You can, however, override that behavior by using table hints, just like you can use table locks for read operations. For example, the following UPDATE statement specifies the TABLOCKX table hint:

UPDATE EmployeeInfo WITH(TABLOCKX)

SET FirstName = ‘Ken’

WHERE EmpID = 1;

The TABLOCKX table hint tells the database engine to issue an exclusive lock on the entire table, rather than at the row or page level, as would normally be the case for this type of statement.

“How do I enable the snapshot isolation level on my transactions?”

As noted previously, the Snapshot isolation level relies on row versions being maintained in the tempdb database to avoid locking contention issues. This approach incurs overhead to maintain those versioned rows. For this reason, you must explicitly enable snapshot isolation at the database level before you can implement it at the session level.

To enable snapshot isolation, you can run an ALTER DATABASE statement to set the ALLOW_SNAPSHOT_ISOLATION option to ON, as shown in the following example:

ALTERDATABASE AdventureWorks2014

SETALLOW_SNAPSHOT_ISOLATION ON;

When you enable the ALLOW_SNAPSHOT_ISOLATION option, you activate a mechanism in your database for storing the versioned rows in tempdb. You can then set the Snapshot isolation level within your session for specific transactions:

SETTRANSACTION ISOLATION LEVEL SNAPSHOT;

 

BEGINTRANSACTION;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

COMMITTRANSACTION;

A transaction using the Snapshot isolation level sees the data as it existed at the beginning of the transaction. As a result, if you try to update data that has changed during the course of the transaction, the database engine rolls back the transaction and raises an error.

“The Serializable and Snapshot isolation levels appear to achieve the same results. What are the differences between them?”

Although the Serializable and Snapshot isolation levels work differently, they both protect against dirty reads, nonrepeatable reads, and phantom reads. The Serializable isolation level relies on shared data locks to address concurrency issues, and the Snapshot isolation level relies on row versioning. (It’s true that all isolation levels use row versioning for memory-optimized tables, but for now, let’s stick with disk-based tables.)

The key to understanding the differences between the two isolation levels is to look at what happens when data gets updated. With the Serializable isolation level, the database engines issues a shared lock on the accessed data and holds that lock until the transaction has completed. As a result, no other transactions can modify the data as long as the current transaction is active.

With the Snapshot isolation level, the database engine does not lock the data. Other transactions can update the data at the same time as the current transaction, resulting in update conflicts. Let’s try another little experiment to demonstrate how this works. In Session 1, we set the connection to the Serializable isolation level, begin a transaction and issue several statements:

–session 1

SETTRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

BEGINTRANSACTION;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

WAITFORDELAY ’00:00:05′ 

 

UPDATE EmployeeInfo

SET FirstName = ‘Roger’

WHERE EmpID = 1;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

COMMITTRANSACTION;

 

As in our previous examples, we’re using a WAITFOR DELAY statement to give us time to run the second session. Before the delay, we retrieve the data (Session 1). After the delay, we update the data and retrieve it once again. During the delay, we run our second session:

–session 2

UPDATE EmployeeInfo

SET FirstName = ‘Harold’

WHERE EmpID = 1;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

In Session 2, we’re simply updating the same row as in Session 1 and then retrieving the results. Because we’re using the Serializable isolation level in Session 1, the statements in Session 2 will not run until the Session 1 transaction has completed. Consequently, the first SELECT statement in Session 1 returns Ken, the original value, and the second SELECT statement returns the value Roger. The SELECT statement in Session 2 then returns the value Harold.

If we repeat the experiment, but instead specify the Snapshot isolation level in Session 1, we will receive different results. The first SELECT statement in Session 1 will still return the value Ken (assuming we reset that value to the original), and the SELECT statement in Session 2 will return the value Harold. However, when we run the UPDATE statement in Session 1, we receive the following error:

Msg 3960, Level 16, State 6, Line 297

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.EmployeeInfo’ directly or indirectly in database ‘AdventureWorks2014′ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

As you can see, the Snapshot isolation level doesn’t handle concurrent updates as gracefully as the Serializable isolation level. However, Snapshot can offer improved query performance because the queries don’t have to wait for locks to be released before being able to access the data. If trying to decide which of the two to use, you’ll have to weigh query performance against tempdb overhead, as well as take into consideration the type of queries you’ll be running against your tables.

“How do I verify which snapshot-related database options are enabled on my database?”

You can verify the snapshot-related database options by using the sys.databases catalog view to retrieve the information on a specific database. For example, if you want to know the setting for the ALLOW_SNAPSHOT_ISOLATION option, you would query the snapshot_isolation_state_desc column in the sys.databases view, as shown in the following SELECT statement:

SELECT snapshot_isolation_state_desc

FROMsys.databases

WHERE name = ‘AdventureWorks2014′

The statement will return the value ON if the option is enabled or OFF if it’s disabled. You can just as easily retrieve the current setting for the READ_COMMITTED_SNAPSHOT option by querying the is_read_committed_snapshot_on column:

SELECT is_read_committed_snapshot_on

FROMsys.databases

WHERE name = ‘AdventureWorks2014′

In this case, the statement will return the value 1 if the option is enabled or 0 if it’s disabled. To retrieve the current setting of the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option, use the is_memory_optimized_elevate_to_snapshot_on column:

SELECT is_memory_optimized_elevate_to_snapshot_on

FROMsys.databases

WHERE name = ‘AdventureWorks2014′

Once again, the statement will return the value 1 if the option is enabled or 0 if it’s disabled.

“How do I verify the transaction isolation levels that an application is using when connecting to the database?”

You can use the sys.dm_exec_sessions dynamic management view (DMV) to verify a connection’s isolation level. For example, suppose you have an application that issues the following statements:

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

 

The application sets the isolation level to Read Uncommitted and then retrieves data from the EmployeeInfo table. You can run the following statement (within the same session) to return the isolation level:

SELECT transaction_isolation_level

FROMsys.dm_exec_sessions

WHERE session_id = @@SPID;

 

The SELECT statement retrieves the transaction_isolation_level column from the DMV. The statement also includes a WHERE clause that uses the @@SPID system variable to specify the current session ID.

In this case, the SELECT statement returns a value of 1. SQL Server uses the following values to represent the isolation levels available through the sys.dm_exec_sessions view:

  • 0 = Unspecified
  • 1 = Read Uncommitted
  • 2 = Read Committed
  • 3 = Repeatable
  • 4 = Serializable
  • 5 = Snapshot

If you can’t query the sys.dm_exec_sessions view within the session whose isolation level you want to verify, you’ll need to take a different approach to identify the application and its session. For example, the view supports the host_name, login_name, and program_name columns. Perhaps querying one of these will give you the information you need.

“What’s the difference between using the NOLOCK table hint and the Read Uncommitted transaction level?”

Both approaches tell the database engine not to issue shared locks when reading the requested data; however, the two approaches differ in scope, within the context of the current session. For example, suppose we issue the following T-SQL statements:

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

SELECT FirstName, LastName

FROM EmployeeInfo

WHERE EmpID = 1;

In this case, we’re setting the connection to the Read Uncommitted isolation level before running our statement. The entire session will use this isolation level until the session ends or we explicitly change the level. That means, for every table we access throughout the session, no shared locks are placed on any of the tables for our read operations. Consequently, our transactions are not protected from dirty reads as well as nonrepeatable or phantom reads.

However, we might decide that only one or two of our target tables require the “lock-less” state, in which case, we can use the NOLOCK table hint to limit that isolation level to the specific query, as shown in the following example:

SELECT FirstName, LastName

FROM EmployeeInfo WITH(NOLOCK)

WHERE EmpID = 1;

The NOLOCK table hint is the equivalent of the READUNCOMMITTED table hint. The database engine ignores the hint if used in the FROM clause of a DELETE or UPDATE statement.

“Why would I ever use the Read Uncommitted isolation level?”

When the Read Uncommitted isolation level is used, the database engine does not issue shared locks on the accessed data, which can lead to the types of concurrency issues described earlier. That said, the Read Uncommitted isolation level can be useful in certain situations, particularly if you’re trying to avoid heavy lock contention.

For example, if you’re pulling historical data from a data warehouse or reporting database, your queries might benefit from the minimal locking, especially those complex joins that can easily escalate to table locks. In such a scenario, the primary DML operations might be inserts only and often not part of the queries. Plus, you might also be able to schedule your data loads to occur at a time when the data is not being accessed for reporting and analytics.

Another way to look at this is that the Read Uncommitted isolation level can be handy if you’re querying data that is not likely to change or you’re aggregating large quantities of data that permit an acceptable margin for error. On the other hand, if the data is time-sensitive or requires drill-down precision, you should forego Read Uncommitted, especially when an application frequently writes against the data. Your typical OLTP operation, for example, is usually a lousy candidate for Read Uncommitted, except for simple queries and small tables with fairly static data.

Developers can also benefit from the Read Uncommitted isolation level because they can use it to help debug a transaction by allowing them to see what is happening within the transaction while it’s still running. In this case, the dirty read can provide line-by-line insight into a transaction, which can be especially useful for those overly complex stored procedures.

Despite the advantages in performance that Read Uncommitted can offer in certain situations, you should use this isolation level judiciously. Make sure you understand your data and your queries, and that you’re not using Read Uncommitted to cover up for other problems.

 

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Simple Talk RSS Feed