Speaking at SQLsaturday #380 New York

New York Times SquareA few weeks ago, I was up early in the morning and to my surprise an email ticked in – the  email was from the SQLSaturday event managers in New York. the had the pleasure to tell my that my session on Azure – Automation of deployment had been selected.

I am very honored to be chosen to speak at this huge event, where more that 70 speakers from around the globe will attend and make this a day of plenty information and knowledge sharing for every attendee.

The event will be held saturday 30th of May, at Microsoft New York office at Times Square, so it’ll be smack middle of Manhattan. Really looking forward to give my talk there, and I think that it may be the biggest event I have been a speaker at to this moment.

We in Rehfeld Partners A/S, think that this would be an opportunity for our customers to get free knowledge and meet some of the leading experts on Business Intelligence, Microsoft SQL Server, Sharepoint, Power BI and other technologies from Microsoft. Therefore we have decided to invite customers along, and join us on this trip to New York, Rehfeld Partners A/S have 2 speakers at the event, and Effektor A/S (company that started in Rehfeld) have 1 speaker at the event. We will arrange roundtable meetings, where we discuss technology, business intelligence and how we see the future from a Danish / Microsoft perspective.

If anyone is interested in joining us, please email me for further information, and I will see to it that everything needed will be forwarded to you.

Catch me on kmn@rehfeld.dk

Campus Days 2014 – Session 1

B3XC8oLCQAEzROX Today was the day where Regis Baccaro ( T | B ) and I had the pleasure to speak at Campus Days in Copenhagen. Started the day by making final changes to the slidedecks, going through the slides together and making a plan for the session. Continue reading →

Pre Pass Summit 14

20141106_000328000_iOS This is the week after Pass Summit 2014, and what have I learned, gained, experienced during Pass – well for starters I’ve meet a bunch of interesting people, sat through a whole lot of sessions, sessions that both inspired me and made me think about the way I implement solutions at my clients. Continue reading →

MDS 2012 – Recreate subscriptionviews

In Master Data Services 2012 (MDS) we have the possibility to create an subscription view of the data in our MDS lists. This gives users, applications access to Master Data (MD) through a view, and therefore we have full control over MD, as the user or application is unable to update the MD in the view.

One of the disadvantages is that whenever there is a change in the Meta Model for the MD which the subscriptionview is based – we have to recreate the subscriptionviews. A rather tedious task for the one that have to do it.

As you can see here it takes a good amount of time to delete and recreate the view.

There are a few ways to do it, one is to use a SSIS package to do it, another is to use a T-SQL script, this post will show the latter, but there is a post on the way for SSIS.

So to help with that I created a SQL Script that will do that for you in a matter of seconds, all views in the specified Model and Version will be deleted and recreated – at the moment the script can only handle the simplest form of views.

All you have to do is to rename the Modelname and VersionName to the one you’re using, and as always this is an unsupported way of using MDS, and you should not do it in your Production Environment, and only if you’re confindent with the MDS datamodel.

DECLARE @ModelName as Varchar(50) = 'FinanceMasterData'
DECLARE @versionName as Varchar(50) = 'MonthlyPostings'
DECLARE @ViewName as Varchar(50) 
DECLARE @ModelID as INT
DECLARE @ModelVersionID as INT
DECLARE @EntityID as INT
DECLARE @Rows INT = 1, @Id INT = 0

----Set Model ID
SELECT @ModelID = ID FROM MDM.tblModel WHERE Name = @ModelName

----Set ModelVersionID
SELECT @ModelVersionID = ID FROM mdm.tblModelVersion 
WHERE name = @versionName

--Delete temp table if already created
IF OBJECT_ID('tempdb..#TempSubscriptionViews') 
IS NOT NULL DROP TABLE #TempSubscriptionViews

--Fill temp table with SubscriptionID, EntityID (MDStableEntityID) 
--and SubscriptionViewNames for supscriptionviews
SELECT S.ID, E.ID as EntityID, E.name Into #TempSubscriptionViews 
FROM mdm.tblSubscriptionView S
INNER JOIN MDM.tblEntity E ON E.ID = S.Entity_ID
WHERE S.Model_ID = @ModelID and S.ModelVersion_ID = @ModelVersionID


--Delete all the Subscriptionviews 
DECLARE @DeleteID INT
DECLARE DeleteCursor CURSOR FAST_FORWARD
FOR

SELECT ID FROM #TempSubscriptionViews
OPEN DeleteCursor

FETCH NEXT FROM DeleteCursor
INTO @DeleteID
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC mdm.udpSubscriptionViewDeleteByID @DeleteID,1
FETCH NEXT FROM DeleteCursor
INTO @DeleteID

END
CLOSE DeleteCursor
DEALLOCATE DeleteCursor

--Recreate all the Subscriptionviews
DECLARE CreateCursor CURSOR FAST_FORWARD
FOR

SELECT EntityID, Name FROM #TempSubscriptionViews
OPEN CreateCursor

FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC MDM.udpSubscriptionViewSave     NULL,
                                         @EntityID,
                                         @ModelID,
                                         NULL,
                                         @ModelVersionID,
                                         NULL,
                                         1,
                                         NULL,
                                         @Viewname,
                                         NULL,
                                         NULL
FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname

END
CLOSE CreateCursor
DEALLOCATE CreateCursor