Archive for category Business Intelligence

Practical Recursion – Part 2

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. Here in part 2 of the series is an example of how recursion can be used to seed a date table.

Also See…

Practical Recursion – Part 1

Recursive CTE vs While Loop – Performance Analysis

DECLARE

     @StartDate DATETIME

     , @EndDate DATETIME;

SELECT

     @StartDate = ‘1970/01/01’

     , @EndDate = ‘2055/12/31’;

WITH

[Dates] AS

(

     SELECT

          CAST(CONVERT(VARCHAR(8), @StartDate, 112) AS INT) AS [DateId]

          , CAST(@StartDate AS DATE) AS [Date]

          , CAST(@StartDate AS DATETIME) AS [DateTime]

          , DATEPART(YEAR, @StartDate) AS [Year]

          , DATEPART(QUARTER, @StartDate) AS [QuarterId]

          , ‘Q’ + CAST(DATEPART(QUARTER, @StartDate) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH, @StartDate) AS [MonthId]

          , DATENAME(MONTH, @StartDate) AS [Month]

          , DATEPART(DAY, @StartDate) AS [DayId]

          , DATEPART(DAYOFYEAR, @StartDate) AS [Day]

 

     UNION ALL

 

     SELECT

          CAST(CONVERT(VARCHAR(8), DATEADD(DAY, 1, D.[DateTime]), 112) AS INT) AS [DateId]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATE) AS [Date]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATETIME) AS [DateTime]

          , DATEPART(YEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Year]

          , DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS [QuarterId]

          , ‘Q’+CAST(DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [MonthId]

          , DATENAME(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [Month]

          , DATEPART(DAY,DATEADD(DAY, 1, D.[DateTime])) AS [DayId]

          , DATEPART(DAYOFYEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Day]

     FROM

          [Dates] AS D

     WHERE

          D.[DateTime] < @EndDate

)

–INSERT INTO

     [Date]

          ([DateId], [Date], [DateTime], [Year], [QuarterId], [Quarter], [MonthId], [Month], [DayId], [Day])

SELECT

    [DateId] AS [DateId]

    , [Date] AS [Date]

    , [DateTime] AS [DateTime]

    , [Year] AS [Year]

    , [QuarterId] AS [QuarterId]

    , [Quarter] AS [Quarter]

    , [MonthId] AS [MonthId]

    , [Month] AS [Month]

    , [DayId] AS [DayId]

    , [Day] AS [Day]

FROM

    [Dates] AS D

ORDER BY

    D.[DateId]

OPTION

    (MAXRECURSION 32767);

Advertisements

, , , , , , , , ,

1 Comment

Practical Recursion – Part 1

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. To kick off the series, this post is a recursive CTE that I’ve used in a number of situations for compiling transactional data into dimensional records. The example is based around member eligibility, but it can be easily modified to convert any transactional data into dimensional data.

Also See...

Practical Recursion – Part 2
Recursive CTE vs While Loop – Performance Analysis

 

WITH

[MemberEligibilityHistory] AS

(

     SELECT

          M.[MemberId] AS [MemberId]

          , M.[Member] AS [Member]

          , M.[FromDate] AS [MemberEligibilityPeriodStartDate]

          , M.[ToDate] AS [MemberEligibilityPeriodEndDate]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId]) AS [Order]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId] DESC) AS [ReverseOrder]

     FROM

          [MemberEligibility] AS M

)

, [MemberEligibility] AS

(

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , DATEDIFF(DAY, MEH.[MemberEligibilityPeriodEndDate], MEH.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , 0 AS [GapDays]

     FROM

          [MemberEligibilityHistory] AS MEH

     WHERE

          MEH.[Order] = 1

     UNION ALL

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , ME.[MemberEligibilityStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , ME.[CoveredDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodEndDate], ME.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , ME.[GapDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodStartDate], MEH.[MemberEligibilityPeriodEndDate]) AS [GapDays]

     FROM

          [MemberEligibility] AS ME

     INNER JOIN

          [MemberEligibilityHistory] AS MEH ON

               ME.[MemberId] = MEH.[MemberId]

               AND ME.[Order] + 1 = MEH.[Order]

)

 

SELECT

     ME.[MemberId]

     , ME.[Member]

     , ME.[MemberEligibilityStartDate]

     , ME.[MemberEligibilityEndDate]

     , ME.[CoveredDays]

     , ME.[GapDays]

FROM

     [MemberEligibility] AS ME

WHERE

     ME.[ReverseOrder] = 1

ORDERBY

     ME.[Member];

, , , , , , , , , , ,

Leave a comment

SSRS Alternating Background Issue

There is a very common issue with SSRS and background expressions that frequently stumps developers. The issue occurs when using a matrix, and has to do with how the RunningValue function works.

As you all know, when it comes to a matrix you can’t use the simple expression below (exmaple 1) to perform alternating backgrounds. This is because a matrix is a pivot of your dataset and thus the columns in the column groups represent rows in the dataset. So when SSRS evaluates background expressions for textboxes in the columns of a column group, those textboxes occur on different rows and thus your each column in the column group has a different alternating background. Experienced developers will recognize this quickly and switch to using the RunningValue function to solve this problem (example 2).

Example 1:

=IIf(RowNumber() Mod 2 = 0, “Alternate Color”, “Color”)

Example2:

=IIf(RunningValue(Field!RowGroup.Value, CountDistinct, “Table Scope”) Mod 2 = 0, “Alternate Color”, “Color”)

Normally this should do the trick, but occasionally an additional issue crops up. Sometimes the background color will span more than the row it should, continuing on into the next row. The root cause of this problem is that RunningValue ignores Nothing (Null) values. Why would we have Nothing (Null) values though? We have Nothing (Null) values when our dataset is a Ragged Matrix (Irregular Matrix) which creates Nothing (Null) values when its pivoted to the SSRS Rigid Matrix (Regular Matrix). A ragged matrix is one where each group does not have the same number of items.

Example Ragged Matrix:

Group A Item 1

Group B Item 1

Group B Item 2

When the Ragged Matrix is pivoted to a Rigid Matrix (a matrix where ever group is expected to have the same number of items), you end up with nulls for the missing items.

Rigid Matrix Example from Pivotted Ragged Matrix:

Group A      Item 1      Null

Group B      Item 1       Item 2

So when SSRS evaluates the background expression for a textbox in the matrix that has a Nothing (Null) value from the pivoting, it evaluates to the same background expression as the previously evaluated textbox. This is because the RunningValue function ignores the Nothing (Null) value. The RunningValue function does not treat the Nothing (Null) value as a change from the previous value it is currently holding. Thus if the previous value was on the previous group (row) then the background expression for the textbox on row 2 is the color of row 1. The solution to all this is a custom report function that does a running value but treats Nothing (Null) values as a change in the value from the previous value. Below is the code to do this along with an example expression of its use.

Example Background Expression Using Custom Code:

=IIf(Code.CountDistinctRunningValue(Fields!RowGroupItem.Value) Mod 2 = 0, “Alternate Color”, “Color”)

NOTE: The below code contains a <> on line 11 that may be pasted as &lt ; &gt ; which will need to be changed back to <> after pasting.

Custom Report Code:

Public Dim intRowNumber As Int32 = 0
Public Dim varPreviousValue As Object

Public Function CountDistinctRunningValue(ByVal o As Object) As Int32

If intRowNumber = 0 Then
intRowNumber = 1
varPreviousValue = o

Return intRowNumber
End If

If (IsNothing(varPreviousValue) And Not IsNothing(o)) _
Or (Not IsNothing(varPreviousValue) And IsNothing(o)) _
Or varPreviousValue <> o Then

intRowNumber = intRowNumber + 1
varPreviousValue = o

Return intRowNumber

End If

Return intRowNumber

End Function

, , , , , , ,

Leave a comment

Microsoft Simplicity Strategy

New blog post about Microsoft’s Simplicity Strategy and my take away from the PASS Business Analytics Conference at Magenic.com

http://magenic.com/Blog/MicrosoftSimplicityStrategy

, , , , , ,

Leave a comment

What is Business Intelligence?

Business Intelligence is Intuitive Actionable Data. Intuitive because a person should not need to look at the data for long periods of time to understand the data, and Actionable because the data should empower its user to take an action.

Business intelligence data is an elevator pitch to a user, it needs to be communicated concisely with brevity. If the user can’t understand the data or what they are supposed to do with it inside of thirty seconds, then the data is not intuitive. Just like an elevator pitch business intelligence data should be summarized to communicate the right information quickly. For instance, if I want to know how much I spent on gas this month commuting, I could look at all the transactions on my bank statement adding up the gas station transactions. However, that is not intuitive, as it requires me to dig through a mountain of data to find the desired information. Whereas if I had a summarized statement that gave me the total amount spent that month across a handful of categories, I could immediately spot the gas category and know if it was a good, bad, or ugly month for commuting to work.

In addition to brevity, business intelligence data needs to communicate an idea, not answer a question. A question is always narrow in scope and context, hence the answer to a question is equally narrow in scope and context, and thus an answered question tends to spawn more questions. Likewise an idea will spark additional ideas but a person with an idea is empowered to take an action, where a person with a question is blocked until they have an answer. So by setting a goal of communicating an idea instead of answering a question, business intelligence data can better empower users and avoid the spiral of questions. Going back to my previous example, my monthly bank statement should be designed to communicate the current state of my finances. Whether I’m trying to determine how much my commute costs each month, what is my monthly revenue, or can I afford a new Lexus, my bank statement can answer all these questions because it is communicating the idea of my financial state instead of trying to answer an individual question.

Business intelligence data should also be actionable. A user must be able to look at data and know what action to take. Data that does not drive a user to action is not useful data. When I look at my monthly bank statement I should immediately understand the current state of my finances and be driven to take any action needed to meet my financial goals. This can only occur if my bank statement is designed to be actionable, presenting useful data that clearly, concisely, and with brevity tells me the state of my finances. A list of transactions does not do this. A list of transactions will force me to sit and stare at my statement trying to mentally summarize the data I believe is relevant. On the other hand if my bank statement has summarized data about my total deposits, total withdrawals, the existing balance, and net total, I can easily assess my financial situation and determine my next action toward meeting my financial goals.

Intuitive Actionable Data, that’s business intelligence. By making data adhere to that simple fundamental, we can add value, increase productivity, and ultimately build a better business.

, ,

4 Comments

%d bloggers like this: