How to Process Multiple Dimensions via BIDS and SSMS

This is one of those tips I need to write down, before I forget…
Sometimes Analysis Services acts up and you need to do a process full on all the dimensions in the database. When that happens I usually turned to Visual Studio Business Intelligence Development Studio [BIDS] or SQL Server Data Tools [SSDT], which allows you to select more than one dimension at a time, and fire up the processing. See figures below:


Open a connection to a “live” database

Open Connection

Open Connection

Choose which server/database to connect to:

Choose Server/Database

Choose Server/Database

Select all dimensions and right-click:

Select dimensions

Select dimensions and right-click

Finally, choose processing mode:

Processing Mode

Processing Mode


Until today, I didn’t know you could actually do the same action through SQL Server Management Studio [SSMS]. You do as following:

Connect to the server:

Connect to Server

Connect to Server

Expand till you get to the Dimensions folder:

Find the Dimensions folder

Find the Dimensions folder

Hit F7 to open the Object Explorer Details:

Select All and Right-click

Select All and Right-click

Select Processing Mode:

Select Processing Mode

Select Processing Mode


Posted in Programming | Tagged , , | 1 Comment

Buy One, Get One Free

Packt Publishing releases the 2000th title. During this offer Packt is giving its readers a chance to dive into their comprehensive catalog and Buy One, Get One Free across their entire range of eBooks until 26th-Mar-2014.


Posted in Mixed | Tagged , | Leave a comment

SSIS Script Task and Proxy Auto Configuration (PAC) Script

Actually this post could have been divided into several topics, but currently time does not permit me to write that often. So this will be the whole shabang at once.

We needed to import data from an external source, using Java Script Object Notation [JSON], though Microsoft

SQL Server Integration Services [SSIS]. Using a Script Task to set up the connection , retrieve the HttpWebRequest and passing the data along to SQL Server was, in broad terms, the way to solve the task. Now, along the way a Proxy Auto Configuration [PAC] Script was introduced, which complicated things a bit.

A PAC is a script that let’s for example Internet Explorer, or any other agent, to choose the appropriate proxy to use, for the current context. An example of a PAC script could look like this:

function FindProxyForURL(url, host) {
 // our local URLs from the domains below don't need a proxy:
 if (shExpMatch(host, "*"))
 return "DIRECT";

 // URLs within this network are accessed through
 // port 8080 on
 if (isInNet(host, "", ""))
 return "PROXY";

 // All other requests go through port 8080 of
 // should that fail to respond, go directly to the WWW:
 return "PROXY; DIRECT";

The quick reader, will already have spotted, that such a PAC Script can indeed return several proxy URLs, separated by semicolon ‘;’. Ours did, in our case. I was ignorantly expecting a single URL to be returned, and spent some ½-hour trying to fix things here and there, without actually getting to the cause. Thanks MessageBox.Show(…) :)

So, how to integrate this into the Script Task of SSIS?

A piece of code on Code-Project managed to get me up and running in a jiff, so please have a look at:

The article explains how to set a proxy using PAC files. The solution presented in the article uses the WinHttp.dll for obtaining a proxy URL.
When this code is embedded into the Script Task, it’s a stroll in the park to get your data flowing.

HttpWebRequest request = null;
HttpWebResponse response = null;

string listOfServers = GetProxyForUrlUsingPac(Variables.SourceURL, "");
WebProxy p = new WebProxy("http://" + listOfServers.Split(new char[] { ';' })[0]);
request = (HttpWebRequest)WebRequest.Create(Variables.SourceURL);
request.Proxy = p;
response = (HttpWebResponse)request.GetResponse();

Stream s = response.GetResponseStream();
StreamReader sr = new StreamReader(s);
string json = sr.ReadToEnd();
//DO Whatever you want with the JSON stream

Now, this has every mark of a quick ‘n dirty, so you might want to make it a lot more robust. The list of proxy servers I receive could be put to a better use, rather tahn just picking the first one… But I hope this helps shed some light onto how to connect SSIS Script Task through a proxy, using a PAC Script.

Posted in Programming | Tagged , , | Leave a comment

How to generate the alphabet from ASCII codes in T-SQL

Just now I had the requirement to create a loop in SSIS, and have that loop the alphabet.
In order to facilitate that, I generated a T-SQL statement, that provides the alphabet including the Danish Æ, Ø and Å. If you don’t need these, you can leave out the latter part of the last where clause (197, 198, 216).

   1:  SELECT CHAR(number) FROM master..spt_values
   2:   WHERE
   3:      Type = 'P'
   4:      AND
   5:      ( number between 65 and 90
   6:          OR
   7:        number IN (197, 198, 216)
   8:      )

Link to an ASCII table with possible values ranging from 0 to 255.

Posted in Programming | Tagged , , | Leave a comment

Subtle differences in Teradata and SQL Server, Part 2

Implementing Business logic in views is something seen quite often. Actually a view in my experience often contains some sort of data enrichment, -enhancement or -extension. So I am surely not the first one to come across the thought of adding some nifty tricks to a view. Well, tonight it seems I got too creative.

Imagine you have a table T1 with a column C1 that contains the definition of a certain type. And then we have another table T2 with a column C2 that cotains values of thses types. Now, since human interaction has been involved in populating the values in T2, there is a good change the values are missspelled, renamed or otherwise differ from the original true value. In my case there was a guaranteed match on the first six (6) characters, the rest could differ. So, I though I’d join the two tables on the columns, using only the first six characters as join condition.

The Query:

SELECT * FROM T1 JOIN T2 ON SUBSTRING(T1.C1 from 1, 6) = SUBSTRING(T2.C2 from 1, 20)

Works pretty well, until you try to create a view containing that type of join. Then you’ll get a syntax error.
Apparently the Teradata engine is parsing the statement in different ways depending on the context of use.

Needles to say, this Works in SQL Server, and is documented as well (see last note):

Posted in Programming | Tagged , | Leave a comment