Kevin S. Goff: Stages of Data

03-08-2010 - SetFocus BI/SQL Master's Program Tip: Sending an email inside of SSIS to a Gmail account

A student in one of my recent SSIS classes asked if it's possible to send an email to a Gmail account, from inside SQL Server Integration Services.

The short answer is "yes", you can.  Of course, the "how" is always in the details - and there are a few details.

First, we can't use the built-in SSIS email control flow task.  Among other reasons, we need to specify a port number and we need to handle a few other properties for credentials that the send mail task doesn't expose. 

So the answer is to write a script task that does the following:

• Creates an SMTP Client object (and objects for a Mail Message and an Attachment as well)
• Enables SSL
• Sets the SMTP client object credentials to the email credentials for the sender

This example uses a Gmail account for both the sender and recipient.  If the sender were using something other than Gmail, then adjustments would need to be made to account for the sender's email.

Here's the code for the SSIS task, in C#.  (Remember that the very top of the SSIS script would need to add references to the System.Net and System.Net.Mail namespaces).

 

1-17-2010 - SetFocus BI/SQL Master's Program Tip (from student alumnus question): when PREVMEMBER alone isn't enough in MDX

A rather common requirement in OLAP reporting is to show sales for a specific day, and for the previous day. Using AdventureWorks as an example, if we wanted to see sales by city for a specific date (e.g. March 1, 2004) and then show sales for the previous day (as a generic calculation), we can use the MDX PREVMEMBER statement and write the following MDX code:

WITH MEMBER [SalesPriorDay] as
    ([Internet Sales Amount],
     [Date].[Date].CurrentMember.PrevMember) , format_string = 'currency'

select { [Internet Sales Amount], [SalesPriorDay]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

That code will produce the following result set (partial screenshot):

Recently, a student who just graduated from the SetFocus BI Master's Program emailed me and essentially asked me the following: suppose we want the column for the previous sales day to be more intelligent...i.e., suppose that for one dimension member, the 3 days prior had no sales at all, but going back 4 days, sales existed. And for another dimension member, maybe you had to go back two days to get sales, etc.

Essentially, the recent graduate was asking for a "smarter" SalesPriorDay calculation - one that would go back and find the last day where sales existed. He also asked if the result set could show the day.

To write something as generic as possible, we have to first acknowledge that the last prior day where sales existed could theoretically go back to the very first day of the database. So in plain English, we need to do the following:

• Retrieve all the days prior to the "base" date that had sales for the current member (in this case, city).
  • To do this, we need to create a range from the first possible date of sales, up to the day prior to the "base" date    
• From that list of days with sales, grab the last (most recent) date

Well, let's break that up into small pieces: first, to create a range of dates from the first possible date to the date prior to the "base" date, we can use the MDX Range operator (:)

{ [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}

Next, we can use the MDX filter function against that range of dates, to build a smaller set of dates where sales occured for the current member (remember that MDX is smart enough to pick up the context of the current date and each city in the row axis).

filter( {[Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0)

OK, we're close. We'd like to take the last date from the result of this filter - that would represent the last actual date where sales occured, for whatever dimension members we're using in the main query. We can use the MDX TAIL function to grab the last item in the filtered set:

tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1)

So the hard part is over, right? Not exactly. Our goal is to express the measure (Internet Sales Amount) in terms of a dimension member that represents the previous date where we have sales. But doesn't the last line fo code provide the dimension member representing the last actual date? No....remember that TAIL returns a SET, even if we provide a value of 1 for the 2nd parameter. So we have to use the ITEM() function to grab the first member of the result from the TAIL function - even if the results from TAIL only contain one tuple expression:

tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1).Item(0)

Now we can create a "smarter" calculation for Sales Prior Business Day, where that day could be several days or even weeks prior. Here's the entire solution. Note that we also show the actual date as a separate member, using the Name property.

with MEMBER [SalesPriorBusinessDay] as
    ([Internet Sales Amount],
    tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'

MEMBER [Measures].[LastSaleDate] AS
    tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0).name

select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

The results (below) show the sales for the date of March 1, 2004 - as well as the last actual date of sales. So notice that Ballard last had activity on February 27th, and that you have to go back an additional week to show sales for Bendigo

There is one issue with this approach - the extra logic could provide very costly, especially if we're repeatedly filtering on a large number of days. An alternate approach might be this: instead of creating a range that goes back to the first possible day of sales, let's only go back 1 week. If we don't have sales for any point in the prior 7 days, then we'll simply settle for a NULL value. In this case, we can use the MDX LAG function instead:

WITH MEMBER [SalesPriorBusinessDay] as
    ([Internet Sales Amount],
    tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'

MEMBER [Measures].[LastSaleDate] AS
    tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
        [Internet Sales Amount] > 0) ,1).Item(0).name

select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on columns,
    filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on rows
from [adventure works]
where [Date].[Date].[March 1, 2004]

Using LAG will result in the following output:

So in the case of Bendigo, we consider any sales prior to the previous 7 days to be "very old". So the need for specific reporting may be need to be weighed against the consequences of slower performance.

Bottom line, the student was able to take this approach and run with it. I always feel happy when I'm able to give a graduate a hand on a query that he/she is working on, either for a job or just as part of continuing education. I certainly won't say that I'm able to answer every question from every former student - some questions are just too intricate and require that I have the data in front of me. Other cases I might be able to make some suggestions, and/or give them some websites where they might be able to get more information. But on this particular day, I got lucky. :)

At SetFocus, we often add content to our courseware, based on feedback and also based on questions/interactions. So in this instance, our MDX class will now include an example based on what I've covered here.

12-23-2009 - SetFocus BI/SQL Master's Program Tip: Resolving the issue of multiple JOIN statements and multiple aggregations

We've all made big mistakes in our career - and so we all know that one thing worse than making a big mistake, is making a big mistake that goes undetected for a long time!

The following is an excerpt from a lesson plan in our SetFocus SQL Server and Business Intelligence Master's Programs.

Consider the following: suppose we have 3 tables in a SQL Server database: JobMaster, JobHours, and JobMaterials. They represent a simple example of multiple one-to-many relationships. A single job in JobMaster can have multiple timesheet rows (or none at all) in JobHours, and multiple material purchase rows (or none at all) in JobMaterials.

So our tables might look something like this:

 

So there are four possibilities:

• A job can have both hours and material purchases (Job A)
• A job can have hours but no material purchases (Job B)
• A job can have material purchases but no hours (Job C)
• A job (likely a new job) may not have data in either child table (Job D)

Now suppose we need to write a query that retrieves each Job in JobMaster, along with the sum of Hours Worked (from JobHours) and the sum of Material Purchases (from JobMaterials). The result set should show all jobs - even those with no child data, and would look something like this:

JobDescription        TotalHoursWorked    TotalPurchaseAmount

 

Many SQL developers, recognizing that a LEFT JOIN is required to show all jobs, might write the code as follows (below). The code performs a LEFT JOIN between the parent table and two child tables, and aggregates (SUMs) the HoursWorked and PurchaseAmount from the two tables. This produces the result set in the result set window (below).

 

At first glance, the output above appears correct.

• Job B did not have material purchase amounts, so we see a NULL for the aggregation, and the correct number of 26 hours (7 + 7 + 6 + 6) for the aggregation of hours worked.
• Job C did not have hours worked, so we see a NULL for the aggregation, and the correct number of $30,000 ($10,000 + $20,000) for the aggregation of material purchases.
• Job D is a new job and doesn't have any child data, so we see NULLs for both aggregations.

At this point, some might be tempted to "bless" the code and results. Surely, the one Job we didn't test (with both hours and purchase amount) is the simplest scenario, and "has" to be correct, right? :)

Well, you probably see where this is going! Take a closer look at the raw data for Job A.

• The output shows 60 hours, though the raw data contains 30 hours ( 8 + 8 + 7 + 7)
• The output shows $12,000 in purchase amount, though the raw data contains $3,000 worth ($1,000 + $2,000). Talk about double (and quadruple) counting!

Further, we notice something else - there were 4 Job Hour rows for Job A, and the material purchase aggregation was off by a factor of 4.
Additionally, there were 2 Material Purchase rows for Job A, and the Job hour aggregation was off by a factor of 2.

COINCIDENCE? No, it's not a coincidence. As it turns out, when we use multiple JOIN statements (either INNER or OUTER joins), and try to perform multiple aggregations on multiple one-to-many relationships, you risk overstating the aggregations. In this case, the 2 instances of a JOIN between JobMaster and JobMaterials (for Job A) wind up joining 2 instances of Job A to the Job Hours table - thus the double-counting of hours. Likewise, the 4 instances of a JOIN between JobMaster and JobHours (for Job A) wind up joining 4 instances of Job A to the Job Materials table - thus overstating the aggregation of material purchases by a factor of 4.

I know developers who have written the same general code as the code above - and didn't realize until much later (usually, when a user or customer discovered high number) that this was a problem waiting to happen.

OK, so we've uncovered the problem - what's the solution?

Before we look at a solution, we need to realize one thing - just like a single long code listing, it's often difficult (or impossible) to solve a problem in one self-contained SQL statement. In this specific situation, we need to "pre-aggregate" JobHours and JobMaterials independently of each other, in some type of subquery. Then we can JOIN the pre-aggregations to the JobMaster as a 1-1 relationship.

In the code below, we can use the Common Table Expression capability in SQL Server 2005 to create 2 CTEs that pre-aggregate Hours and Materials. Then in the next line of code, we perform LEFT JOINs between JobMaster and the 2 CTEs, and that produces the correct results.

 

 

Of course, there's often more than 1 way to write a query. Some might prefer to write a 2 correlated subqueries, each of which retrieves a single aggregated amount for every job in the outer query (see below). Of course, there are always debates about whether a correlated query might run a bit slower and might take up additional resources. We cover these issues in the Master's Program classes.

 

If there's a moral of this story, it's that sometimes our safest assumption might be the very root of the problem!

12-21-2009 - SetFocus BI Master's Program Tip: Reason #1 (of a list of thousands of reasons) why MDX is important

During our PerformancePoint Server class in the SetFocus Business Intelligence Master's Program, we cover analytic charts and analytic grids - two powerful ways to incorporate image-style output (i.e., output that can fit on a single screen) into a PerformancePoint Server dashboard. We also cover the role of MDX in coding the necessary calculations to support the charts/grids.

One example is the screen shot below, where we create a dashboard page that allows the user to select any product in the Product Hierarchy (either a high level category or a detail level SKU). The page then generates a chart that shows the following:

• A column chart that plots revenue (Dollar Sales) over the last 12 months of available data
• A line chart (on a secondary Y-axis) that shows each product's sales as a % of the product's parent (in the product hierarchy)
• A line chart (also on a secondary Y-axis) that also shows the calculation of product's sales as a % of the product's parent, but for the same time period a year ago

It should be noted that PPS analytic charts are smart enough to plot the percentages on a dual Y-axis, since a % is on a different scale than the revenue measure.

The dashboard page also generates a data grid recap that shows all of the siblings of the selected product (i.e. all of the products that share the same hierarchical parent of the selected product), using the MDX SIBLINGS statement, and the % of parent Sales for by month. The data grid recap also navigates up the product hierarchy from the user selection, to show the same % of Parent calculation for each parent level. So in this example, we're showing the following:

• GamePlace 2 and all its siblings
• Video Games (the parent of GamePlace 2)
• Electronics (the parent of Video Games)

The chart helps to spot upward or downward trends in a product's share, and also to verify any trends based on seasonality over the two year period. The data recap at the bottom provides additional information on other products that share the same parent. A category manager might use this to (among other things) spot product cannibalization, where product sales decrease due to sales of a newer product.

In the chart below, we see that sales for GamePlace 2 took two significant nose-dives in 2005: in July of 2005 and again in September/October 2005. We can check the analytic grid at the bottom to see which products had a % of Parent increase during that same time period.

OK, so where does the MDX come in? Well, first we need to create calculations to support [Pct of Parent] and [Pct of Parent LY]. We can use Analysis Services to place the following MDX calculations into the cube area:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent] AS

     [Measures].[Dollar Sales] /

         ( [Measures].[Dollar Sales],

         [Product].[ByCategory].CurrentMember.Parent)

   

CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent LY] AS

    ( [Pct of Parent],

        PARALLELPERIOD([Time].[YQMD].[Year], 1))

   

What's great about the first calculation is that it will for any level in the product hierarchy. If I run the chart for a product SKU, the MDX code will use the $ Sales for the SKU's parent in the denominator: likewise, if I run the chart for a product subcategory, the MDX code will use the $ sales for the product subcategory's parent in the denominator. So we can re-use this calculation for any product category level.

What's so great about the 2nd calculation is that we can use the MDX function ParallelPeriod to go back one year, and express the first calculation in terms of the "same time period a year ago". So if we're looking at a measure for Feb 2005, this code will give us the measure in terms of Feb 2004. If we're looking at a measure for Q2, 2005, this code will give us the measure in terms of Q2, 2004.

Note how neither calculation receives the time period (in this case, Months along the bottom axis) as a parameter - this is because MDX calculated members do not receive parameters. Instead, they pick up the context in which they're being run. In other words, they are intelligent enough to pick up that they are being run in the chart (i.e. "sliced") by month.

OK, so that covers the calculations for the chart - how about the analytic grid at the bottom? How are we able to show the selected product and its siblings, along with each parent product, all the way up to the top level? Well, we can do the following:

• Create one MDX set of the siblings of the User Selection <<ProductSelection>>.Siblings
• Create a 2nd MDX set that traverses the hierarchy upwards to retrieve all the parents of the User Selection: ascendants( <<ProductSelection>>)
• Use the MDX UNION function to combine these two sets together
• Use the MDX HIERARCHIZE function to re-order the resulting UNION set in the original Product hierarchical order (from top to bottom)

   

SELECT [MostRecent12Months] ON COLUMNS,

    hierarchize(

        union( ascendants( <<ProductSelection>>),

                    <<ProductSelection>>.Siblings ) ) ON ROWS

FROM [Sales]

WHERE [PctOfParent]

   

This is but one of many examples we cover in the MDX and PerformancePoint Server weeks of the SetFocus Business Intelligence Master's Program.

12/4/2009 - SetFocus BI Master's Program Tip: Testing SSAS Roles in Excel

In the SetFocus Business Intelligence Master's Program, we cover the use of security roles in SSAS databases.

SQL Server Analysis Services 2005/2008 both allow you to define roles for when your Windows users (or groups of users) access OLAP data.

As an example, suppose in the AdventureWorks OLAP database, we wanted to associate users with a role that only allows visibility to data for the countries of Australia and the United Kingdom, for the product category of Bikes. You would create a new role in BIDS (or in SSMS), call the role something like TestBikeRoleForAustraliaAndUK, and make the following selections in the Dimension Data area for the Customer and Product Dimensions (next 2 screen shots):

   

   

You can test the role by going to the cube browser in BIDS - the cube browser provides the ability to view the contents of the cube as if you were a user associated with the new role of TestBikeRoleForAustraliaAndUK. But suppose you wanted to test the role in Excel? After all, many users prefer to use Excel to create Pivot Tables and Pivot Charts from OLAP data. But how can we open the cube in Excel and test the role, without needing to actually sign on as a user associated with that role?

It's actually quite easy. In Excel, you can create a connection to an Analysis Services database. You can open the connection properties in Excel, and set the Roles property in the connection string to the role you wish to test (see screen shot below).

   

After making the connection, you can access the PivotTable field list in Excel, and select the entire Customer Geography Hierarchy and Product Category Hierarchy (see screen shot below). However, since you're accessing the OLAP cube based on the Role in the connection string, you'll only see data for Bikes for Australia and the U.K. (screen show below that).

   

   

So as you can see, Excel provides us with the ability to test a role by placing the role in the Connection String property. Pretty slick!

11/8/2009 - SetFocus successfully places 39 candidates in one month!!!

Most people who know me, know that I work as a full-time contract trainer and courseware author for SetFocus, LLC.
SetFocus is a Microsoft Gold Partner that provides learning solutions and career placement services in the Microsoft technology space.

I've been working with them since early 2007, focusing in their Business Intelligence and SQL Server curriculum areas.

SetFocus offers career placement services, which is the subject of this post. Over the last several months, even amidst all the economic problems and challenges, the SetFocus career placement people have been doing an incredible job of placing students in different I.T. positions. These positions have been for software/Business Intelligence Developers, Data Analysts, DBAs, etc.

Well, just a few days ago, I learned that SetFocus placed 39 candidates in October 2009. You can read the press release here.

I know a number of the 39 candidates, because they were students of mine. So believe me, this isn't hype, not one little bit (and those who know me well, know that I'm not one to promote sales hype) - this is the result of some truly remarkable effort and dedication.

I play a role, one of many roles in the SetFocus process, and I'm very proud to do so. We're all raised to believe that business "is business", and therefore shouldn't be personal. However, the people who promote this concept have overlooked something: business isn't conducted by robots and machines, but by human beings. And what could be greater than giving other people a hand to help them get jobs (or better jobs) to support themselves and their families?

From 2004 to 2008, I penned The Baker's Dozen series in CoDe magazine. Job demands and new family demands (now that I'm a daddy, haha) have kept me from writing, but I still think in terms of 13. :)

And so, in celebration of SetFocus pulling off this impressive achievement, here is a special Baker's Dozen: 13 reasons (in no order of importance) why SetFocus could be right for you

1. They utilize video-conferencing for their training. It's normal to have a class of 12-15 people from Seattle to Vancouver to Phoenix to Atlanta. So just because you live three time zones away doesn't mean you have to travel to their corporate location in New Jersey to attend training - they'll come to you. At least 80% of our students participate via our GRID online training system.
   
   When I started with them, I admit I was skeptical about this - but I realized very quickly that their infrastructure for remote training is amazing. After the first day, you realize just how close it is to a "single physical classroom". Click here to learn more about their online training.
2. Their career services/job placement people bust their butts. You can't pull off those kinds of placement numbers without some serious hard work and skill.
3. The general curriculum has a substantial amount of hands-on. There are full project weeks to apply what you've learned.
4. SetFocus has a growing network of business partners, and has been on the Inc. 5000 list.
5. They've been around since 1997. Now, to some people, sheer longevity numbers don't mean very much. At one time I might have agreed - but the last 8 years have taught us that the economic survivors have been far more than just lucky or fortunate.
6. I've worked as a developer/consultant in this industry since 1987. Many times I've met people who graduated from technical schools that claimed they taught "XYZ". Well, I worked with them long enough to be rather suspicious about technical schools in general. All my life, even when I was a contractor and an author, I wanted to be a teacher - and swore that if I ever had a position of influence in a technical school, I'd do things very differently. Well, here I am, as a full-time trainer and curriculum author, and every day for the last 3 years that's been my goal - to arm students to the teeth with information about software tools and how to use them in different business application settings.
7. The trainers and technical support staff are incredibly dedicated - these people are almost constantly putting in time in the evenings and weekends to help students.
8. We don't stand still. We often receive feedback from alumni on ways to improve the program, on content we should add in the future.
9. We prepare people for technical interviews. Several of our instructors have previously been on both sides of the interview table.
10. SetFocus is a Microsoft Certified Gold Partner for Learning Solutions.
11. We will challenge you - we will throw a large amount of content on your plate - we will push you - sometimes you will love us for it, and some days you won't love us for it. Yes, that is the price of success. But as much as we possibly can, we are here to help. (Remember that old line - if something were easy, everyone would be doing it!)
12. The sales reps/placement people will do everything they can to help people get enrolled in the classes that are right for them. On a regular basis, reps will ask people from the training team to talk to potential candidates, either to answer questions, address concerns, etc. The reps will stay in touch with you throughout the training, whether you take a 1 week class or a 2 week class or the full-blown 10 week Master's program.
13. The classes are NOT cookie-cutter style classes. You will get perspectives from people who have used these tools in production.

Now, there are more than 13 reasons why SetFocus could be right for you. But if you are looking for training in different Microsoft technologies, hopefully this will help you in your decision-making.

So again, SetFocus (and to the 39 students), congratulations on this huge achievement. Two thumbs up!

KG