Posts Tagged 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

%d bloggers like this: