SQL Server Master Data Services (Nov CTP)

Last night I configured up SQL Server Master Data Services (MDS) on a test box. It looks good so far. I ran into a few issues with the box/setup that I had to tweak in order to get it working. I had to allow “handlers” and “modules” in the applicationHost config on the machine. IIS was also inadvertently set up with only anon access, which was an issue, after I got windows auth installed and turned on everything seemed to work.

The app/system itself is pretty slick. Very basic, but lets you do complex things. Once you get some users set up, and a few models (think: Product, Customer), you can add entities (think: Category1, Category2, etc) you can set up hierarchies, business rules, etc.

I haven’t played much more with it, but it seems like it could get the job done. I would say some things aren’t intuitive enough. Example – they could say “drag this over to this area” but there is nothing as far as what to do, its kind of guess and check.

I’m excited to see where MDS goes.

mds

Windows 7 – ASP.NET Temporary Internet Files, Assembly Redirects

Just updated my work laptop to Windows 7. Sweet right? Except now the fun of getting everything to work.

First issue I ran into, a Web App in development, not being able to load up “Microsoft.AnalysisServices.AdomdClient” because it was looking for version “9.0.242.0” – the version you get with Office 2007, SQL 2005. But I have 2010 running and SQL 2008 stuff on this new build, so saw that issue. Of course I could install the stuff from SQL 2005, the drivers, but also I can redirect my assembly in the Web.config





I was also getting some weird stuff about permission denied to any third party assembly when trying to debug/run it locally. Since the web app is impersonating a user, I figured I need to give that user rights to the temporary internet files for ASP.NET, which worked, but in Win7 (and Vista?) the path is different, it is

%LocalAppData%TempTemporary ASP.NET Files

I gave the user modify and my app finally builds!

Hopefully not too many more roadblocks going forward..

Using CTE’s to Create Dynamic Pivot Tables in SQL 2005/2008

One of the cool new commands introduced in SQL 2005 was the PIVOT command (and UNPIVOT). One thing that has always irked me about PIVOT was that you need to “hard code” the column names you want to actually pivot on. This is usually fine for a static report, say you want to pivot on Apples, Oranges, Bananas. Great. But what if you want to pivot on Month/Year? “Apr 2009”, “May 2009”, etc, etc. Still ok, if you want to hardcode your month years and they never change. But in some cases, you need to keep adding the current “Month Year” combo to the query.

You can go the old fashioned route and create a dynamic SQL statement and do an EXEC (@dynamicSql) , or you can try to figure out a way to do your PIVOT dynamically.

What I did here is create a dataset and added a numeric ordering and then pivoted on that instead of the the actual value “Month Name Year” (eg: “May 2009”). That way, my pivot is always the same, 6 months worth, or whatever you like, and you can pass in parameters for the date filters in the top query to get your correct range.

-- get your distinct month/year vals into a temp table
-- need temp table here because CTE doesnt like a subquery and row_number
SELECT DISTINCT CalendarMonthYearName, MonthOfYear,CalendarYear
      INTO #tmp_monthyears
      FROM dbo.DimDate
      WHERE Date > '02/01/2009' AND Date < '07/01/2009'
      ORDER BY CalendarYear DESC,MonthOfYear DESC;

-- create a CTE that adds a "Row Number" which is number 1-6
WITH CalMonthYears AS
(
-- add your number
SELECT CalendarMonthYearName AS 'CalendarMonthYearName',
      MonthOfYear,
      CalendarYear,
      ROW_NUMBER() OVER (ORDER BY CalendarYear DESC) AS 'YearMonthNum'
      FROM  #tmp_monthyears

)
SELECT ItemNumber,
      [1],[2],[3],[4],[5],[6]
FROM
(
-- your query to get your data, pivot this data
SELECT YearMonthNum,s.ItemNumber,
      SUM(Quantity) AS 'Quantity'
      FROM DataHistory s
      INNER JOIN dbo.DimDate d ON s.DateKey = d.DateKey
      INNER JOIN CalMonthYears cmy ON d.CalendarMonthYear = cmy.CalendarMonthYearName
GROUP BY YearMonthNum,s.ItemNumber
) AS SourceTable
PIVOT
(
SUM(Quantity)
FOR YearMonthNum IN (
      [1],[2],[3],[4],[5],[6]
      )
) AS PivotTable;

DROP TABLE #tmp_monthyears

The sky is the limit with this, you can modify this or think of other ways to use this logic to make your PIVOT commands dynamic, so you don’t have to keep editing stored procedures every time your pivot columns change 🙂

SQL Server 2008 – Saving changes is not permitted

Finally getting around to doing some work on SQL 2008, and after about 3 minutes, I run into this error: “Saving Changes is not permitted.. blah blah blah” See screenshot below.

image

This is different than SQL 2005. Microsoft maybe trying to save us from ourselves? The thing is, I never “enabled the option Prevent saving changes that require the table to be re-created” – it seems to be enabled by default. It would be awesome if this error told me exactly where the setting was.

 

Well, it happes to be in Tools->Options, Designers, Table and Database Designers. Uncheck the box and go about your merry way!

 

image