SSASMeta – C# App to Log Info About SSAS Objects

I manage some servers that have many cubes. OK, a lot of cubes (60+ on one). I needed some way to output a report of last processed time, last schema update, etc. Now, there are about 5 different ways to do this (one being the SSAS Stored Procedure Project), but this is what I came up with. I wrote a 100 line C# app to take a server name, loop through the SSAS DB’s, cubes, measures, partitions, and dimensions and log info about them.

Here is a c# code snippet of a function that just outputs to the console, the app I have actually logs the info to a SQL Server database and then I can write reports off that.

     private static void LogSSASInfo(string serverName)
        {
            var server = new Server();
            server.Connect(serverName);

            foreach (Database database in server.Databases)
            {
                Console.WriteLine(database.Name + " " + database.LastUpdate + " " + database.EstimatedSize / 1024 + " " + database.CreatedTimestamp);

                foreach (Cube cube in database.Cubes)
                {
                    Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);

                    foreach (MeasureGroup measureGroup in cube.MeasureGroups)
                    {
                        Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);

                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
                        }
                    }
                }

                foreach (Dimension dimension in database.Dimensions)
                {
                    Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
                }

                Console.WriteLine("");
                Console.WriteLine("------------------------------------------------");
                Console.WriteLine("");
            }

            server.Disconnect();
        }

As you can see, it isn’t the most elegant code in the world, but it works. In order to get this to work in your project, you need to reference the Microsoft.AnalysisServices assembly.

ssasmeta

Use your imagination, you could make an app wrap that function above and log info for all the SSAS instances on your network. There have been a few times already in the last year where I have found some cube or measure group not updating correctly and a report like the one I can get now will help dealing with that challenge.

Hacking Microsoft Pro Photo Tools – Using Reflector to use MapPoint Lat Long Lookup (for free!) in C#

The other day, Microsoft came out with “Microsoft Pro Photo Tools” which allows you to geocode your photos. It is a pretty cool app, but there are some things that I wonder, like why didn’t they just build this functionality into Windows Live Photo Gallery?

Anyway’s, with any new thing I download and play around with, I started digging into stuff. I looked in the install directory, C:Program FilesMicrosoft Pro Photo Tools and noticed that there are some Interop assemblies and other assemblies, etc. I fired up Reflector and started disassembling the assemblies and exe. Pretty cool stuff, you can see what they are doing. Using xaml forms, etc. The cool stuff is the Location based stuff.

Microsoft has MapPoint web services which you can use/sign up for, but they cost a pretty penny. I have used some of these web services in the past and they have a ton of functionality.

Like I said, digging through the disassembled stuff in Reflector, I saw a method “GetLatitudeLongitude()” which takes in country, state, city, address, zip and returns a lat long object. But, you need a “MapPointWrapper” object to use it.

I fired up Visual Studio 2008, and then referenced the assemblies in the Pro Photo Tools directory so I could use them in code. I created a test WinForms app, and started hacking away.

Looking at the MapPointWrapper class constructor in Reflector, I noticed that it needs a username, password, URL, and timeout, the first three I don’t have – but I bet I could find!!

Here you can see the constructor as it looks in Reflector. The thing I noticed right away is that they have the username and password embedded in the function, although its all “encoded”, then blend the strings together to create default credentials. Their blend method is using some bitwise operators, etc, if you are interested, you can just click on the Blend method and it browses to that (did I mention Reflector is cool??) – anyway’s, I still need a URL…

image

Reflector lets you click on a class and “analyze” it, which gives you what classed depend on it, which classes use it etc. Just going through the list for MapPointWrapper, I found one that showed how they call the constructor.

image

That’s the ticket! You can see they are passing in empty strings for user/pass (which then gets converted to the correct user/pass by the constructor) and then the URL is right there!!! nice! We can use this!!

Now, on to using this functionality in our own app!!

image

Now, this will give you the lat/long back from MapPoint! Sweet. Now we can start digging into everything else – what else do these assemblies expose?? Can I get routes? directions? Maps? etc, etc, etc. There is a plethora of things to dig into. It looks like they are just using Virtual Earth though to get maps, not MapPoint (from what I can tell anyways).

I know there are a ton of other ways to get this info, but this was basically a test to reverse engineer their assemblies and use the functionality. I don’t recommend or condone hacking/reverse engineering assemblies like this for profit, more for fun , in other words – don’t use this in a production app as Microsoft would probably find out and come hunt you down.

This post is also just an example of how .NET code can be disassembled easily and re-used, for good, or evil 🙂

There are some basic things that every developer should do with .NET desktop apps – use Dotfuscator (which just obfuscates your code, making it harder/not feasible to reverse engineer, and also encrypt any strings/values you don’t want anyone else using or reading. That being said, Reflector is a great way to see how other applications are coded, and learn how they work. Happy Coding Hacking!

OAuth: Getting Started with OAuth in C#, .NET

I have been playing around with Pownce and their API. They offer HTTP Basic Authentication and OAuth authentication. I decided to give a go with OAuth since BASIC auth just seems, dirty insecure to me. I started digging around, and http://oauth.net/ has some good info. Under code there is a C# (CSharp) version – http://oauth.googlecode.com/svn/code/csharp/  but, I couldn’t find any good examples of getting started implementing this in your app, so…

I downloaded the OAuthBase.cs class and added it to a sample project so I could get going. Now, how to use this OAuth thing…

Well, first you need a “request token” server/url that you can use, something that takes your request and gives back a token (You can use http://term.ie/oauth/example/ to test, instead of Pownce  or some other utility)

As the “consumer” of the service, you have a key and a secret. The hardest part of the OAuth request is generating the signature, which the OAuthBase.cs does for you. I did run into some small issue with generating a timestamp though, seems that the OAuthBase.cs class had/has a bug in the timestamp function. it was returning back a timestamp like 12393923423.134  instead of just 12393923423 – which the first one, with the .134 will cause an invalid signature in your requests.

I sent a comment/message to the creator of OAuthBase.cs about it, not sure what else to do there, I am pretty sure I had the latest version (it was linked off oauth.net)

here is the function I changed:

public virtual string GenerateTimeStamp() {
    // Default implementation of UNIX time of the current UTC time
    TimeSpan ts = DateTime.UtcNow – new DateTime(1970, 1, 1, 0, 0, 0, 0);
    string timeStamp = ts.TotalSeconds.ToString();
    timeStamp = timeStamp.Substring(0, timeStamp.IndexOf(“.”));
    return timeStamp;           
}

Now, you want to test this out, create a test .NET app (C#), and add OAuthBase.cs to your project. I created a test Windows Form app. I had to add a reference to System.Web as well., then the basic code (I am using the test OAuth server)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Web;
using OAuth;

namespace PownceTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            string consumerKey = “key”;
            string consumerSecret = “secret”;
            Uri uri = new Uri(“http://term.ie/oauth/example/request_token.php”);

            OAuthBase oAuth = new OAuthBase();
            string nonce = oAuth.GenerateNonce();
            string timeStamp = oAuth.GenerateTimeStamp();
            string sig = oAuth.GenerateSignature(uri,
                consumerKey, consumerSecret, 
                string.Empty, string.Empty,
                “GET”, timeStamp, nonce,
                OAuthBase.SignatureTypes.HMACSHA1);

            sig = HttpUtility.UrlEncode(sig);

            StringBuilder sb = new StringBuilder(uri.ToString());
            sb.AppendFormat(“?oauth_consumer_key={0}&”, consumerKey);
            sb.AppendFormat(“oauth_nonce={0}&”, nonce);
            sb.AppendFormat(“oauth_timestamp={0}&”, timeStamp);
            sb.AppendFormat(“oauth_signature_method={0}&”, “HMAC-SHA1”);
            sb.AppendFormat(“oauth_version={0}&”, “1.0”);
            sb.AppendFormat(“oauth_signature={0}”, sig);

            System.Diagnostics.Debug.WriteLine(sb.ToString());

        }
    }
}

 

If you run that app, you will get a debug line like..

http://term.ie/oauth/example/request_token.php?oauth_consumer_key=key&oauth_nonce=1901809&oauth_timestamp=1208645244&oauth_signature_method=HMAC-SHA1&oauth_version=1.0&oauth_signature=iv%2b45QPR9a%2fMDjw8qkEee61Fp0g%3d

One thing that had me scratching my head of a second was my signature was good like 80% of the time, I noticed I wasn’t URLEncoding it, so spaces were getting sent as ( ) instead of (+) – doh!

If you click on the link that is generated, you will get a response like

oauth_token=requestkey&oauth_token_secret=requestsecret

We are good to go! This is just the first step. We need to use those tokens now to move on, but we got past the first step of authenticating to the OAuth server to get tokens! Yay! (Ex: your app has to actually request that url, use the tokens, have the user authorize your app, then go from there..)

This maybe the first in a few blog posts on OAuth – happy coding!