MDS Configuration Manager

Problem
So you found yourself looking at a delightful love note…make that an error message telling you:

Could not load file or assembly ‘Microsoft.Web.Administration, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ or one of its dependencies. The system cannot find the file specified.

Context
This error message appears after you install Microsoft Master Data Services (MDS) and you open Master Data Services Configuration Manager.  Unfortunately, this error prevents you from viewing the Web Configuration tab.  Fortunately, the error message is a clear indicator as to the problem.  MDS requires the use of IIS, Internet Information Services, for the web interface in the data management.  For some odd reason, or my lack of desire to know why, the Web Management Tools was not included during the installation.  The World Wide Web Services should already be installed, but notice that the IIS Manager is not present under the Administrative Tools indicating the missing Web Management Tools.

Solution
In a nutshell, you need one check box.  So here’s the treasure map, find the x!

1. Open up Program and Features.
2. Open up Turn Windows features on or Off
3. Check the following box to include Web Management Tools.

Mission accomplished.  Now move along with the deployment.

SQL to remember

SELECT * FROM syscomments s

LEFT OUTER JOIN sysobjects o ON s.id = o.id

WHERE text like '%Search Term%'

That is how you search through (in my case) stored procedures in a database. Works in SQL 2000. No that's not a typo.

SQL 2008 Writeback

When attempting to do a writeback in Excel 2010 the following error is returned:

"Data could not be retrieved from the external data source.
Error message returned by the external data source:

Server: The operation has been cancelled due to memory pressure."


I repeated the same action while looking at the resource monitor on the server that the cube was located. The memory linearly increased from 17% to 99% then started hitting huge seg. faults with varying memory consumption until the error was displayed in Excel.

So it's a memory problem.

And the interesting thing about this, SQL 2008 analysis services doesn't flush the memory until someone connects to it. (i.e. the memory on the server didn't drop back to 17% until I did a reconnect in SQL Server Management studio to the cube)

Apparently this cube needs to be designed better or the server needs more than 4GB of RAM.



Not that you wanted to know this...whoever decided to read it.

Note to self

SQL 2005 SSAS Measure Display Folders:
http://msmvps.com/blogs/robfarley/archive/2009/06/09/putting-a-calculated-member-into-a-display-folder.aspx

SQL 2005 SSIS Sharepoint list tool component:
http://sqlsrvintegrationsrv.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=17652

Virtual PC and SQL Database Restore

A coworker ran into the problem of not being able to restore a SQL database to a vpc machine. The Name of the backup would read in as "*** INCOMPLETE ***". The backup file was fairly large (about 6GB). A small database restore worked, but not the large file.

Anyways, the exact problem and simple solution to the problem can be found on Stuart Cox's blog:
http://techpunch.wordpress.com/2008/10/16/virtual-pc-sql-server-restore-database-failure-media-family-incorrectly-formed/

I need to start structuring posts like Stuart Cox, clearly laying out Problem / Resolution, because that makes things clear to understand.

Row Numbering

I came across a precarious situation today at work. I needed to write a paging stored procedure for use with a DetailsView. The situation and solution for start to finish is described at http://microsoft.apress.com/index.php?id=51. Unfortunately the sql database was currently running on an instance of sql 2000. Row_Number() is a specific function avaliable with sql 2005, not sql 2000.

The sql query I developed seems to work for an equivalent to Row_Number(). My case only had one sorting method, and I know that my column that is being sorted is unique. thus simplifying some matters.

Let's presume we have a Table1 with columns ID as an int, Code as a varchar, and Name. Table1 should be sorted by the column Code.


SELECT ID, Code, Name
(SELECT COUNT(ID) AS Expr1
FROM Table1
WHERE (Code < (SELECT Code FROM Table1 WHERE (ID = t.ID)))
) AS RowNum FROM Table1 AS t ORDER BY Code


Whilst this may not be very efficient or pretty, it gets the job done. Basically this query has a sub query to count the number of rows that are less than the current Code (which is selected by a further sub query).

Unorthodox probably, as bad as a GOTO statement maybe, works under the right circumstances.

AdventureWorks Database

Tips on installing AdventureWorksDW for the analysis tutorial for SQL 2005:
1) Download the file at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004 (No longer at downloaded at Microsoft)
2) Select the AdventureWorksBI.msi (This is the database for the ssas tutorials).
3) Run the msi installer that you just downloaded, default installation is fine.
4) The msi installer for the sql 2005 Adventure Works database does not automatically attach the database files to a SQL Instance. There are multiple ways of accomplishing this:
a) Open Sql Managment Studio, New Query, Copy the text below (If you made any changes to the install location, or the drive is not on C, then make the appropriate changes in the file names. Just search for AdventureWorks if you're not sure of the file location)

exec sp_attach_db @dbname = N'AdventureWorks',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'

[http://www.codeplex.com/SqlServerSamples/Wiki/View.aspx?title=SQLServerDatabasesandSamplesOverview&referringTitle=Home]

b) Connect to the SQL Server Instance that you would like to install the AdventureWorks database. Right click the database folder and select "Attach...". This will bring up a SQL Server Attach Databases Dialog. Click the Add button and locate the AdventureWorks_Data.mdf file.


Basically it's really easy, you need to know that the msi file does not automatically attach the database.

Slowly Changing Dimensions

I learn something new every time I go to work. Today, the concept of slowly changing dimensions. Typically when I created a database and then a website application to go on top of the database, I had the typical select, update, and insert. The update function would overwrite the current values with the new values. So what about history of the data? It's gone obviously.

Let's say we are looking to analyze the data in our database. The database is tracking sales from users. One user buys items, then moves to another state (and updates their account information) and buys more items. If you want to know the sales by state, all the sales for that user will be linked to their current location. Maybe this is what you want, however if you want to compare sales in each state over time, history will keep changing. Solution? Slowly changing dimensions: keep a history of these dimensions that change over time (typically slowly, hence the name slowly changing dimensions).

Time to adjust my databases, as Yuri once said: "The less you know, the better you sleep".