Apr 19

I am now, officially, a Supper Presenter!

I finally had an opportunity to present at SQL Supper event in London on the 18th of April 2017. I have thoroughly enjoyed the event and the pub session that followed. I even managed to learn a few things myself! Thank you very much, James and other organisers for presenting me with this opportunity to share the knowledge and experience with others and contribute to the community. Many thanks for people attending the event and particularly those who participated in the discussions.

The talk I have presented was on BI ALM with TFS 2017 (DevOps for MS BI using TFS2017), please let me know any feedback on the session by commenting to this post, that will be very much appreciated. If you have any questions or if you want any part of the scripts I have used in demo – please let me know.

Oct 16

Disk and memory-optimized tables in a single query: cross-container transactions

When you set up a database for  memory-optimized tables in SQL Server 2014 it is recommended that you enable SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT setting:

ALTER DATABASE IMOLTP_DB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

Memory x DiskThis setting is important when using transactions involving in-memory tables and disk-based tables, the so-called “cross-container transactions”. As you may know, SQL Server default transaction isolation level is normally READ COMMITTED. The above setting will elevate the isolation level for memory-optimized tables to SNAPSHOT for every cross-container transaction with lower isolation level (such as default, read committed). This setting is equivalent to including WITH(SNAPSHOT) hints to every DML operation involving memory-optimized table in cross-container transaction.

For more information http://msdn.microsoft.com/en-us/library/dn133175.aspx

If this setting is not specified, you may see the following message returned when attempting a cross-container transaction:

Msg 41368, Level 16, State 0, Line 31
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

So one way of dealing with this message is to use WITH (SNAPSHOT) hint next to every memory-optimised table involved in the DML statement, just as advised by the error message:

BEGIN TRANSACTION
UPDATE dt
SET data = mot.data
FROM MemoryOptimisedTable mot WITH (SNAPSHOT) 
   INNER JOIN DiskTable dt on mot.id = dt.id

DELETE FROM MemoryOptimisedTable WITH (SNAPSHOT)
COMMIT

However, if you expect to use cross-container transactions often, a more convenient way is to specify a database option SETMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

If SERIALIZABLE or REPEATABLE READ transaction isolation level is used, the error message is somewhat different:

Msg 41333, Level 16, State 1, Line 29
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Here, the isolation level is higher than SNAPSHOT, so the elevation setting is not going to help, and you must use WITH (SNAPSHOT) hints:

IF @@TRANCOUNT > 0 ROLLBACK

IF OBJECT_ID('dbo.MemoryOptimisedTable') IS NOT NULL DROP TABLE dbo.MemoryOptimisedTable

CREATE TABLE dbo.MemoryOptimisedTable (
id int NOT NULL PRIMARY KEY NONCLUSTERED,
im_data int) with (MEMORY_OPTIMIZED = ON)
IF OBJECT_ID('dbo.DiskTable') IS NOT NULL DROP TABLE dbo.DiskTable
CREATE TABLE dbo.DiskTable
(id int NOT NULL PRIMARY KEY CLUSTERED,
dt_data int)
GO
INSERT INTO MemoryOptimisedTable (id, im_data)
values (1,3876),(2,6576),(3,3345)
INSERT INTO DiskTable (id, dt_data)
values (1,45756),(2,1231),(3,3432)
GO
SELECT * FROM MemoryOptimisedTable mot INNER JOIN DiskTable dt on mot.id = dt.id
BEGIN TRANSACTION
UPDATE dt
SET dt_data = mot.im_data
FROM MemoryOptimisedTable mot INNER JOIN DiskTable dt on mot.id = dt.id
DELETE FROM MemoryOptimisedTable
COMMIT

Generic Previous Period MDX Calculation Measure

Image

I had recently had a requirement to create a Calculated Member or Calculation for a generic previous period measure. The user wanted to use the same calculation with a number of different date cube dimensions. For  example, one may want to see previous value of Internet Sales for Adventure works over OrderDate or for Delivery Date using the same calculated member for convenience and clarity. In addition, you want to display something intuitive and user-friendly for when the value is not available (you are looking at the first dimension level member). Brief googling came to no avail, so I decided to blog the solution I created, which I thought may come pretty handy for those not particularly hot on MDX.

The idea is to use coalesceEmpty MDX function:

WITH 
 MEMBER [Measures].[Previous Internet Sales Amount] AS
 coalesceEmpty(
 ([Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
 ([Delivery Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
'Not Avaiable'
              ), FORMAT_STRING='Currency'
SELECT 
 {[Measures].[Internet Sales Amount],
 [Measures].[Previous Internet Sales Amount]} ON COLUMNS,
{[Delivery Date].[Calendar].[Calendar Year]} ON ROWS
FROM [Adventure Works]

…which results in the following:

Previous Internet Sales over Delivery Date

Now, the same calculated member can be used with a different date dimension, for here’s an example with Order Date (aka “Date” in Adventure Works cube):

WITH 
 MEMBER [Measures].[Previous Internet Sales Amount] AS
 coalesceEmpty(
 ([Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),
 ([Delivery Date].[Calendar].PrevMember, [Measures].[Internet Sales Amount]),'Not Avaiable'
 )
, FORMAT_STRING='Currency'
SELECT 
 {[Measures].[Internet Sales Amount],
 [Measures].[Previous Internet Sales Amount]} ON COLUMNS,
{[Date].[Calendar].[Calendar Year]} ON ROWS
FROM [Adventure Works]

…with results in slightly different figures:

Using Order Date (Called "Date" in Adventure works)

Obviously this calculated member is very simple but the idea remains the same for any previous period calculation. Naturally it makes more sense to use it as a calculation rather than a session-scope member:

Using generic previous period MDX as a calculation

Hope this is useful.

If you would like an alternative solution, have a look at this one, by Alex Whittles from PurpleFrog. There are a few other ones, too, using IIF function or Utility dimension, but I did not like them as much.

Happy MDXing!