Neal Waterstreet

DAX vs MDX Smackdown!

by 2 Comments

Last Monday Damu Venkatesan and I presented DAX vs MDX Smackdown! to the Atlanta Microsoft BI group. I thought it went well, but could definitely use some tweaking. In fact, it may be worth it to overhaul the presentation and focus on DAX. Here are a few of my thoughts about the presentation:

  1. More needs to be added to establish the use of DAX. I didn’t do a good job with this and assumed everyone was familiar with the Tabular model.
  2. I showed all of the queries in SSMS, which confused some people. The resolution here is to build a PowerPivot report and show the result from there. Then I can take it into SSMS and show the end result in PowerPivot and finally Power View.
  3. I need to spend more time telling the story of each query. Some of this was due to technical issues, but I still need to improve here. Damu did a very good job with stories about the MDX queries.
  4. Make sure the entire environment is installed on my VM. I had multiple SSAS instances, but didn’t have Excel or SharePoint installed. This would have helped me to elaborate on several questions.

On the technical side there were issues too. My Lenovo had issues with the projector and I could only see on screen and not the laptop. This was also at a very low resolution, so all of my work setting up SSMS for readability was lost. I need to find a backup plan here and find out what other people are doing.

I’d also like to thank Teo Lachev for his help before, during and after the presentation.

All in all it went well. It is always easier to pick out what went wrong instead of what went well. I am looking forward to doing it again soon.

WITH MARK is your friend

by Leave a Comment

Using the WITH MARK clause in a transaction can save time and tears. Instead of restoring backups to a point in time, WITH MARK can be used to name transactions and can then be used to roll forward to the transaction itself.

Make sure the AdventureWorks database recovery model is set to FULL
[code language=”sql”]
USE master
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT
GO
[/code]
We need to take a FULL backup first, just in case it was not using the FULL recovery model.
[code language=”sql”]
BACKUP DATABASE AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.bak’
GO
[/code]

sshot-1

We can run some update statements against the Person.Contact table. We can start off by changing the EmailPromotion to 0 for ContactID 2.
[code language=”sql”]
USE AdventureWorks
GO
BEGIN TRANSACTION EmailPromotion WITH MARK
UPDATE AdventureWorks.Person.Contact SET EmailPromotion = 0 WHERE ContactID = 2
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
sshot-2

 

Update all of the phone numbers to the same number by leaving out a WHERE clause. This is a favorite mistake of mine.
[code language=”sql”]
BEGIN TRANSACTION UpdatePhone WITH MARK
UPDATE AdventureWorks.Person.Contact SET Phone = ‘123-456-7890’
–WHERE ContactID = 4
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]
OOPS.

sshot-3

Finally change the last name from ‘Ackerman’ to ‘Smith’.
[code language=”sql”]
BEGIN TRANSACTION LastName WITH MARK
UPDATE AdventureWorks.Person.Contact SET LastName = ‘Smith’ WHERE ContactID = 5
COMMIT TRANSACTION
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-4

Go ahead and back up the transaction log.
[code language=”sql”]
USE master
GO
BACKUP LOG AdventureWorks TO DISK = N’D:\Backups\AdventureWorks_now.trn’
GO
[/code]
First, we’ll use STOPBEFOREMARK to restore to the point prior to the EmailPromotion transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG [AdventureWorks] FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘EmailPromotion’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-5

Now we can use STOPBEFOREMARK to restore to the point prior to the UPDATEPHONE transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘UpdatePhone’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-6

Now we can use STOPBEFOREMARK to restore to the point prior to the LASTNAME transaction.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPBEFOREMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-7

STOPATMARK is similar to STOPBEFOREMARK, but it includes the marked transaction instead to stopping prior to it. Here we will restore back to LastName with STOPATMARK.
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE
GO
RESTORE LOG AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.trn’
WITH STOPATMARK= ‘LastName’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-4

But wait, there is more! You can also use WITH MARK to restore to a LSN instead of a name.
[code language=”sql”]
SELECT TOP 3 database_name, mark_name, description, lsn, mark_time
FROM msdb.dbo.logmarkhistory
ORDER BY mark_time DESC
GO
[/code]

sshot-8
[code language=”sql”]
USE master
GO
RESTORE DATABASE AdventureWorks FROM DISK = N’D:\Backups\AdventureWorks_now.bak’
WITH NORECOVERY, REPLACE GO RESTORE LOG AdventureWorks
FROM DISK = N’D:\Backups\AdventureWorks_now.trn’ WITH STOPBEFOREMARK= ‘lsn:45000000323400003’
GO
SELECT TOP 5 FirstName, LastName, EmailAddress, EmailPromotion, Phone
FROM AdventureWorks.Person.Contact
GO
[/code]

sshot-7

Issue installing PowerPivot (again)

by Leave a Comment

I have run into this problem a few times and it always takes me a while to remember the solution.

Installing PowerPivot on Excel 2010 appears to be successful, but the tabs do not appear. When I tried to add it directly in the Add-Ins section it still gives an error message “Not loaded. A runtime error occurred during the loading of the COM Add-in.”. The solution for me has been to install VSTO 4.0 Runtime.

See this thread on MSDN for the details

PASS Healthcare July Presentation

by Leave a Comment

Topic: SQL Server MDS with Maestro Healthcare
Speaker: Tyler Graham, Profisee
Date/Time: July 25, 2012 1:00PM Eastern Standard Time (Wednesday)
Attendee Link: www.livemeeting.com/cc/8000181573/join

Topic Synopsis: In SQL Server 2012, MDS is matured into an enterprise MDM solution. To leverage all its capabilities, companies should have a standardized, industry-specific model to jumpstart their business into a single view of all known entities. In Healthcare, an enterprise-ready solution must fully comply with HL7 V3 requirements and all other healthcare standards bodies’ recommendations.

Building on this requirement, Maestro Healthcare goes the extra mile to include complete reference data that solves your ICD9-ICD10 transition dilemmas. In this presentation, Tyler Graham will discuss how a wide variety of healthcare companies can leverage the reference data support of Maestro Healthcare Industry Solutions with SQL Server 2012. He will then delve deeply into Profisee’s ICD9 and 10 reference data support and demonstrate that Maestro Healthcare provides a comprehensive, industry-specific MDM solution with a full body of features – matching, survivorship, and data quality strategies; integration, collaboration, and governance services; expert skills transfer and implementation services; and complete platform and solution support.

About the Speaker: As a key member of the former Stratature team, Tyler Graham joined Microsoft during its acquisition of the original MDS technology in 2007. During his tenure at Microsoft, he designed and managed development of many MDS features, including the Entity Based Staging and Excel add-in, which are new in SQL 2012. Tyler also co-authored the first ever MDS book in 2011 with an updated edition scheduled for release later this summer. In 2012, Tyler joined Profisee where he is director of Industry Solutions, building specific Maestro and MDS models for industries such as Retail, Insurance and Healthcare.