This article contains a list of frequently used SQL scripts and snippets for Kepion projects. Most of these scripts have links to fuller articles explaining their process in more detail, but here they are presented together as a quick reference.
Note: Replace all the terms within <>. These scripts may need further customization based on your requirements.
Reference parameters
Get Member ID from parameter selection
SET @<VARIABLEID> =
(
SELECT TOP 1 [MemberID] FROM [D_<DIMENSION>]
WHERE [MemberLabel] = (SELECT TOP 1 Label FROM @<VARIABLE>)
);
Add new dimension member from parameter selection
CREATE PROCEDURE [dbo].[<STORED_PROCEDURE>]
-- Add the parameters for the stored procedure here
@<NEW_MEMBER> nvarchar(200),
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @<NEW_MEMBER> NOT IN (SELECT [MemberLabel] FROM [D_<DIMENSION>])
BEGIN
INSERT INTO [dbo].[D_<DIMENSION>]
([MemberLabel],
[MemberName],
[Input],
[Annotate])
VALUES
(@<NEW_MEMBER>,
@<NEW_MEMBER>,
1,
0)
DECLARE @<NEW_MEMBER_ID> SMALLINT;
SET @<NEW_MEMBER_ID> = (SELECT [MemberID] FROM [D_<DIMENSION>] WHERE [MemberLabel] = @<NEW_MEMBER>)
INSERT INTO [dbo].[H_<DIMENSION>_<HIERARCHY>]
([MemberId],
[ParentMemberId])
VALUES
(@<NEW_MEMBER_ID>,
@<NEW_MEMBER_ID>)
END
END
For more information: Use Parameters in SQL Rule, Add Dimension Members with SQL Rule
Select filter selections
Single dimension member
DECLARE @<VARIABLE> SMALLINT;
SET @<VARIABLE> =
(
SELECT TOP 1 [MemberId] FROM @FilterScope
WHERE [Dimension] = N'<DIMENSION>' and [Hierarchy] = N'<HIERARCHY>'
);
Fiscal year
DECLARE @<VARIABLE> INT;
SET @<VARIABLE> =
(
SELECT [MemberId] FROM [D_Time]
WHERE [Granularity] = N'Year'
AND [MemberLabel] = (SELECT TOP 1 Name FROM @FilterScope WHERE [Dimension] = N'Time' and [Hierarchy] = N'FiscalYear')
);
For more information: Configure SQL Rule
Debugging tools
Generate test tables to select from
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestChangelist')
BEGIN DROP TABLE TestChangelist END
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestScope')
BEGIN DROP TABLE TestScope END
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestSelectionScope')
BEGIN DROP TABLE TestSelectionScope END
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestFilterScope')
BEGIN DROP TABLE TestFilterScope END
SELECT * INTO TestChangeList FROM @Changelist
SELECT * INTO TestScope FROM @Scope
SELECT * INTO TestSelectionScope FROM @SelectionScope
SELECT * INTO TestFilterScope FROM @FilterScope
Rule progress start
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<STEP>'
, @status = 1 -- Started
, @message = N''
Rule progress finish
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<STEP>'
, @status = 2 -- Completed
, @message = N''
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<STEP>'
, @status = 3 -- Warning
, @message = N''
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<STEP>'
, @status = 4 -- Error
, @message = N''
Informational messages
N'<INFO>Top down allocation completed'
N'<SUCCESS>Top down allocation completed'
N'<WARNING>Top down allocation encountered a warning on execution'
N'<ERROR>Top down allocation encountered an error on execution'
View MemberLabels instead of MemberID in fact records
SELECT M.[ModelName], P.[PartitionName], N'SELECT ' + S.[COLUMNS] + N', F.[Value] FROM ' + P.[Source] + N' F ' + J.[INNER_JOIN_CLAUSE] [QUERY] FROM [Partitions] P
INNER JOIN
(
SELECT MD.[ModelID], STRING_AGG(N'INNER JOIN [dbo].[D_'
+ D.[DimensionName] + N'] [' + [ModelDimName] + N'] ON [' + [ModelDimName] + N'].[MemberID] = F.['
+ [ModelDimName] + N'ID]', N' ') [INNER_JOIN_CLAUSE]
FROM [dbo].[ModelDimensions] MD
INNER JOIN [dbo].[Dimensions] D
ON MD.[DimensionID] = D.[DimensionID]
GROUP BY MD.[ModelID]
) J ON J.[ModelID] = P.[ModelID]
INNER JOIN
(
SELECT MD.[ModelID], STRING_AGG(N'[' + [ModelDimName] + N'].[MemberLabel] [' + [ModelDimName] + N']', N', ') [COLUMNS]
FROM [dbo].[ModelDimensions] MD
INNER JOIN [dbo].[Dimensions] D
ON MD.[DimensionID] = D.[DimensionID]
GROUP BY MD.[ModelID]
) S ON S.[ModelID] = P.[ModelID]
INNER JOIN [dbo].[Models] M
ON P.[ModelID] = M.[ModelID]
ORDER BY M.[ModelName], P.[PartitionName]
Find a member in fact records
DECLARE @MemberLabel NVARCHAR(100);
DECLARE @DimensionName NVARCHAR(100);
DECLARE @MemberID NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @MemberLabel = N'FTE-Personnel Pipeline ID#';
SET @DimensionName = N'Metric';
SET @SQL = N'SELECT @MemberID = (SELECT TOP (1)
[MemberID]
FROM [dbo].[D_' + @DimensionName + N']
WHERE [MemberLabel] = @MemberLabel)';
EXECUTE sp_executesql
@SQL
,N'@MemberLabel NVARCHAR(100), @MemberID NVARCHAR(MAX) OUTPUT'
,@MemberLabel, @MemberID OUTPUT;
SELECT
N'IF (EXISTS(SELECT 1 FROM [dbo].[' + T.name + N'] WHERE [' + C.name + N']=' + CONVERT(NVARCHAR(MAX), @MemberID) + N')) PRINT N''' + T.name + N''''
FROM sys.columns C
JOIN (
SELECT [name], [object_id] FROM sys.views
UNION ALL
SELECT [name], [object_id] FROM sys.tables
) T
ON C.object_id = T.object_id
WHERE C.name = @DimensionName + N'ID';
Analyze performance log
SELECT * FROM [dbo].[Log]
SELECT * FROM [dbo].[LogExtension]
SELECT L.[UserID], L.[CreatedOn], L.[Duration], P.* FROM
(
SELECT L.[LogID], [Key], [Value]
FROM [dbo].[LogExtension] LE
INNER JOIN [dbo].[Log] L
ON L.LogID = LE.LogID AND L.Operation = N'Query-Form'
) LE
PIVOT(
MAX([Value])
FOR [Key] IN (
[FormId],
[Query],
[CellCount]
)
) AS P INNER JOIN [dbo].[Log] L
ON L.LogID = P.LogID
SELECT L.[UserID], L.[CreatedOn], L.[Duration], P.* FROM
(
SELECT L.[LogID], [Key], [Value]
FROM [dbo].[LogExtension] LE
INNER JOIN [dbo].[Log] L
ON L.LogID = LE.LogID AND L.Operation = N'Run-Rule'
) LE
PIVOT(
MAX([Value])
FOR [Key] IN (
[SubmissionId],
[RuleId],
[Rule]
)
) AS P INNER JOIN [dbo].[Log] L
ON L.LogID = P.LogID
For more information: Show Rule Progress Message, Show Customized Message with SQL Rule, Enable Performance Logging
Generate dimensions
Number generator
WITH n(n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM n WHERE n < 5000
)
SELECT n FROM n ORDER BY n
OPTION (MAXRECURSION 5000);
Build Line Item dimension
TRUNCATE TABLE [dbo].[D_LineItem];
SET IDENTITY_INSERT [dbo].[D_LineItem] ON;
WITH n(n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM n WHERE n < 50
)
INSERT INTO [dbo].[D_LineItem]
(
[MemberID], [MemberLabel], [MemberName]
)
SELECT n [MemberID], n [MemberLabel], n [MemberName] FROM n ORDER BY n
OPTION (MAXRECURSION 50);
SET IDENTITY_INSERT [dbo].[D_LineItem] OFF;
TRUNCATE TABLE [dbo].[H_LineItem_LineItem];
WITH n(n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM n WHERE n < 50
)
INSERT INTO [dbo].[H_LineItem_LineItem]
(
[MemberId], [ParentMemberId]
)
SELECT n [MemberID], n [ParentMemberId] FROM n ORDER BY n
OPTION (MAXRECURSION 50);
Build 4-4-5 Week dimension
--FIRST CONFIGURE D_TIME FISCAL YEAR TO MATCH THE CUSTOMER FISCAL YEAR
--IN THIS EXAMPLE, WE SET FY2014 STARTING FROM 7/1/2013
DECLARE @FiscalYear_Start INT
,@FiscalYear_End INT;
SET @FiscalYear_Start = 2015;
SET @FiscalYear_End = 2019;
DECLARE @LastDay TABLE
(
FiscalYear INT
,[LastDay] DATETIME
,[LastDayID] INT
)
INSERT INTO @LastDay
SELECT FiscalYear, MAX([Date]), MAX(MemberID)
FROM D_Time DT
WHERE Granularity = N'Day'
AND DayOfWeekName = N'Saturday'
AND MonthOfYearName = N'June'
AND FiscalYear >= (@FiscalYear_Start - 1) * 10000
AND FiscalYear <= @FiscalYear_End * 10000
GROUP BY FiscalYear
DECLARE @FirstDay TABLE
(
FiscalYear INT
,[FirstDay] DATETIME
,[FirstDayID] INT
)
INSERT INTO @FirstDay
SELECT
LD.FiscalYear + 10000
,DT.[Date]
,DT.MemberId
FROM @LastDay LD
INNER JOIN D_Time DT
ON DT.[Date] = DATEADD(DD, 1, [LastDay])
WHERE DT.Granularity = N'Day'
AND LD.FiscalYear / 10000 < @FiscalYear_End
DELETE FROM @LastDay
WHERE FiscalYear / 10000 < @FiscalYear_Start
DECLARE @Base TABLE
(
[Date] [datetime],
[CalendarYear] [int],
[CalendarQuarter] [int],
[CalendarMonth] [int],
[CalendarWeek] [int],
[CalendarWeekday] [int],
[CalendarDay] [int],
[CalendarYearName] [nvarchar](25),
[CalendarQuarterName] [nvarchar](25),
[CalendarMonthName] [nvarchar](25),
[CalendarWeekName] [nvarchar](25),
[CalendarWeekdayName] [nvarchar](25),
[CalendarDayName] [nvarchar](25),
[FinancialYear] [int],
[FinancialDayOfYear] [int],
[FinancialQuarter] [int],
[FinancialDayOfQuarter] [int],
[FinancialPeriod] [int],
[FinancialDayOfPeriod] [int],
[FinancialWeek] [int],
[FinancialDayOfWeek] [int],
[SalesWorkingDay] [bit],
[FinancialDay] [int],
[FinancialYearName] [nvarchar](25),
[FinancialQuarterName] [nvarchar](25),
[FinancialPeriodName] [nvarchar](25),
[FinancialWeekName] [nvarchar](25),
[FinancialDayName] [nvarchar](25)
)
INSERT INTO @Base
SELECT
[Date]
,[Year] [CalendarYear]
,[Quarter] [CalendarQuarter]
,[Month] [CalendarMonth]
,[CalendarWeek] [CalendarWeek]
,[DayOfWeek] [CalendarWeekday]
,MemberId [CalendarDay]
,YearName [CalendarYearName]
,[QuarterName] [CalendarQuarterName]
,[MonthName] [CalendarMonthName]
,CalendarWeekName [CalendarWeekName]
,[DayOfWeekName] [CalendarWeekdayName]
,[DayName] [CalendarDayName]
,FiscalYear [FinancialYear]
,FinancialDayOfYear
,[FiscalYear] + [FinancialQuarter] * 10 [FinancialQuarter]
,ROW_NUMBER() OVER(PARTITION BY FiscalYear, [FinancialQuarter] ORDER BY DC.MemberId) [FinancialDayOfQuarter]
,[FiscalYear] + [FinancialPeriod] * 100 [FinancialPeriod]
,ROW_NUMBER() OVER(PARTITION BY FiscalYear, [FinancialPeriod] ORDER BY DC.MemberId) [FinancialDayOfPeriod]
,[FiscalYear] + 9900 + [FinancialWeek] [FinancialWeek]
,FinancialDayOfWeek
,CASE
WHEN DayOfWeekName IN (N'Saturday', N'Sunday') THEN 0
ELSE 1
END [SalesWorkingDay]
,MemberId [FinancialDay]
,[FinancialYearName]
,N'Q' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialQuarter]), 2) + N' ' + [FinancialYearName] [FinancialQuarterName]
,N'P' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialPeriod]), 2) + N' ' + [FinancialYearName] [FinancialPeriodName]
,N'W' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialWeek]), 2) + N' ' + [FinancialYearName] [FinancialWeekName]
,[FinancialDayName]
FROM
(
SELECT *
,CASE
WHEN [FinancialWeek] > 52 THEN 12
WHEN ((([FinancialWeek] - 1) % 13) / 4 + 1) < 3
THEN ([FinancialQuarter] - 1) * 3 + ((([FinancialWeek] - 1) % 13) / 4 + 1)
ELSE ([FinancialQuarter] - 1) * 3 + 3
END [FinancialPeriod]
FROM
(
SELECT *
,CASE
WHEN (([FinancialWeek] - 1) / 13 + 1) > 4 THEN 4
ELSE (([FinancialWeek] - 1) / 13 + 1)
END [FinancialQuarter]
,[MonthDate] + [FinancialYearName] [FinancialDayName]
FROM
(
SELECT
*
,(FinancialDayOfYear - 1) / 7 + 1 [FinancialWeek]
,(FinancialDayOfYear - 1) % 7 + 1 [FinancialDayOfWeek]
FROM
(
SELECT DT.MemberId
,DT.[Date]
,DT.[Year]
,DT.[Quarter]
,DT.[Month]
,DT.[CalendarWeek]
,DT.[DayOfWeek]
,DT.YearName
,DT.[QuarterName]
,DT.[MonthName]
,DT.CalendarWeekName
,DT.DayOfWeekName
,DT.[DayName]
,F.FiscalYear
,ROW_NUMBER() OVER(PARTITION BY F.FiscalYear ORDER BY DT.MemberId) [FinancialDayOfYear]
,N'FY' + LEFT(CONVERT(NVARCHAR(8), F.FiscalYear), 4) [FinancialYearName]
,LEFT(DT.MemberName, 8) [MonthDate]
FROM D_Time DT
INNER JOIN @FirstDay F
ON DT.MemberId >= F.FirstDayID
INNER JOIN @LastDay L
ON DT.MemberId <= L.LastDayID
AND F.FiscalYear = L.FiscalYear
WHERE Granularity = N'Day'
) DC
) DC
) DC
) DC
TRUNCATE TABLE [dbo].[D_Calendar]
SET IDENTITY_INSERT [D_Calendar] ON
INSERT INTO [dbo].[D_Calendar]
(MemberId
,[MemberLabel]
,[MemberName]
,[Input]
,[Annotate]
,[Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName])
SELECT
CalendarDay [MemberId]
,CalendarDayName [MemberLabel]
,FinancialDayName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Day' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
UNION
SELECT
FinancialWeek [MemberId]
,FinancialWeekName [MemberLabel]
,FinancialWeekName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Week' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
WHERE FinancialDayOfWeek = 1
UNION
SELECT
FinancialPeriod [MemberId]
,FinancialPeriodName [MemberLabel]
,FinancialPeriodName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Period' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
WHERE [CalendarDay] IN (SELECT MIN([CalendarDay]) FROM @Base GROUP BY [FinancialYear], [FinancialPeriod])
SET IDENTITY_INSERT [D_Calendar] ON
TRUNCATE TABLE [dbo].[H_Calendar_Day]
TRUNCATE TABLE [dbo].[HO_Calendar_Day]
INSERT INTO [dbo].[H_Calendar_Day]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Day'
INSERT INTO [dbo].[HO_Calendar_Day]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Day]
TRUNCATE TABLE [dbo].[H_Calendar_Period]
TRUNCATE TABLE [dbo].[HO_Calendar_Period]
INSERT INTO [dbo].[H_Calendar_Period]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Period'
INSERT INTO [dbo].[HO_Calendar_Period]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Period]
TRUNCATE TABLE [dbo].[H_Calendar_Week]
TRUNCATE TABLE [dbo].[HO_Calendar_Week]
INSERT INTO [dbo].[H_Calendar_Week]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Week'
INSERT INTO [dbo].[HO_Calendar_Week]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Week]