Posted in MDS

SQL Server 2012 MDS Model corruption after SP1

Problem

After applying Service Pack 1 to a SQL Server 2012 instance my Master Data Services Model became corrupt.

When trying to validate the model, I got an Invalid Column: uda_nn_nn error message.

Needless to say that this column didn’t exist in the current model. I checked in the last backup of the database and it didn’t exist in that model either.

I’m not sure what caused it but this was how I solved it:

Solution

  • Export the model using MDSModelDeploy.exe
  • MDSModelDeploy createpackage -model “MyModel” -version “Version 3” -service “MDS1” -package “c:\MyPackage.pkg” -includedata
  •  Delete the problematic model from within the MDS UI.
  • Deploy the exported model using the deploynew clause:
  • MDSModelDeploy.exe deploynew -package “c:\MyPackage.pkg” -model “MyModel” -service MDS1

It seems that just deploying a new model (which in contrast to the deploy clone option) creates new internal object names (i.e. new attributes etc). And this sorts out the metadata corruption.

 

Posted in PerformancePoint

Building multiple caches for PerformancePoint

Background

Users are willing to wait a short amount of time for results to come back from reports, but their expectations are a lot higher when it comes to dashboards –  these need to be almost simultaneous.

We had to create 4 dashboards, each with 7 pages and each page with 3 or 4 scorecards.

These scorecards have 4 KPIs and the user needs to be able to drilldown to 3 static levels of attributes.

Even though the data is sourced from Analysis Services, the response time was sluggish, causing complaints.

Warming the Analysis Services Cache.

One method that we tried was to capture the MDX statements using SQL Server Profiler and use SSIS to fire these against the cube each morning. ( Chris Webb has a good article here )

This made things a lot faster, but it still wasn’t optimal.

Warming the PPS and IIS Caches

When a user opens a dashboard there are a number of cache’s that are built , so that subsequent executions return results  quickly.  Here’s an article that nicely explains the different caches for PPS 2010

Capture POSTs

I used a free utility called Fiddler to capture the POSTs that were being sent to Sharepoint .

These I then saved to a text file.

Send POSTs via SSIS

Next, I used a Script Task in SSIS to send each POST to the server.

public
void Main()

{

//Table
of Dashboards that need to be warmed.

List<string>
dashboardPosts = new List<string>();

string
requestFullName =
Dts.Variables[“RequestFullFilename”].Value.ToString();

string
webRequestURL = Dts.Variables[“WebRequestURL”].Value.ToString();

dashboardPosts
= stripPostsFromFile(requestFullName);

string
postData;

byte[]
byteArray;

int
PostCount = 0;

int
TotalPosts = dashboardPosts.Count;

bool
eventFireAgain = true;

foreach
(string dPost in dashboardPosts)

{

PostCount++;

//
Create a request using a URL that can receive a post.

WebRequest
request = WebRequest.Create(webRequestURL);

//Use
current credentials for the request

request.UseDefaultCredentials
= true;

request.PreAuthenticate
= true;

request.Credentials
= CredentialCache.DefaultCredentials;

request.Timeout
= 400000;

//
Set the Method property of the request to POST.

request.Method
= “POST”;

//
Create POST data and convert it to a byte array.

postData
= dPost;

byteArray
= Encoding.UTF8.GetBytes(postData);

//
Set the ContentType property of the WebRequest.

//request.ContentType
= “application/x-www-form-urlencoded”; //PPS 2007

request.ContentType
= “application/json”;

//
Set the ContentLength property of the WebRequest.

request.ContentLength
= byteArray.Length;

//
Get the request stream.

Stream
dataStream = request.GetRequestStream();

//
Write the data to the request stream.

dataStream.Write(byteArray,
0, byteArray.Length);

//
Close the Stream object.

dataStream.Close();

Dts.Events.FireInformation(1,

“Dashboard
Cache Build”,

“Post

+
PostCount.ToString() + “/” + TotalPosts.ToString(),

“”,

0,

ref
eventFireAgain

);

try

{

//
Get the response.

WebResponse
response = request.GetResponse();

//
Get the stream containing content returned by the server.

dataStream
= response.GetResponseStream();

//
Open the stream using a StreamReader for easy access.

StreamReader
reader = new StreamReader(dataStream);

//
Read the content.

string
responseFromServer = reader.ReadToEnd();

//
Clean up

dataStream.Close();

reader.Close();

response.Close();

}

catch
(Exception ex)

{

Dts.Events.FireInformation(
1,

“Exception
: ” + ex.ToString() ,

“Post

+
PostCount.ToString() + “/” + TotalPosts.ToString(),

“”,

0,

ref
eventFireAgain

);

continue;

}

}

Dts.TaskResult
= (int)ScriptResults.Success;

}

static
List<string> stripPostsFromFile(string fullFilename)

{

List<string>
posts = new List<string>();

using
(StreamReader rd = new StreamReader(fullFilename))

{

string
line;

while((line
= rd.ReadLine()) != null)

{

//if
(line.StartsWith(“dashboardId=”)) //PPS 2007

if
(

line.StartsWith(@”{“”renderRequestRecord”””
)

)

{

posts.Add(line);

}

}

}

return
posts;

}

}

The only difference between PPS 2007 and PPS 2010 is the content type and how the body of the POST begins:

Content Type

2007 – “application/x-www-form-urlencoded”

2010 – “application/json”

POST start

2007 – ‘ dashboardId= ‘

2010 – ‘ {“renderRequestRecord” ‘

Posted in SSIS

Copying SQL Stored SSIS Packages across instances

The problem

 

At the organization that I work at we run all our SSIS Packages from SQL Server (as opposed to the file system).

 

We had a need to copy all our packages from one server to another (From Production to our Disaster Recovery server). If you’ve ever tried this with Management Studio you’ll know that you painstakingly need to copy them one by one.

 

What packages are on my server?

 

The following script can be used to query which packages are on the server:

 

select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName

 

DTUtil

 

The Dtutil command line utility can be used to manage packages.

It can be used to delete or copy the packages that you’re interested in.

You can find all the parameter options at Books Online (http://msdn.microsoft.com/en-us/library/ms162820%28v=SQL.100%29.aspx)

 

Here are the options and arguments that suited me in copying one package:

dtutil /SQL “SourceFolder\SourcePackage” /SOURCES “MyProdServer” /COPY SQL;”TargetFolder\TargetPackage” /DESTS “MyDRPServer” /Quiet

Now that we’ve got the list of packages, and the DTUtil syntax for what we’re trying to do, we can generate our DTUtil commands and run them on the command line.

 

select ‘dtutil /SQL “‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /SOURCES “ProdServer” /COPY SQL;”‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /DESTS “DrpServer”‘
+ ‘ /Quiet’
from (
select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName
) pk

 

I’ve run this on my local instance, here’s the output: