View Change Tracking Settings

Statements to reveal Change Tracking settings.  This query does not require VIEW DATABASE STATE permission, which is required to display the database properties page in SSMS.

select 
	[database_name] = DB_NAME(database_id) 
	,*
from  sys.change_tracking_databases

 

Populating Date Dimension with Fiscal Periods

A standard method for populating a date table with fiscal periods will always come to grief over exceptions.  This post includes a procedure that should be customisable enough to cater for most variations on fiscal periods – provided all periods contain a whole number of weeks.

Fiscal Periods

… do vary.

  • A 4-4-5 week pattern is standard… except when adjustments are made for busy months.
  • Normally a quarter contains three months… except when a company is adjusting its financial year end.

The procedure detailed below takes a few parameters which specify the fiscal period schema:

  • FirstDayOfFiscalYear  : the day it all starts, which also determines the first-day-of-week.
  • WeeksPerMonthPattern : a csv list stating the number of weeks in each month.  e.g.  4,4,5,4,4,5,4,4,5,4,5,4.    The procedure defaults to 4-4-5 throughout the year.
  • MonthsPerQuarterPattern : a csv list stating the number of months per quarter.  defaults to 3,3,3,3 so only needs overriding in those exceptional circumstances.

There is some validation to ensure that the numbers of months, quarters and weeks align.  There’s a “checksum” NbrOfWeeks parameter which defaults to 52; set a value if 53 weeks applies – or for fiscal years with other numbers of weeks.

Columns to Update

A date table might have any number of specialised fiscal columns to update – week and month numbers, start and end date keys, overall period counts.  Therefore the procedure provides a simple but flexible way of specifying an update clause using placeholders for values to be inserted.

  • TableToUpdate : name of table
  • DateKeyColumn : The Date-key column of the TableToUpdate. Column must be integer conversion of date format 112 (yyyymmdd).
  • ColumnUpdatePattern :  The contents of a SET clause as it would appear in a an UPDATE statement, with placeholders.  E.g.  “FiscalMonthNumber={m}, FiscalQuarterNumber={q},… FiscalMonthStart={mst},…”. Available placeholders:
    • {year} : the value of @FiscalYearName
    • {d}, {w}, {m}, {q}, {s} : day, week, month, quarter and semester numbers.
    • {wst}, {mst}, {qst}, {sst} : week, month, quarter, semester start date key
    • {wend}, {mend}, {qend}, {send} : week, month, quarter, semester end date key

Procedure ai_GenerateFiscalCalendar

This procedure generates table update statements for a single financial year.

Fiscal Calendar Update Generator.sql

Example Call

This call uses a specialised week-per-month pattern.

DECLARE @ColumnUpdatePattern varchar(1000) = 'Fiscal_year={year}, Fiscal_quarter={q}, ' +
    'Fiscal_period={m}, Fiscal_Week={w}, ' +
	'Fiscal_week_overall_nbr=(1161 + {w}), Fiscal_period_overall_nbr=(1037 + {m}), ' +
	'[quarter_Fiscal_of_year_end_date_key] = {qend}';
EXEC ai_GenerateFiscalCalendar    
	@FirstDayOfFiscalYear='2017-04-30',
	@FiscalYearName = '2017',
	@TableToUpdate = 'dbo.DimDate',
	@DateKeyColumn = 'date_key',
	@ColumnUpdatePattern = @ColumnUpdatePattern,
	@WeeksPerMonthPattern = '4,4,5,4,4,5,4,5,4,4,4,5';

A summary is output to the messages tab so that the periods can be checked.

FISCAL YEAR: 2017
START DATE: 2017-04-30 Sunday
TOTAL DAYS: 364
TOTAL WEEKS: 52
MONTHS:
 1  2017-04-30 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
 2  2017-05-28 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
 3  2017-06-25 Sun  35 days  5 weeks  Qtr: 1  Sem: 1
 4  2017-07-30 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
 5  2017-08-27 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
 6  2017-09-24 Sun  35 days  5 weeks  Qtr: 2  Sem: 1
 7  2017-10-29 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
 8  2017-11-26 Sun  35 days  5 weeks  Qtr: 3  Sem: 2
 9  2017-12-31 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
10  2018-01-28 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
11  2018-02-25 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
12  2018-03-25 Sun  35 days  5 weeks  Qtr: 4  Sem: 2

The update statements (output as rows) are created by simple concatenation of the TableToUpdate, ColumnUpdatePattern and DateKeyColumns specified.   One update is created for each day in the fiscal year.

UPDATE dbo.DimDate SET Fiscal_year=2017, Fiscal_quarter=1, Fiscal_period=1, Fiscal_Week=1, Fiscal_week_overall_nbr=(1161 + 1), Fiscal_period_overall_nbr=(1037 + 1), [quarter_Fiscal_of_year_end_date_key] = 20170729 WHERE date_key = 20170430;

-- 364 update statements generated.

 

A Procedure for generating Data Warehouse Update code

Background

Transactional tables exist in Database A.   These tables have partner archive tables in the same or another database.  For each insert or change of a value in the transactional database a timestamped new row is entered into the counterpart history table.  Standard archiving, similar to the temporal archiving offered in SQL Server 2016.

The difficulty is that writing the code to populate the archive table (or a Type 2 Slowly Changing Dimension data warehouse table) is complex and error prone.  Particularly since a Merge statement cannot on its own achieve this, so a temporary table has to populated from the Merge Output clause, and so on.

Code Generator

The procedure UTIL_GenerateArchiveProcedureDDL will generate the code required to populate an archive table.  The procedure is in the file attached at the end of this article.

It can generate code for a single specified table or all tables in a schema or the database.

The source table(s) must have an explicit primary key.

The archive table(s) must

  • have same name as the source tables (TODO add option to add _Archive to the name)
  • have temporal date columns named StartDate and EndDate (TODO allow other naming schemes to be used)
  • have the schema as the source tables with the addition of two date columns StartDate (not null) and EndDate (nullable).

The generator and generated code requires this Error reporting procedure.

Examples

Usage and description.

Standard Table (single Primary Key)

-- Typical table with a single primary key.
CREATE TABLE dbo.Products(
	ProductId int NOT NULL IDENTITY PRIMARY KEY,
	ProductName varchar(30) NOT NULL,
	AvailableFromDate smalldatetime NOT NULL,
	IntroducerId int NULL);

Run this with results to grid (it may be necessary to set the SSMS option to display carriage returns in the output grid view).

-- Generate Archive procedure for this table only by specifying table name
-- (if there is a similarly named table in another schema, provide @SourceSchema too)
EXEC UTIL_GenerateArchiveProcedureDDL
	@WarehouseDatabaseName = Archive,
	@SourceTable = 'Products';

This is the generated DDL.

  • The main action occurs in the MERGE statement. This joins all rows in the LIVE table with all current rows, i.e. rows where the EndDate is Null, in the Warehouse table (selected by CTE TargetCTE).
  • The Live and Warehouse tables are joined on all columns of the Primary Key (this example has only one).
  • If there is a match it means the same record (“same” here means having the same Primary Key value) exists in live and warehouse databases.
    • The procedure then compares the sets of non-PK columns in each table using the EXCEPT set operator. If a row is returned, there is a difference in one of the non-PK columns.
    • The THEN clause UPDATES the EndDate column of the warehouse table to obsolete the row in the warehouse table.
    • Note that at this stage no new row can be inserted into the warehouse to represent the new value; that is handled after the MERGE statement.
  • The remaining two MERGE clauses are simpler
    • Not matched by SOURCE:  the record no longer exists in live so the warehouse row is obsoleted.
    • Not matched by TARGET: the record doesn’t exist in warehouse, so a row is inserted.
  • All the SOURCE columns are OUTPUT-ed to a temporary table, together with the MERGE action key.
  • We still need to insert rows for records that have changed value since the warehouse was last updated.  The Merge Output will contain all rows from the full join of the source and target tables.  These can be filtered to only the records that resulted in an UPDATE operation, but even that is too wide since it includes records which have been deleted from the source; we don’t want to insert those again.  Therefore we filter to insert only rows that exist in the merge Source – that is, those where the ouput-ed Source columns are not null.  To test this, the code chooses the primary key (or the first PK column of a compound key) since this cannot be null if the row exists.
  • The Merge and changed-row Insert statements are wrapped in a transaction.
  • Row counts follow.
CREATE PROCEDURE [dbo].[UpdateDataWarehouse]
/*
	** DO NOT EDIT. 
	** Auto-generated by procedure UTIL_GenerateArchiveProcedureDDL ON Jan 22 2017  3:35AM.

	PURPOSE
		This procedure updates an archive warehouse database with the current values in the source database.
		The warehouse database must contain the same tables with the same columns plus temporal (StartDate, 
		EndDate) columns.		

	CODE GENERATOR HISTORY
		18/01/2017 Andrew Ing 1.02.

*/
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY

DECLARE @EffectiveDate datetime;
DECLARE @ObsoletedRowCount int;
DECLARE @InsertedRowCount int;
DECLARE @UpdatedRowCount int;
DECLARE @TableActionSummary varchar(1000);
DECLARE @DebugInfo varchar(2000);

SET @EffectiveDate = GETDATE();


-------------------------------------------------------------
-- TABLE: [dbo].[Products]

  SET @DebugInfo = 'Create Temp Table [#MergeOutput_Products]';
  CREATE TABLE [#MergeOutput_Products] (
    MergeAction nvarchar(10)
   ,[ProductId] int NULL
   ,[ProductName] varchar(30) NULL
   ,[AvailableFromDate] smalldatetime NULL
   ,[IntroducerId] int NULL
  );

  SET @DebugInfo = 'Merge [dbo].[Products]';
  BEGIN TRANSACTION;
  -- Select only Current (EndDate IS NULL) warehouse rows for the comparison.
  WITH TargetCTE AS
    (SELECT * 
     FROM Archive.[dbo].[Products]
     WHERE EndDate IS NULL
    )
  MERGE  TargetCTE AS [Target]
  USING  [dbo].[Products] AS [Source]
    ON
         [Target].[ProductId] = [Source].[ProductId]
  WHEN MATCHED
    AND EXISTS (
      SELECT
          [Target].[ProductName]
         ,[Target].[AvailableFromDate]
         ,[Target].[IntroducerId]
      EXCEPT
      SELECT
          [Source].[ProductName]
         ,[Source].[AvailableFromDate]
         ,[Source].[IntroducerId]
    )
    THEN  -- Record has changed.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY SOURCE
    THEN  -- Record deleted from source.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY TARGET
    THEN  -- New Record.
      INSERT (
        [StartDate]
       ,[ProductId]
       ,[ProductName]
       ,[AvailableFromDate]
       ,[IntroducerId]
       ) VALUES (
        @EffectiveDate
       ,[Source].[ProductId]
       ,[Source].[ProductName]
       ,[Source].[AvailableFromDate]
       ,[Source].[IntroducerId]
       )
  OUTPUT
     $Action AS MergeAction
     ,[Source].[ProductId]
     ,[Source].[ProductName]
     ,[Source].[AvailableFromDate]
     ,[Source].[IntroducerId]
  INTO [#MergeOutput_Products];

  -- Insert new rows for records that have changed (old rows were marked as expired by
  -- the first MATCHED clause. #MergeOutput_ has been populated only with Source columns
  -- but MERGE is a full outer join so exclude rows with a Null primary key column.
  SET @DebugInfo = 'Insert for changed rows [dbo].[Products]';
  INSERT INTO Archive.[dbo].[Products]
    (
      [StartDate]
     ,[ProductId]
     ,[ProductName]
     ,[AvailableFromDate]
     ,[IntroducerId]
    )
    SELECT
      @EffectiveDate
     ,[ProductId]
     ,[ProductName]
     ,[AvailableFromDate]
     ,[IntroducerId]
    FROM [#MergeOutput_Products]
    WHERE
      MergeAction = 'UPDATE'
      AND [ProductId] IS NOT NULL;
    SELECT @UpdatedRowCount = @@ROWCOUNT;

  COMMIT TRANSACTION

  SET @DebugInfo = 'Get Row Counts [dbo].[Products]';
  SELECT
    @ObsoletedRowCount = SUM(CASE WHEN MergeAction = 'UPDATE' THEN 1 ELSE 0 END),
    @InsertedRowCount = SUM(CASE WHEN MergeAction = 'INSERT' THEN 1 ELSE 0 END)
  FROM
    [#MergeOutput_Products];

  SET @TableActionSummary = CONVERT(varchar(19), GETDATE(), 121) + ' [dbo].[Products]
			 ' + CAST(ISNULL(@ObsoletedRowCount, 0) AS varchar(10)) + ' Rows Obsoleted, ' + 
			 CAST(ISNULL(@InsertedRowCount, 0) AS varchar(10)) + ' New Rows Inserted, ' +
			 CAST(@UpdatedRowCount AS varchar(10)) + ' Update Rows Inserted.';
  PRINT @TableActionSummary;
  D ROP TABLE [#MergeOutput_Products];

-- END OF TABLE [dbo].[Products]


END TRY
BEGIN CATCH
  EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo;
  RETURN 5555;
END CATCH
/*
  END OF GENERATED SCRIPT
*/
GO

Many to Many tables

Or any table with just primary key columns.

CREATE TABLE dbo.StudentCourses(
	StudentId int NOT NULL,
	CourseId int NOT NULL,
	CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId)
	);

If the table only has a primary key then it is not possible to have changed values in the same record. The record either exists in both live and warehouse tables (in which case no action is needed) or it exists in only one, in which case it must be inserted or obsoleted in the warehouse accordingly.

If no non-primary key columns are found, the WHEN MATCHED AND EXISTS… clause is omitted from generated code.  Note in the section below that both primary key columns are used in the Merge join statement, and that the Matched And Exists clause is not generated.

...
  WITH TargetCTE AS
    (SELECT * 
     FROM Archive.[dbo].[StudentCourses]
     WHERE EndDate IS NULL
    )
  MERGE  TargetCTE AS [Target]
  USING  [dbo].[StudentCourses] AS [Source]
    ON
         [Target].[StudentId] = [Source].[StudentId]
     AND [Target].[CourseId] = [Source].[CourseId]
  WHEN NOT MATCHED BY SOURCE
    THEN  -- Record deleted from source.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY TARGET
    THEN  -- New Record.
      INSERT (
...

Generating Updates for all Tables

Omitting the SourceTable and SourceSchema parameters will generate DDL for a procedure that will include all tables in the database.   This can be restricted by providing SourceSchema (which will include all tables in the named schema) or SourceTable.

 

UTIL_GenerateArchiveProcedure.sql (3)

Mscomctl32.ocx ActiveX Control

Phenomenally useful. Not supported at 64bit.

Location

Normally:  C:\Windows\SysWOW64.

Versions

I’m using version 6.01.9846 of 9/12/2015

Registering

  • C:\Windows\system32>Regsvr32  c:\windows\syswow64\mscomctl.ocx
  • Unregister C:\Windows\system32>Regsvr32 /u  c:\windows\syswow64\mscomctl.ocx

Troubleshooting

First check References.  If it shows as MISSING, untick it and close the References dialog box.  Compile the code (which will fail, naturally), then re-add the reference.

Problem Library is  correctly (Re)Registered but you get The Expression MouseDown you entered …  error.  Or there is a The OLE server may not be registered error when double-clicking a control in design view:

The operation on the ListViewCtrl object failed.  The OLE server may not be registered.  To register the OLE server, reinstall it.

The OLE server may not be registered

Solution:  open the reference dialog and increase the Priority of the Microsoft Windows Common Controls 6.0 (SP6) entry  as far as it will go (it won’t go higher than the Access Object Library reference). This has immediate effect.

 

 

 

 

Backup Compression

Sample backup compression figures – SQL Server 2012.  The relative sizes and timings are interesting. This was a database with data that compressed well.

  • Uncompressed backup file:  73.4GB.  18 minutes to create
  • Compressed backup:  17.5GB.  About 20 minutes to create
  • Compressing the 73.4GB backup file using 7-zip:  9.7GB.  About 2 hours to compress.

 

Mobile Reports in SSRS 2016

A tiled, dashboard style data presentation derived from DataZen and presentable from SQL Server Reporting Services (traditional rdl reports are differentiated as “Paginated Reports”).

Verdict:  it looks very nice, scales to different displays well, and can be presented in a browser via SSRS.   It lacks any update features, so usage as a dashboard is limited.

SSRS Mobile Report

These are initial comments on Build 1.0.3911.0.

Help

  • There is no help or instructions.

Development

  • developed in a basic application called “SQL Server Mobile Report Publisher” which is not integrated into Visual Studio.
  • The app can publish direct to report server or to file system as a .rsmobile file.
  • .rsmobile is a zipped file containing several xml files (sources, assets, metadata, definition), thumbnail images, a resources subfolder with colour specifications, and a data subfolder.
    • The data subfolder only exists if Enable client data caching is ticked in Settings.  Does this mean that data is fixed at design time and will not refresh?  If not, how long is data cached?
    • If present, the data subfolder contains a file for each data set defined containing the full data set returned, as far as I can see.  The format is JSON – encrypted if Encrypt data stored on the client is selected in Settings.

Data

  • Takes data from standard SSRS Datasets or from Excel.  Presumably the Excel data has to be cached in the .rsmobile file.
  • Parameterised Datasets (without defaults) cannot be used.  However MS stated that these would be supported by general release.   However, there would seem to be little value in supporting parameterised datasets unless the parameters could be exposed to the mobile report users – as query string parameters, for example.  Maybe that is what is being considered.
  • Enable client data caching defaults to ON, Encryption defaults OFF.

Auto Refresh

It would make an attractive dashboard if it could refresh automatically.

  • There is no refresh option in the UI.  The full browser page can be refreshed, which a fair amount of distracting animation.
  • An auto-refresh browser plugin could be used.
  • From tests I can confirm that setting Dataset cache expiry on a scheduled doesn’t cause the report automatically to update.
  • Is there a refresh button in the mobile rendering of it?

Powershell Commands for SQL Server

SQLPS

The SQLPS utility is being replaced by the sqlps module:

Import-Module "sqlps" -DisableNameChecking

To run this interactively against a remote server, enter a remoting session:

Enter-PSSession -ComputerName HOST-NAME -Credential DOMAIN\user

Add a User to a SSAS Role

This solves the problem of adding a domain user to a SSAS database role from a different domain which cannot be done from the SSAS project or via SSMS (XMLA) from outside the domain since these methods attempt to resolve credentials locally.

Import-Module "sqlps" -DisableNameChecking
cd sqlas
cd "database name"
Add-RoleMember -Database "Telecoms Sales" -RoleName "ReportingServices" -M
emberName "DOMAIN\UserName"

(It looks like the SSRS default execution account can’t be used implicitly as with sql datasources)

SSRS Date Range Parameter

Summary

A method for providing a selection of pre-set date ranges plus the ability to enter a custom range of dates.

Until the SSRS improvements in 2016, this will require Nullable date parameter boxes to be used. (Why could not the date boxes be pre-filled with the date range selected?  that would be a good solution. However exhaustive testing has proved this to be impossible.  Two text boxes can be set to the selected dates in a date range but then they can never be edited.  An editable date parameter can be set to show the date range selected and be editable only if the date range parameter has a default but after initial selection the dates shown will never be updated even on selection of a different date range.)

The DateRange data source

This is a table valued function. It has a single parameter (@DateRangeKey) that is set to 0 to return all rows – the full range of options.   Once the choice has been made, set @DateRangeKey to the selected range’s ID and only that row is returned. This is then used to set defaults of SSRS parameters.

 

CREATE FUNCTION [parm].[fnDateRanges](@DateRangeKey int)
/*
	Use to populate SSRS date ranges to provide a set of standard relative date ranges
	(e.g. Last Day, Last Month).

	Note that the DateRangeKey should not alter because this value will be saved in report
	subscriptions and other preset range definitions.

	PARAMETER
		@DateRangeId : set to 0 to return all date ranges.  Use this mode for populating a
			dropdown list parameter named "DateRangeId", Label "Date Range".
	HISTORY
		21/7/2016 AI : Added "Week (starting Sun) to Yesterday".
		03/02/2017 AI:  Corrected calculation of @LastMonday.
		22/02/2017 AI:  + "4 Months to Yesterday".
		27/03/2017 AI: Similar correction to calculation of @LastSunday.
*/
RETURNS @retDateRanges TABLE 
(
    -- Columns returned by the function
    DateRangeKey int PRIMARY KEY NOT NULL, 
    DateRange varchar(50) NOT NULL, 
    StartDate date  NULL, 
    EndDate date  NULL,
	StartDateKey int  NULL,
	EndDateKey int  NULL
)
AS 
BEGIN

	DECLARE @KnownMonday date = '1900-01-01';
	DECLARE @KnownSunday date =  '1905-12-31';

	DECLARE @Today date = CAST(GETDATE() AS date);
	DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date));

	DECLARE @LastMonday date =  DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownMonday, @Today) % 7) ), @Today);
	IF (@LastMonday = @Today) SELECT @LastMonday = DATEADD(DAY, -7, @LastMonday);

	DECLARE @LastSunday date =  DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownSunday, @Today) % 7) ), @Today);
	IF (@LastSunday = @Today) SELECT @LastSunday = DATEADD(DAY, -7, @LastSunday);

	DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) ;
	DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1), @LastDayOfPrecedingMonth) AS date) ;
	DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday)  AS date);

	

	WITH CTE AS (
	SELECT * FROM
		( VALUES
			-- Daily values
			( -1, 'Yesterday', @Yesterday, @Yesterday ),
			(  1, 'Today', CAST(GETDATE() AS date), CAST(GETDATE() AS date) ),
			(  2, 'Yesterday and Today',  @Yesterday, @Today ),
			(  7, '7 Days to Today', DATEADD(dd, -6, @Today), @Today),
			( 14, '14 Days to Today', DATEADD(dd, -13, @Today), @Today),
			( 21, '21 Days to Today', DATEADD(dd, -20, @Today), @Today),
			( 28, '28 Days to Today', DATEADD(dd, -27, @Today), @Today),
			( 30, '30 Days to Today', DATEADD(dd, -29, @Today), @Today),
			( 60, '60 Days to Today', DATEADD(dd, -59, @Today), @Today),
			( 90, '90 Days to Today', DATEADD(dd, -89, @Today), @Today),
		
			
			(107, 'Last 7 Days',  DATEADD(dd,  -6,	@Yesterday), @Yesterday ),
			(110, 'Last 10 Days', DATEADD(dd,  -9,  @Yesterday), @Yesterday ),
			(114, 'Last 14 Days', DATEADD(dd, -13,  @Yesterday), @Yesterday ),
			(121, 'Last 21 Days', DATEADD(dd, -20,  @Yesterday), @Yesterday ),
			(128, 'Last 28 Days', DATEADD(dd, -27,  @Yesterday), @Yesterday ),
			(130, 'Last 30 Days', DATEADD(dd, -29,  @Yesterday), @Yesterday ),
			(131, 'Last 31 Days', DATEADD(dd, -30,  @Yesterday), @Yesterday ),
			(156, 'Last 56 Days', DATEADD(dd, -55,  @Yesterday), @Yesterday ),
			(160, 'Last 60 Days', DATEADD(dd, -59,  @Yesterday), @Yesterday ),
			(190, 'Last 90 Days', DATEADD(dd, -89,  @Yesterday), @Yesterday ),
			(195, 'Last 141 Days (20 weeks + 1 day)', DATEADD(dd, -140,  @Yesterday), @Yesterday ),
			(196, 'Last 365/6 Days', DATEADD(year, -1,  @Yesterday), @Yesterday ),

			-- Weekly Values
			(201, 'Week (starting Mon) to Yesterday', @LastMonday, @Yesterday),
			(202, 'Week (starting Sun) to Yesterday', @LastSunday, @Yesterday),
			(205, 'Last week', CASE @@datefirst 
				WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,-1,GETDATE())) AS date) 
				WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+2,dateadd(week,-1,GETDATE())) AS date)
				END, CASE @@datefirst WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE()),dateadd(week,0,GETDATE())) AS date) 
				WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,0,GETDATE())) AS date) END),
			(210, '4 Weeks (starting Mon) to Yesterday', DATEADD(week, -3, @LastMonday), @Yesterday),
			(211, '4 Weeks (starting Sun) to Yesterday', DATEADD(week, -3, @LastSunday), @Yesterday),

			-- Monthly Values
			(301, 'Calendar Month to Yesterday', @FirstDayOfYesterdaysMonth, @Yesterday),
			(302, 'Two Months to Yesterday', DATEADD(MM, -1, @FirstDayOfYesterdaysMonth), @Yesterday),
			(303, 'Three Months to Yesterday', DATEADD(MM, -2, @FirstDayOfYesterdaysMonth), @Yesterday),
			(304, 'Four Months to Yesterday', DATEADD(MM, -3, @FirstDayOfYesterdaysMonth), @Yesterday),
			(306, 'Six Months to Yesterday', DATEADD(MM, -5, @FirstDayOfYesterdaysMonth), @Yesterday),


			(311, 'Last month', @FirstDayOfPrecedingMonth, @LastDayOfPrecedingMonth ),
			(312, '2 months ago', DATEADD(MM, -1, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -0, @FirstDayOfPrecedingMonth)) ),
			(313, '3 months ago', DATEADD(MM, -2, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -1, @FirstDayOfPrecedingMonth)) ),
			(314, '4 months ago', DATEADD(MM, -3, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -2, @FirstDayOfPrecedingMonth)) ),
			(316, '6 months ago', DATEADD(MM, -5, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -4, @FirstDayOfPrecedingMonth)) ),
			(322, '12 months ago', DATEADD(MM, -11, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -10, @FirstDayOfPrecedingMonth)) ),
			(999, 'Custom date range',  CAST(NULL as date),  CAST(NULL AS date) )
	
	
		) DT (DateRangeKey, DisplayName, StartDate, EndDate) 
	)

	INSERT @retDateRanges
	SELECT
		CTE.DateRangeKey,
		CTE.DisplayName,
		CTE.StartDate,
		CTE.EndDate,
		StartDateKey = CAST(CONVERT(VARCHAR, CTE.StartDate, 112) AS INT),
		EndDateKey = CAST(CONVERT(VARCHAR, CTE.EndDate, 112) AS INT)	
	FROM
		CTE
	WHERE
		(@DateRangeKey = 0)
		OR
		(@DateRangeKey = CTE.DateRangeKey)

	RETURN
END

Shared Datasets

Create a shared dataset named DateRangeParameter

Query

SELECT        DateRangeKey, DateRange, StartDate, EndDate, StartDateKey, EndDateKey
FROM            parm.fnDateRanges(@DateRangeKey)

Parameter:  @DateRangeKey

Report Datasets

Create two report datasets, each based on the shared data source DateRangeParameter

  • DateRangeParameter
    set @DateRangeKey to 0 (fixed value).  This is used as the available values source of the Date Range parameter option list.
  • DateRangeParameterSelected
    set  @DateRangeKey to the report parameter of the same name.

 

Report Parameters

Create the following parameters

  • DateRangeKey
    (“Date Range”, visible, Integer)
    Use DateRangeParameter as source of Available Values.
  • StartDateKey, EndDateKey
    (Integer, Allow Null, Hidden)
    Use DateRangeParameterSelected as AvailableValues source (StartDateKey for both Value and Label field) and as Default Values source (StartDateKey for Value field).
  • ManualStartDate
    (“Start Date”, Date/Time,  allow Null, visible)
  • ManualEndDate
    (“End Date”, Date/Time, allow Null, visible)

Dataset Parameters

Use the following expressions in the dataset date range parameters.

  • =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, “yyyyMMdd”), Parameters!StartDateKey.Value )
  • =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualEndDate.Value, “yyyyMMdd”), Parameters!EndDateKey.Value )

Revision to allow Fixed Start Date

This parameter arrangement doesn’t allow scheduling of a report with, say, a fixed start date and a moving end date (say, yesterday).  Now although we don’t often want to have fixed start dates – leading to ever-increasing report size – sometimes it’s useful.  Here’s a revision for the start parameter. It makes it slightly less intuitive in that the manual start date will be taken even if the Date Range is not ‘manually entered’.

=IIf(Not IsNothing(Parameters!ManualStartDate.Value), 
Format(Parameters!ManualStartDate.Value, "yyyyMMdd"), 
Parameters!StartDateKey.Value )

Date Range Limitation

Modification to limit the maximum period selectable in a report.

  1. Add report parameter MaxDateRangeDays (Integer, Hidden). Set the AvailableValues and Default values to specified values, e.g. 92.

This is difficult – maybe create a function.

 

Date Range Label

Expression source.   Note that changes should be made if the Revision for Fixed Start Date is deployed.

=IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, "dd/MM/yyyy") & " to " & Format(Parameters!ManualEndDate.Value, "dd/MM/yyyy"), Format(First(Fields!StartDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy") & " to " & Format(First(Fields!EndDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy"))

 

 

 

Data-Tier Application (dac) and SqlPackage.exe

Can you install tools to export/deploy DacPac files without requiring a Visual Studio installation?

Favourite SqlPackage commands

Command Line reference  https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

Extract DACPAC I didn’t find that the /p:IgnoreUserLoginMappings={True | False} switch made any difference. Users were still created on default deployment.

The definition in the reference is not easy to interpret: “Specifies whether relationships between users and logins will be ignored.”  Does that mean it will still attempt to create users, or not?

Deploy DACPAC   “C:\Program Files\Microsoft SQL Server\120\DAC\bin\SQlPackage.exe” /a:publish /tcs:”Data Source=localhost;Initial
Catalog=mydb;Integrated Security=true” /sf:”C:\Temp\db_backup\ADatabase.dacpac”
/p:ExcludeObjectTypes=RoleMembership;users;Logins;

This excludes users and logins, necessary if deploying to environment that doesn’t support users that were in the source.  If there are objects owned by a custom user the deployment will still fail.

Master Data Services 2012 Notes

Notes from a first brush with MDS.

Installation (2102)

  • The ApplicationPool credential should be a member of MDS_Services (?) local user group. (not convinced that setup does this for you).
  • The installation is not complete until you get the chance to click Apply on the Web Configuration page of Master Data Services Configuration Manager.   If this button isn’t enabled, it’s not fully installed (for example, the Authentication mode won’t be correctly configured and the WCF Service (which is used by the Data Explorer and the Excel Add-in) won’t work.

{Code} not updating in Excel

Symptom the Code column seems to have become disconnected from the recordset.  The cell is not highlighted when it is changed, and its value is not submitted on Publishing. This causes “The code cannot be empty” errors.

Cause check that the pixel width of Code is not zero.   Pixel width can be set to zero to hide columns in Excel, but for the Code column this causes the column to appear but to be disconnected (non-updateable).

Deploying Models

  • Model deployment does not include integration views.  Each view has to be recreated manually on production environment – there are not many settings.
  • Model deployment does not include permissions, reasonably since these will often vary between environments.