Small cell value suppression in SSAS cube output

I am currently working on project building information system for one of the provincial health registries. Patient data protection and privacy is one of the main requirements that touched all parts of the information system including standard reports and SSAS cubes.

One of the requirements I’ve encountered while working with SSAS cubes was that measure values that contain values less than 6 must be suppressed and displayed to the user as “<6” in order to minimize potential patient re-identification.

My first intuition was to implement this using a calculated measure that would inspect the value of the cube measure and overwrite it with “<6” using the following statement:

CREATE MEMBER CURRENTCUBE.[Measures].[# of Something - All Supressed]
AS

iif([Measures].[# of Something - All] > 0 AND [Measures].[# of Something - All] < 6, "<6", [Measures].[# of Something - All]),
FORMAT_STRING = "0",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Something';

The following picture shows the result using the actual measure from the cube and calculated measure described above side by side. You can see that this approach works very well and values in small cells do get supressed and displayed to the end user as “<6”.

Now, one can argue that this approach is too simple and it doesn’t protect suppressed values from possible re-identification if result contains only one suppressed value. Here is the scenario that describes it:

Column “Actual value” contains the original non-modified values.

Column “Suppressed value” contains suppressed values using calculated member method described in MDX above.

Column “Suppressed value with modified total” shows desired behaviour of total value when result contains only one suppressed value.

Dimension Members	Actual Value	Suppressed value	Suppressed value with modified total
Member 1	10	10	10
Member 2	5	<6	<6
Member 3	10	10	10
Grand Total*	25	25	20

At this point I was unable to find the proper solution that can be implemented within SSAS as calculated measure or perhaps SCOPE statement that would effectively overwrite the value of totals.

Solutions for a limited number of scenarios can be found in the blog maintained by Vinuthan (http://vnu10.blogspot.ca/2011/01/mdx-grand-total-sub-total.html), however he doesn’t provide a generic solution that would work for potential ways of querying the data (filters, intersection of any attributes, etc).

Please let me know if you can help in resolving this puzzle. Your help will be greatly appreciated!

The importance of data type for averaging

Here is a small cautionary tale for those, who use Average function in SQL Server. The data type of the aggregated data element greatly influences the result of AVG aggregate function.

With source data set defined as INT, the result of the straight AVG function will produce 5. However, if we were to convert numbers to float, the result of the aggregation will become 5.5. If we than round up that number, than the result of the aggregation now becomes 6.

I guess the best practice would be to always make sure you convert source data to float before applying AVG aggregation function.

;WITH CTE AS

(
          SELECT 3 AS Rating
    UNION SELECT 4
    UNION SELECT 7
    UNION SELECT 8
)
SELECT 
AVG(Rating) as average_of_int,
AVG(cast(Rating as float)) as average_of_float, 
round(AVG(cast(Rating as float)),0) as average_of_float_rounded
FROM 
CTE

Result:
average_of_int average_of_float       average_of_float_rounded

-------------- ---------------------- ------------------------
5              5.5                    6

Database recovery procedure for corrupted databases

Follow the following procedure to recover corrupter SQL Server database. It is assumed that database is in full recovery mode, full database backup is available and transaction log backups was taken sometime before the failure.

If database has been corrupted due to server hardware failure and is no longer accessible through SQL Server Management Studio or SQL Analyzer, execute the following steps to recover the data.

1. Attempt to backup database transaction log so all activities that happen between last transaction log backup and point of failure can be recovered. If not successful, database can only be restored to the state when last transaction log backup was taken. Execute transaction log backup using the following statement: BACKUP LOG <database name> TO DISK = <path\filename.bak> WITH NO_TRUNCATE

2. Drop/Delete corrupted database from the server.

3. Re-Create database by restoring the last full database backup with NO RECOVERY option (use SQL Server Management Studio).

4. Sequentially restore all transaction log backups taken in the period from the last full database backup and the point of corruption. If Trans log backup attempted in step 1 was successful, restore it last and recover the database.

Quick and simple aggregation of Hierarchical Data in SQL Server

Sometimes data containing hierarchies have to be aggregated and total calculated on different levels. The following script will help archive fast calculation of totals on each level of hierarchy.

INPUT DATA STRUCTURE AND VALUES

0100***Level 1*****$21
0110*****Level 2***$6
0111******Level 3**$1
0112******Level 3**$2
0113******Level 3**$3
0120*****Level 2***$15
0121******Level 3**$4
0122******Level 3**$5
0123******Level 3**$6


-- define codes table with hierarchy

 Declare @Code Table (Code varchar(10), Hierarchy varchar(50), Description varchar(100))

 INSERT @Code (Code, Hierarchy, Description) VALUES ('0100', '1.', 'Total')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0110', '1.01.', 'Total of 0110')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0111', '1.01.01.', 'Value of 0111')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0112', '1.01.02.', 'Value of 0112')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0113', '1.01.03.', 'Value of 0113')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0120', '1.02.', 'Total of 0120')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0121', '1.02.01.', 'Value of 0121')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0122', '1.02.02.', 'Value of 0122')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0123', '1.02.03.', 'Value of 0123')

-- create table to record values for codes

 Declare @CodeValues Table (Code varchar(10), Value money)

 INSERT @CodeValues (Code, Value) VALUES ('0111', 1)
 INSERT @CodeValues (Code, Value) VALUES ('0112', 2)
 INSERT @CodeValues (Code, Value) VALUES ('0113', 3)
 INSERT @CodeValues (Code, Value) VALUES ('0121', 4)
 INSERT @CodeValues (Code, Value) VALUES ('0122', 5)
 INSERT @CodeValues (Code, Value) VALUES ('0123', 6)

-- create temp table that will combine values with code hierarcies and will be used in the rollup

 Declare @TmpValues Table (Code varchar(10), CodeHierarchy varchar(10), Value money)

 INSERT @TmpValues (Code, Value, CodeHierarchy)
 Select
   v.Code,
   v.Value,
   c.Hierarchy
 From
   @CodeValues v
   Inner Join @Code c On c.Code = v.Code

-- Rollup Data

 Select
  c.Code,
  c.Description,
  Sum(v.Value)
 From
  @TmpValues v
  Join @Code c On v.CodeHierarchy Like c.Hierarchy+'%'
 Group By
  c.Code,
  c.Description
 Order By
  c.Code

This method was originally suggested by Scot Smith, Toronto.

Statistical function in T-SQL

In my current project I need to calculate mean, mode, median and percentiles (95th percentile, 99th percentile) of a given data set.

In this article I've put together a summary of what I've discovered during my research.


The following code snippets are taken from the following blog - please visit it for more detailed information:
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq


MEAN Calculation

Mean is another name for average so we can use AVG function to calculate mean.

MEDIAN Calculation

Median is middle point in the set

DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))


INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(9)
INSERT INTO @Temp VALUES(10)
INSERT INTO @Temp VALUES(NULL)


SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 50 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 50 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2

--MODE Calculation


DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(9)
INSERT INTO @Temp VALUES(10)
INSERT INTO @Temp VALUES(NULL)


SELECT TOP 1 WITH ties DATA
FROM   @Temp
WHERE  DATA IS Not NULL
GROUP  BY DATA
ORDER  BY COUNT(*) DESC


Percentile Calculation


Please reference the following blog for details:



http://www.sqlteam.com/article/computing-percentiles-in-sql-server

-- function for floating point division

CREATE FUNCTION dbo.FDIV 

(@numerator float, 

@denominator float)

RETURNS float

AS

BEGIN

RETURN CASE WHEN @denominator = 0.0 

THEN 0.0

ELSE @numerator / @denominator

END

END

GO

-- function for linear interpolation

CREATE FUNCTION dbo.LERP 

(@value float, -- between low and high

@low float,

@high float,

@newlow float,

@newhigh float)

RETURNS float -- between newlow and newhigh

AS

BEGIN

  RETURN CASE 

      WHEN @value between @low and @high and @newlow <= @newhigh THEN @newlow + dbo.FDIV((@value-@low), (@high-@low)) * (@newhigh - @newlow)

      WHEN @value = @low and @newlow is not NULL THEN @newlow

      WHEN @value = @high and @newhigh is not NULL THEN @newhigh

      ELSE NULL

END

END

GO

Declare @TestScores table (StudentID int, Score int)

insert @TestScores (StudentID, Score) Values (1,  20)

insert @TestScores (StudentID, Score) Values (2,  03)

insert @TestScores (StudentID, Score) Values (3,  40)

insert @TestScores (StudentID, Score) Values (4,  45)

insert @TestScores (StudentID, Score) Values (5,  50)

insert @TestScores (StudentID, Score) Values (6,  20)

insert @TestScores (StudentID, Score) Values (7,  90)

insert @TestScores (StudentID, Score) Values (8,  20)

insert @TestScores (StudentID, Score) Values (9,  11)

insert @TestScores (StudentID, Score) Values (10, 30)

--Find the percentile rank of a given score 

-- The derived table makes one scan over the data values to compute some aggregates. The outer select interpolates 

-- between the pth percentile of the nearest samples below and above the given value.

-- The result is 25, 0.5. That means a score of 25 is at the 50th percentile, the median, of the distribution.

declare @val float

set @val = 25

select 

@val as val,

dbo.LERP(@val, scoreLT, scoreGE, 

dbo.FDIV(countLT-1,countMinus1), 

dbo.FDIV(countLT,countMinus1)) as percentrank

from (

select 

SUM(CASE WHEN Score < @val 

THEN 1 ELSE 0 END) as countLT,

count(*)-1 as countMinus1,

MAX(CASE WHEN Score < @val

THEN Score END) as scoreLT,

MIN(CASE WHEN Score >= @val

THEN Score END) as scoreGE

from @TestScores

) as x1

-- Find the percentile (the score) that characterizes a given percentage

declare @pp float

set @pp = .75

select 

@pp as factor, 

dbo.LERP(max(d), 0.0, 1.0, max(a.Score), max(b.Score)) as percentile

from

(

select floor(kf) as k, kf-floor(kf) as d

from (

select 1+@pp*(count(*)-1) as kf from @TestScores

) as x1

) as x2

join @TestScores a

on 

(select count(*) from @TestScores aa

where aa.Score < a.Score) < k

join @TestScores b

on 

(select count(*) from @TestScores bb

where bb.Score < b.Score) < k+1

SQL function to calculate number of work days

Found this code that uses CTE on the internet and modified to my own needs:


CREATE FUNCTION uf_get_num_of_work_days (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @workdays INT;


WITH DATE (Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')
UNION ALL
SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)
SELECT
@workdays = COUNT(*) --CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date], DATENAME(weekday, d1.Date1) [Working Day]
From
DATE d1
Where
(DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday')

RETURN @workdays
END

Script to de-normalize Parent - Child Hierarchy

Parent Child data hierarchies are great for storing hierarchical data, however it becomes a major pain when you need to convert hierarchy structure into a flat view (e.g. to be used in reporting or SSAS cubes). While SSAS provide a build in support for parent-child dimensions, it only allows for one parent attribute per dimension.

I've looked into various techniques to convert parent-child into flat table and found a tool designed by Jon Burchel specifically for normalizing SSAS parent-child dimensions. The original tool is available on codeplex at http://pcdimnaturalize.codeplex.com

Base on this, I came up with my own version - a SQL Script that creates a SQL Server view for each lookup table in the database that contains parent-child hierarchy and converts it into a flat hierarchy with up to 3 levels.

The script assumes that lookup tables have "lu_" prefix. And it detects parent-child structure by searching for a column that starts with name "parent_".


Declare @Sql varchar(8000)


Declare @SrcTableName varchar(250), @AttributeName varchar(250)


Declare Cur Cursor For
Select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like 'lu_%' and COLUMN_NAME like 'parent_%'
Open Cur


Fetch Next From Cur Into @SrcTableName


While @@FETCH_STATUS = 0
Begin


Set @AttributeName = SUBSTRING(@SrcTableName, 4, 250)


Set @Sql =
'
CREATE VIEW [dbo].[vw_naturalized_' + @SrcTableName + '] AS
WITH 
PCStructure(Level, [parent_' + @AttributeName + '_id], [' + @AttributeName + ' Name_KeyColumn], [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + ' 03_KeyColumn])
AS
(
SELECT 
3 Level, [parent_' + @AttributeName + '_id], [' + @AttributeName + '_id], [' + @AttributeName + '_id] as [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + '_id] as [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + '_id] as [' + @AttributeName + ' 03_KeyColumn] 
FROM 
[dbo].[' + @SrcTableName + '] 
WHERE [parent_' + @AttributeName + '_id] IS NULL OR [parent_' + @AttributeName + '_id] = [' + @AttributeName + '_id] 

UNION ALL 

SELECT 
Level + 1, e.[parent_' + @AttributeName + '_id], e.[' + @AttributeName + '_id], 
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 01_KeyColumn] END AS [fetal_therapy 01_KeyColumn], 
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] WHEN 3 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 02_KeyColumn] END AS [' + @AttributeName + ' 02_KeyColumn],
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] WHEN 3 THEN e.[' + @AttributeName + '_id] WHEN 4 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 03_KeyColumn] END AS [' + @AttributeName + ' 03_KeyColumn] 
FROM [dbo].[' + @SrcTableName + '] e 
INNER JOIN PCStructure d ON e.[parent_' + @AttributeName + '_id] = d.[' + @AttributeName + ' Name_KeyColumn] AND e.[parent_' + @AttributeName + '_id] != e.[' + @AttributeName + '_id]
)


select 
Level4Subselect.*
from PCStructure a, 
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 03_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 03_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 03_' + @AttributeName + ' Order Num_KeyColumn], Level3Subselect.*
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 02_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 02_' + @AttributeName + ' Order Num_KeyColumn] , Level2Subselect.*
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 01_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 01_' + @AttributeName + ' Order Num_KeyColumn], CurrentMemberSubselect.* 
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] as [original_' + @AttributeName + '_id], [' + @AttributeName + '_name] [original_' + @AttributeName + '_name], [parent_' + @AttributeName + '_id] [original_parent_' + @AttributeName + '_id], [' + @AttributeName + '_order_num] [original_' + @AttributeName + '_order_num]
from [dbo].[' + @SrcTableName + '] b) CurrentMemberSubselect
) Level2Subselect
) Level3Subselect
) Level4Subselect
where
Level4Subselect.[' + @AttributeName + ' 03_KeyColumn] = a.[' + @AttributeName + ' 03_KeyColumn] and
Level4Subselect.[' + @AttributeName + ' 02_KeyColumn] = a.[' + @AttributeName + ' 02_KeyColumn] and
Level4Subselect.[' + @AttributeName + ' 01_KeyColumn] = a.[' + @AttributeName + ' 01_KeyColumn] and
Level4Subselect.[original_' + @AttributeName + '_id] = a.[' + @AttributeName + ' Name_KeyColumn]
GO
'
Print @Sql


Fetch Next From Cur Into @SrcTableName
end


close cur
deallocate cur