Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS: Create a Calculated Measure always getting the value for "Yesterday"

If you want to create a calculated measure in SSAS (SQL Server Analysis Services) that will always get the value for yesterday, this is what I have come up with:

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[My Yesterday Measure]
 AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),
FORMAT_STRING = “$#,##0.000”,
VISIBLE = 1  ;

What is really cool is you can test this before you even make the measure, using an MDX query:

 

WITH
   MEMBER Measures.[Yesterday Measure Test1] 
   AS ([Time].[Calendar Date].&[2007-07-09T00:00:00],[Measures].[My Cool Value])
   MEMBER Measures.[Yesterday Measure Test2] 
    AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),FORMAT_STRING = “$#,##0.000”
SELECT {
Measures.[Yesterday Measure Test1]
,Measures.[Yesterday Measure Test2]
} ON COLUMNS
FROM [My Cube]

and now you can take that calculated measure and tweak for any days back (30, etc)

Cool! 🙂

Technorati tags: , , , , , , , , , ,
Categories
Geeky/Programming

Real World IT: Backing Up Cisco Routers using .NET

Usually, in a company, there is a “development” department and a “IT” department, and usually the departments don’t really work together. Development is focused on delivering business value through coding applications for end users or B2B clients. IT is busy making sure the corporate network is humming along, and that other internal issues related to technology in general are taken care of.

In my experience, I like to jump the threshold between the two departments. I started out working Helpdesk (IT Dept) and coded in the time I had free, eventually starting/breaking into the Development side. But, my passion for internal IT functions didn’t slow. Some of the guys I worked with in the IT Department always wanted applications to do specific things for OUR network, things you can’t buy, or things that you can buy a generic application for way to much money and it won’t work exactly how you want it to. That is where developers and IT can actually work together and bridge that gap.

This post is about backing up configurations on Cisco routers using .NET (C#). Now, most developers programming away on business applications really don’t care about the routers inside their company. They know they are there, might know somewhat how they work, and as long as they work, its fine – that is what IT is all about. But on the other hand, the Network Administrator really cares about Cisco routers. He dreams about them. Names his kid Cisco, or Switch.

Now, the network admin can login to all his routers, and run some commands to backup his configs. The most usual way to do this is to send the config to a TFTP server. Now, if they want a backup once a month, and they have one router, well then great, a manual solution is fine. The network is probably not big or complex and the network admin needs something to do. In most cases though, they would want to back up their routers daily, and they might have multiple routers.

In this scenario, let the network admin set up the TFTP server. Those are abundant and easy to find, easy to setup. What we are concerned with from a development standpoint is actually logging into the router, running commands to backup the config (to the TFTP server) and getting out.

Now, a few things are needed from your network admin. First, you are going to need the IP addresses of all the routers. Next, you want to make sure that they have one user on all the routers with the same password that you can use just for this backup program. There are multiple ways I am sure they can do this, and since I am not a network guru, leave that to them – they will throw out terms like RADIUS, etc, but it should be easy for them. Next, you need them to make sure that all routers are set up the same, as far as the way they use “enable” commands, etc.

The first thing you want to do is take that information from your network admin, and then test each one manually. Telnet (or SSH if you can get that working) using the IP, login with the user and password, and then run the enable command, and look at the strings that are responsed back to you. Every router has a name like

company-router-123>

where the > is the prompt. You need to jot down this name to go along with the IP address. Now you can get fancy later and have your network admin set that name up in DNS and then you can just have a list of names, but start with IP addresses first.

Now, here comes the developing part. A long long time ago, right when .NET hit the airwaves, I created a class library called Winsock.Telnet so I could use it. Named it Winsock because I was a VB6 developer and I used the Winsock control to do telnets within my programs, so it just made sense. I still use this library today, and I do have the source code to it somewhere buried on a backup DVD or server in my apartment, and finding it would just be a wasted effort at this point, but the class library works, so that is what matters. I use this class library to do my telnets. (To do SSH I have used WeOnlyDo’s .net SSH Client – Chris Super blogs about how to run SSH on your network yet still use telnet for a specific purpose – such as this). You can get my Winsock library here.

Here is the guts of the main method to log a config from a Cisco router. Steps are easy. Connect, login, enable, run the TFTP command, send in the TFTP address, and a path , then exit. The second half is extra credit. I actually set up a SVN repo to the directory on the server that I TFTP the configs to, do a SVN diff, and if different, I email the changes to the network admin. But everything up the “exit” command would get you buy. The Sleep(1) function just waits for a second, which with telnet you need to do, so you don’t overrun your self. I have included the methods to do the SVN diff.

 

        private static void LogRouterConfigTelnet(string deviceName, string ipAddress, string enablePassword)
        {
            _connectorTelnet = new WinsockTelnet.Winsock(ipAddress, 23, 60);
            _connectorTelnet.Connect();
            _connectorTelnet.WaitAndSend("Username:", _username);
            _connectorTelnet.WaitAndSend("Password:", _password);
            _connectorTelnet.WaitAndSend(deviceName + ">", "enable");
            _connectorTelnet.WaitAndSend("Password:", enablePassword);

            Sleep(1);

            _connectorTelnet.SendAndWait("copy run tftp", "[]?");
            _connectorTelnet.SendAndWait(_tftpAddress, "?");
            _connectorTelnet.SendAndWait("routers/" + deviceName + "/" + _filename, deviceName + "#");
            _connectorTelnet.SendMessage("exit");
            _connectorTelnet.Disconnect();

            // copy over svn copies, delete from root folder
            File.Copy(@"C:TFTP-Rootrouters" + deviceName + @"" + _filename, @"c:tftp-sourcerouters" + deviceName + ".txt", true);

            // do svn diff
            string diff = SVNDiff(deviceName + ".txt");

            if (!string.IsNullOrEmpty(diff))
            {
                System.Console.WriteLine(diff);

                // if different, commit to svn, email diffs
                SVNCommit(deviceName + ".txt");

                EmailDiff(deviceName, diff.Replace(Environment.NewLine, "<br>").Replace("n", "<br>"));
            }


        }

        private static string SVNDiff(string filename)
        {
            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = @"C:Program FilesSubversionbinsvn.exe";
            psi.WorkingDirectory = @"C:tftp-sourceRouters";

            psi.Arguments = String.Format("diff {0}", filename);

            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.CreateNoWindow = true;

            Process p;
            String output;

            p = Process.Start(psi);

            try
            {
                output = p.StandardOutput.ReadToEnd();
                p.WaitForExit();

            }
            finally
            {
                // shouldnt happen but lets play it safe
                if (!p.HasExited)
                {
                    p.Kill();
                }
            }

            return output.Trim();

        }

        private static void SVNCommit(string filename)
        {
            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = @"C:Program FilesSubversionbinsvn.exe";
            psi.WorkingDirectory = @"C:tftp-sourceRouters";

            psi.Arguments = String.Format("commit -m "config changed" {0}", filename);

            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.CreateNoWindow = true;

            Process p;
            String output;

            p = Process.Start(psi);

            try
            {
                output = p.StandardOutput.ReadToEnd();
                p.WaitForExit();

            }
            finally
            {
                // shouldnt happen but lets play it safe
                if (!p.HasExited)
                {
                    p.Kill();
                }
            }

        }

        static void EmailDiff(string deviceName, string diff)
        {

            MailMessage msg = new MailMessage();
            msg.To = "networkadmin@yourcompany.com";

            msg.From = "ciscoconfig@yourcompany.com";
            msg.Subject = "Cisco Config Changed - " + deviceName;
            msg.Body = diff;
            msg.BodyFormat = MailFormat.Html;
            SmtpMail.SmtpServer = "yourmailserver";

            try
            {
                SmtpMail.Send(msg);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }

        }

        static void Sleep(int seconds)
        {
            System.Threading.Thread.Sleep(seconds * 1000);
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }


 So, you can see, taking a little time to create a small program to do this is not really tough. And your IT department will be happy. It will also give you a reason to use things in .NET that you might not use everyday, especially if you are a Web Programmer, and also you will learn a little more about IT things (routers, networks, etc).

Note: you can see the code isn’t the prettiest, and really doesn’t need to be. There is some duplication yeah, and some hardcoded paths. If you are worried, release a 2.0 version with all that in the App.Config and refactor out a couple of methods. Or if you get really good, create a library called Utils or something with all the common functions you are going to use, like for calling processes, etc.

 

Technorati tags: , , , , , , , , , ,
Categories
Geeky/Programming Product Reviews

Pownce: really needed?

Yesterday I got an invite to Pownce (http://pownce.com/ScaleOvenStove/). Pownce is the next thing from Kevin Rose, the founder of Digg. It is an application that works on the web and desktop. You can send messages, links, files to your friends and to public. It is kind of like twitter but with more options, and there is the desktop aspect.

image 

The desktop app is written on top of AIR (Adobe Integrated Runtime) – which is in experimental beta right now, which kind of sucks, but yeah, it works. One thing that I really don’t like, is that you can’t minimize the app to the systray, its open, or closed. Doesn’t seem like their are many options in the app as well. Basically a suped up flash app that runs on your desktop.

I have some friends on there, that I invited, that really aren’t my friends (that is the way I got an invite) – I have a couple invites left if someone that I would actually send stuff to would like one.

This app reminds me of an app I started a while ago, PeTuPe – to share files and such with your friends (which maybe one day I will just make and complete it). Just another p2p app/IM client. I don’t see any real benefit at this point, but maybe something more will come out of it.

Technorati tags: , , , , , , , ,
Categories
Geeky/Programming Product Reviews

More Online TV: VeohTV – Best Yet

Since cutting cable, I have tried many different online TV programs. Democracy TV (soon to be Miro), TVTonic, Amazon Unbox, ITunes Video Store, Joost, etc. I finally got a beta invite to VeohTV the other day, and installed it Friday night. Since I am sunburned like crazy, I am holed up in my apartment anyway’s, so why not fool around with it.

It is the best online TV program I have used yet. First, it works with my Media Center remote. Second, it has all the shows from the major networks, the ones they have online. ABC, FOX, NBC, CBS, etc – you can watch the episodes they have online but through Veoh, which is a better interface.

They have an extensive list of other programs as well, short podcasts and small shows. DrinksTV.com as an example. (TVTonic does have a beer channel though :))

I am now watching Jericho (from CBS), all the episodes, which is pretty cool, and there is minimal advertising. One thing with all online TV networks/programs is this, and it is a huge pet peeve, the advertisements volume is usually WAY higher than the show, its just ridiculous, I just end up muting it then, what do they think?

I have noticed as well, as the major networks shows are different quality and different options. ABC/NBC seem good, CBS on the other hand, choppy, lower res, and harder to skip segments. FOX seems OK as well.

I recently redid my desktop computer with Vista Ultimate (with Media Center) and I have that directly hooked up to my TV, which I have never done before, and I really like the experience – the 360 has the extender, but direct connection is much better. I have decided that I will use my 360 for Video downloads from their marketplace, and for games, and forget the extender for now. If I had two TV’s in different rooms, then maybe it would be another story.

Technorati tags: , , , , , , , , , , , , , , , , , ,
Categories
Geeky/Programming Product Reviews

HowTo: Upload and Tag pictures with Flickr, the right way

Flickr. Great online app, but uploading pictures through their website is pain at best. Other online sites have better uploading tools built right into their websites (MySpace, Facebook, etc). There is also the ability to tag your photos when you upload, but from what I have seen from all my friends photos, they aren’t tagging them correctly.

First, if you do any kind of serious uploading (more than 3-4 photos at a time), you want to download the Flickr Uploadr Tool onto your computer, then run it. What I have seen, in Windows XP, the Explorer Shell integration (you know, highlight one or more photos, right click, “Send to Flickr…”) always works, whereas on Vista, it seems to work off and on. You can either do the right click, or run the tool from the shortcut, and you should see a screen like this:

image 

Now, you can either add more photos, remove photos, or click “Upload”

image

Here you can set tags (we will get to that later), add to a existing set, create a new set, and set the permissions. As far as permissions go, its all or none type thing. Usually I have a ton of photos from a day/weekend and some are private, some are public. What I do in this scenario is upload the public ones first (creating a set) then uploading the private ones after, adding to the set I just created, but making the pictures private.

Tags. Ok, Flickr’s tagging system is cool, but the biggest thing I have seen is people put tags for something with two or more words, but they put the tags wrong, so it shows up goofy on their pictures.

Here is an example. I just uploaded pictures from camping, and one of my tags was “Silver Falls State Park”. When I added the tag, I had the quotes around it, so the pictures have that whole phrase as a tag. If you leave off the quotes, the picture will have 4 tags: “Silver” “Falls” “State” “Park” – which doesn’t really make much sense. My pictures don’t have any silver in them 🙂

So, there you have it. The How To on uploading and tagging your Flickr uploads!

Technorati tags: , , , , , , , , , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS: RunningValue() to get Cumulative Totals

If you have a SSRS (SQL Server Reporting Services) report, and you want to aggregate values on a column, the function RunningValue() is what you would use.

The function takes 3 parameters, the field you want to “run the value” on, the function you want to run on it (Sum for example), and then the scope. The scope can be “Nothing” ex: RunningValue(Fields!Blah.Value,Sum,Nothing) but where the scope really comes into play is when you want to group by given fields in your row.

So if you want to group by say, salesperson, and run their values by day of their sales for a month to date total, you would have

 

Salesperson           Date           Daily Sales          Month To Date Sales
——————————————————————————-
Homer Simpson   
                              07/01/2007       50                            50
                              07/02/2007       43                            93
Bart Simpson
                              07/01/2007        5                              5
                              07/02/2007        8                              13

 

How you achieve this is: on the row with the salesperson, there is a group defined if you right click. Look at the group name, by default it is usually table1_detailsGroup or something similar (if you used the wizard).

Then, the column for the Month to Date Sales would have the expression

=RunningValue(Fields!DailySales.Value,Sum,”table1_detailsGroup”)

Where you can see the scope is now defined as the table group name as a string.

Technorati tags: , , , , , ,
Categories
Geeky/Programming Product Reviews

Browser Wars: Handling a Phishing Site

The other day, I got an email from US Bank saying I need to login to their site and change my password, funny though, I don’t bank at US Bank. What’s the deal? A “phishing” attempt was made!

Phishing, according to Wikipedia, is: In computing, phishing is a criminal activity using social engineering techniques.[1] Phishers attempt to fraudulently acquire sensitive information, such as usernames, passwords and credit card details, by masquerading as a trustworthy entity in an electronic communication. eBay and PayPal are two of the most targeted companies, and online banks are also common targets. Phishing is typically carried out by email or instant messaging,[2] and often directs users to give details at a website, although phone contact has been used as well.[3] Attempts to deal with the growing number of reported phishing incidents include legislation, user training, and technical measures.

Woah, ok, long definition. What it means is someone tries to pass themselves off as someone else to jack your passwords.

Anyway’s, since I rarely get phishing emails, I decided to test out how Firefox 2.0 and Internet Explorer 7 (On Vista) compare as far as their phishing filters.

firefox_phish ie_phish

As you can see from the screenshots, Firefox on the left, Internet Explorer on the right.

Firefox puts a gray shade over the webpage, and pops a balloon up saying :”Suspected Web Forgery”. You can ignore, or get the hell out, which brings you to your homepage. You can clearly see in the address bar that the URL Address is not US bank at all, clearly a hoax. You can also report the site as not a phishing site if by some chance it isn’t.

Internet Explorer makes the address bar “red”, I am assuming meaning “stop!” It actually then displays an error message saying that it is a phishing site and gives a brief overview of the meaning of a phishing site. Two options. “Click here to close” (with a green shield, meaning, go, good) and “Continue to this website (not recommended)” with a red shield, stop, bad. Also they display the URL again in the page contents, and allow you to report it as not a phishing site as well.

Which one is the winner here? Hard to say. I think I like Microsoft’s implementation better, for a few reasons. First, they don’t show the actual image of the site like Firefox. Unsuspecting or unfamiliar users might see that US Bank site the way Firefox displays it and say, hey! that looks like it, so it must be OK. Where on IE, they get the error message, say WTF and close out. I like the red address bar on IE as well, and when you are on some sites (ex: Paypal) it is green, which is good as well. One place where Firefox might be better is in the terminology. They call it a “web forgery” where Microsoft calls it a “phishing website”, but to be true to what it really is, Microsoft is correct.

In any event both browsers are doing good in handling fake websites and making sure the users know they are about to get hoodwinked. A year or two ago, people would just blindly hit these sites and put in their username/password, and be taken to the cleaners.

Technorati tags: , , , , , , , , , , , , , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS Exporting Report to Excel – Keep Formatting on Export Round 2

Earlier I blogged about exporting SSRS (SQL Server Reporting Services) Reports to Excel and keeping formatting. Well, my previous solution works, but not always (doh!)

I found in a new report that I created that the formatting was taking on all columns. The solution? Wrap those expressions in CDec() function and it works. It was for some currency and percentage formatted columns.

Nothing can ever be easy!

Technorati tags: , , , , , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS 2005 – Named Sets – TopCount – Static/Dynamic and Ordering

Named Sets in SSAS 2005 (SQL Server Analysis Services 2005) are a good thing. You can set up named sets for things like “Last 3 months” or “Yesterday” which is really nice for end users. The nature of dates is that you can make the MDX dynamic but yet the named set is still “static”.

You can also create sets when running MDX queries, and use them later in queries you run. These are dynamic named sets. The problem is, when you are working on your cube, you cannot define dynamic named sets in the “Calculations” tab. The named sets there are static. If you try to define a named set that say, for instance, tries to get the “Top X Salespeople by Revenue” or something, you can create it, but what happens is when you deploy your cube, the named set gets run for one slice of the cube, usually just the default (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817566&SiteID=1   and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1304964&SiteID=1).

I did read on a site, that in SSAS 2008, you will be able to create dynamic named sets in the cube editor, so that is good.

One other thing with named sets, if you create a named set that is using TopCount, when you bring it into an Excel pivot table, you will see the Top X values you are looking for (sliced incorrectly, as I stated above, but still there). Thing is, they are sorted by the SET Expression, not the Numeric Expression. I haven’t found a way to get them to sort by the numeric expression. Example from above, you have the Top 3 Salespeople by Revenue. You want to see that sorted by revenue. If you run a MDX query using TopCount

SELECT
{[Measures].[Revenue]} ON COLUMNS,
{TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])} ON ROWS
FROM
    [MyCube]

you will see it ordered in the query results correctly

John Smith           $1000
Amanda Jones      $950
Homer Simpson     $500

etc

Now if you take the TopCount out –

TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])

and create a named set in your MDX Script of the cube editor

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT([Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;        

or even like this as the forums above say

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT(Existing [Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;  

When you pull that named set into your pivot table in Excel, they wont be ordered by Revenue, instead, by name

Amanda Jones      $950
Homer Simpson     $500
John Smith           $1000

Funky stuff. My recommendation – if you are going to be needed dynamic named sets like the ones created above, just create a SSRS (SQL Server Reporting Services) report instead, or wait until SSAS 2008 comes out.

Note:

Now, the above is just what I have found. There might some crazy way that I am unsure of in MDX or somewhere to get this to work, but I can’t find anything that would let me do what I want.

Technorati tags: , , , , , , , , , , , , , , ,
Categories
Geeky/Programming SQLServerPedia Syndication

SQL Server: SELECT Machine, Computer, Instance Names

to get the instance name: SELECT @@ServerName

to get the machine name of the server: SELECT SERVERPROPERTY(“MachineName”)

to get the machine name of the computer running the query: SELECT host_name()

one tip: cast the serverproperty as a varchar if you want to use it in a string or you will get errors in your stored procs/queries.

Technorati tags: , , , , , ,