Process More Than One SSAS Object At a Time in SSMS

Durrr . . . sometimes I discover something so simple all I can say is "durrrrrr". I've been working with SSAS for over 2 years now and never knew that you could process more than one SSAS object at a time using SQL Server Management Studio. In case anyone else is as dense as me . . .

Simply open the Object Explorer Details window if it's not already open. Click on the folder containing the objects to be processes. Select multiple objects in the browser and initiate processing. That simple. I'm not sure why SSMS doesn't allow the selection of multiple objects in the Object Explorer tree control.

What's That Confounded Character?? (ASCII and CHAR Functions)

I was on my way back from the kitchen at my client's site when I passed the office of one of their developers. We made momentary eye contact and I could see that look of desperation. She immediately called out my name and said something like, "Dave! I need your help. You're smart." I can't resist that kind of flattery so I immediately put down my yogurt and pulled up a chair.

It seemed like a simple problem. She just wanted to take part of a string from a larger one - a substring. Below are examples of the complete strings. They were created from some kind of a flat file extract from a legacy system. Assume the column name is PRODVAL.

ymkez 11/30/2009 456.8
nipin+ 11/30/2009 432.90
gapter- 11/30/2009 543.12

She simply wanted the first part of the string - up to the "space". I don't recall her logic at this point but it used the T-SQL SUBSTRING and PATINDEX functions.

I began to re-write her query. I came up with the following.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(' ', PRODVAL) - 1) FROM table

Well, it didn't work. To troubleshoot the problem I rewrote the above query to as follows.
     SELECT CHARINDEX(' ', PRODVAL) FROM table

The query returned a whole long list of zeros. Something was obviously wrong. I could see the space. So why couldn't the CHRINDEX function? Next step, figure out what the "space" character actually is. The next query helped determine that.
    SELECT ASCII(SUBSTRING(PRODVAL,6,1) FROM table

The first row in the table had a PRODVAL value with a "space" in the 6th position so the above query returned the ASCII value of this 6th character. It returned a 9 - Tab.

Ok great. So now I knew we were looking for a  tab, not a space. So, going back to our original query, we can modify it to use the CHAR function to look for the tab.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(CHAR(9), PRODVAL) - 1) FROM table

Voila! Using the ASCII function we identified the mysterious character and then used the CHAR function to convert the ASCII code for Tab (9) to a character so that we could use it in a SUBSTRING function.

Is Everything OK?

Is it just me or is there still a bit of a lack of an overall SQL Server BI ecosystem? Don't get me wrong, I am a big proponent of SQL Server BI stack and there are a lot of nice integration points but I think there is still a lot of work to be done to tie it all together.

One of my biggest gripes is related to where to look when there seems to be an issue with a BI solution that uses SSIS, SSAS, and SSRS. And what if you just want to know if things have been humming along. We can implement all the email notifications in the world but what if your email system is unreliable (as it was with a recent client). Hence the question, "Is everything OK?"

I'm pretty well versed in exception handling and logging and can probably set my brain to autopilot and find an error message in a log on one of 10 servers without actually thinking about it but what about everyone else? There are other stakeholders such as more junior developers or even senior level people that are unfamiliar with SQL Server. How about the business? An analyst might see something fishy in the data and want to know if last night's load was successful. Do they have to call me or someone else on the development team? In many cases yes. But why? That kind of a dependency is unhealthy.

That's where the SQL Server BI Health Monitor Dashboard comes in. The SQL Server Health what? Don't fret if you haven't heard of this. I'd be suprised, no maybe disturbed, if you had. The SQL Server Health Monitor Dashboard was something I recently put together for a client that was unable to obtain a seasoned SQL Server BI developer but needed to continue to support the solution I built for them without having to rely on getting a hold of me.

The dashboard uses all out-of-the-box data sources including some system tables, the SSIS log table created by the SQL Server log provider, and the SSRS tables found in the ReportServer database to tie together a majority of the data needed to determine where in the SQL Server BI ecosystem your problem lives thus helping fix the problem that much faster.

I've posted the source code on Codeplex. You can find it @ http://bihealthdashboard.codeplex.com/. It's not exactly plug and play at this point. Connection strings need to be modifed and you might have to create the DimDate table if you don't already have a table of dates accessible. It's also geared towards a BI solution that has a single SQL Server Agent job step that calls a master SSIS package that in turn calls all other SSIS packages and a single SQL Server Agent job step that process your SSAS database. If that doesn't sound like your system, the solution can still be valuable in that it shows you how to extract very useful process metadata from your SQL Server BI Solution.

Which leads me to my next endeavor  ->  The SQL Server Metadata Manager . . .

Schedule a Cognos Report Outside of the Cognos Platform

Download Source Code


I know, I know . . . this isn't a Cognos blog. I apologize. However, I do think this is a good little tid-bit and it can also be applied to SQL Server based on some (very high level) similarities in their architecture.

The Cognos scheduler is not extremely robust (nor is SQL Job Agent at times) and even if it was, there are times when it is appropriate to run a job using an enterprise scheduler application such as AppWorx or Tidal Enterprise Scheduler.

By exposing most functionality through web services, Cognos (and SSRS) allow you do to things such as execute a report. So, if your scheduler can make web services calls or call executables (wrap the web service calls in a console app) you can bypass the Cognos scheduler and leverage your enterprise scheduler by running the report directly (or through a proxy app such as a .NET exe wrapped web service) by your scheduler of choice.

Below is an example of this using .NET and Cognos.


using System;

using System.IO;
using System.Web.Services.Protocols;
using cognosdotnet_2_0;
using System.Configuration;

namespace InformationCollaboration.Cognos.Utilities

{

class ExecuteCognos8BIJob
{

[STAThread]
static int Main(string[] args)
{
    string serverHost = System.Configuration.ConfigurationSettings.AppSettings["serverHost"];
    string serverPort = System.Configuration.ConfigurationSettings.AppSettings["serverPort"];

searchPathSingleObject jobPath = new searchPathSingleObject();
jobPath.Value = System.Configuration.ConfigurationSettings.AppSettings["jobPath"];

// Parse the command-line arguments.
bool exit = false;
string jobName = string.Empty;

for (int i = 0; i < args.Length; i++)
{
if (args[i].CompareTo("-job") == 0){
i++;
jobName = args[i];
jobPath.Value += "jobDefinition[@name='" + jobName + "']";
} else {
Console.WriteLine("Unknown argument: {0}\n", args[i]);
showUsage(jobPath.Value);
exit = true;
}
}

if (!exit)
{
try
{
string Server_URL = "http://" + serverHost + ":" + serverPort + "/p2pd/servlet/dispatch";

jobService1 jobService = new jobService1();
jobService.Url = Server_URL;

Console.WriteLine("Logging on.");
setUpHeader(jobService);

parameterValue[] parameters = new parameterValue[] { };
option[] runOptions = new option[] { };

asynchReply res = jobService.run(jobPath, parameters, runOptions);
Console.WriteLine("Show usage.");
Console.WriteLine("Successfully executed job " + jobName);
writeMessageToLog("Successfully executed job " + jobName, false);
return 0;
}

catch (SoapException ex)
{
Console.WriteLine("SOAP exception!\n");
Console.WriteLine(ex.Message);
writeMessageToLog(ex.Message, true);
return -1;
}
catch (Exception ex)
{
Console.WriteLine("Unhandled exception!");
Console.WriteLine("Message: {0}", ex.Message);
Console.WriteLine("Stack trace:\n{0}", ex.StackTrace);

writeMessageToLog("Unhandled exception!\n" + "Message: " + ex.Message + "\n" + "Stack trace:\n" + ex.StackTrace + "\n", true);
return -1;
}
} // if (!exit)
else
{
return -1;
}
} // Main

///

/// This function will prepare the biBusHeader
///
/// If no error was reported, then logon was successful.
///

static void setUpHeader(jobService1 jobService)
{
// Scrub the header to remove the conversation context.
if (jobService.biBusHeaderValue != null)
{
if (jobService.biBusHeaderValue.tracking != null)
{
if (jobService.biBusHeaderValue.tracking.conversationContext != null)
{
jobService.biBusHeaderValue.tracking.conversationContext = null;
}
}
return;
}

// Get authentication info from config file
string onyxUserId = System.Configuration.ConfigurationSettings.AppSettings["onyxUserId"];
string onyxPassword = System.Configuration.ConfigurationSettings.AppSettings["onyxPassword"];
string onyxApplication = System.Configuration.ConfigurationSettings.AppSettings["onyxApplication"];
string onyxSite = System.Configuration.ConfigurationSettings.AppSettings["onyxSite"];
string CAMNamespace = System.Configuration.ConfigurationSettings.AppSettings["CAMNamespace"];

// Set up a new biBusHeader for the "logon" action.
jobService.biBusHeaderValue = new biBusHeader();
jobService.biBusHeaderValue.CAM = new CAM();
jobService.biBusHeaderValue.CAM.action = "logonAs";
jobService.biBusHeaderValue.hdrSession = new hdrSession();

formFieldVar[] ffs = new formFieldVar[5];
ffs[0] = new formFieldVar();
ffs[0].name = "onyxUserId";
ffs[0].value = onyxUserId;
ffs[0].format = formatEnum.not_encrypted;
ffs[1] = new formFieldVar();
ffs[1].name = "onyxPassword";
ffs[1].value = onyxPassword;
ffs[1].format = formatEnum.not_encrypted;
ffs[2] = new formFieldVar();
ffs[2].name = "onyxApplication";
ffs[2].value = onyxApplication;
ffs[2].format = formatEnum.not_encrypted;
ffs[3] = new formFieldVar();
ffs[3].name = "onyxSite";
ffs[3].value = onyxSite;
ffs[3].format = formatEnum.not_encrypted;
ffs[4] = new formFieldVar();
ffs[4].name = "CAMNamespace";
ffs[4].value = CAMNamespace;
ffs[4].format = formatEnum.not_encrypted;

jobService.biBusHeaderValue.hdrSession.formFieldVars = ffs;
}

static void showUsage(string jobPath)
{
Console.WriteLine("Run a Cognos 8 BI job.\n");
Console.WriteLine("usage:\n");
Console.WriteLine("-job searchPath Search path in Cognos Connection to a job.");
Console.WriteLine(" " + jobPath);
}

static void writeMessageToLog(string message, bool error)
{
try
{
string errorLogPath = System.Configuration.ConfigurationSettings.AppSettings["errorLogPath"];
errorLogPath += "CognosJobServiceLog_" + DateTime.Today.ToString("dd-mm-yy") + ".txt";
if (!File.Exists(errorLogPath))
{
File.Create(errorLogPath).Close();
}
using (StreamWriter w = File.AppendText(errorLogPath))
{
string logText;
if (!error)
{
logText = message;
}
else
{
logText = "ERROR: " + message;
}
w.WriteLine("{0} {1}",DateTime.Now.ToString(), logText);
w.Flush();
w.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}

Download Source Code

SQL Server - What's My Version/Edition?

Here's a quick way to find out what version of the SQL Server engine you have installed.

SELECT 
    SERVERPROPERTY('productversion') AS ProductVersion,
    SERVERPROPERTY ('productlevel') AS ProductLevel,
    SERVERPROPERTY ('edition') AS Edition

Keep in mind that it is possible to have a SQL Server running the Enterprise edition of the SQL Server engine but the Standard edition of Analysis Services.

Deleting a Windows Service

While I typically stick with Microsoft products there are times that I must drift. I'm currently working with a client that is using Cognos for their metadata and presentation (reporting and analysis) layers. I ran into an issue this morning while trying to upgrade their version of Cognos. Unfortunately there is no automated upgrade process between the version they have and the one they want to upgrade to. This meant uninstalling the old and installing the new. In the process I could not start the Cognos service because the previous version already had a service registered with the same name. I tried uninstalling and reintsalling a number of times but to no avail. Eventually I decided to manually delete the service. I did this with the following commands (entered at a command prompt):

This command lists all the registered services on the machine
sc query state= all findstr "SERVICE_NAME"

This command deletes the service
sc delete service_name

If necessary you can also delete the service from the registry. You should be able to find the key in the following location:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

SSRS Rendering Extension - Pipe Delimited

It's very easy to leverage the existing Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering type to "create" a new rendering option in SSRS that uses a delimiter other than a comma. Below is an example of adding a "CSV" renderer that uses a pipe ( | ).

Simply add the following to the Render section of your rsreportserver.config file to add the option for rendering a pipe delimited file.


<Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
    <OverrideNames>
        <Name Language="en-US">CSV (pipe delimited)</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <FieldDelimiter>|</FieldDelimiter>
            <NoHeader>false</NoHeader>
        </DeviceInfo>
    </Configuration>
</Extension>
   
Note the FieldDelimiter tag. This is where you can set your delimiter to the character of your choice.

Be sure to include the OverrideNames tag but feel free to change the value. Once you have completed the change, restart the Reporting Services service to reload the newly modified config file.