-- Create SQL Server database.
-- Create schema config.
CREATE SCHEMA [config] AUTHORIZATION [dbo];
GO

IF OBJECT_ID('config.SSASPartitionsBase') IS NOT NULL DROP TABLE config.SSASPartitionsBase;
CREATE TABLE [config].[SSASPartitionsBase](
	[CubeName] [varchar](100) NOT NULL,
	[MeasureGroup] [varchar](100) NOT NULL,
	[FactTableName] [varchar](3000) NOT NULL,
	[PartitionFieldName] [varchar](100) NULL,
	[TableStartDateKey] [int]  NULL,
	[LoadType1] [varchar](20) NULL,
	[LoadType2] [varchar](20) NULL,
	[LoadType3] [varchar](20) NULL,
 CONSTRAINT [PK_SSASPartitionsBase] PRIMARY KEY CLUSTERED 
	([CubeName] ASC, [MeasureGroup] ASC)
) 
GO

IF OBJECT_ID('config.SSASPartitions') IS NOT NULL DROP TABLE config.SSASPartitions;
CREATE TABLE [config].[SSASPartitions](
	[CubeName] [varchar](100) NOT NULL,
	[MeasureGroup] [varchar](100) NOT NULL,
	[Partition] [varchar](100) NOT NULL,
	[SQL] [varchar](3000) NOT NULL,
	[MinDateKey] [int] NOT NULL,
	[MaxDateKey] [int] NOT NULL,
	[NeedsReProcessing] [bit] NOT NULL,
	[LastProcessDate] [smalldatetime] NULL,
 CONSTRAINT [PK_SSASPartitions] PRIMARY KEY CLUSTERED 
([CubeName] ASC,[MeasureGroup] ASC, [Partition] ASC)
)
GO

TRUNCATE TABLE config.SSASPartitionsBase;
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Exchange Rates'		,'[FactCurrencyRate]'		, NULL			, NULL		, 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Financial Reporting','[dbo].[FactFinance]'		, NULL			, NULL		, 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Internet Customers'	,'[dbo].[FactInternetSales]', 'OrderDateKey', '20010101', 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Internet Orders'	,'[dbo].[FactInternetSales]', 'OrderDateKey', '20010101', 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Internet Sales'		,'[dbo].[FactInternetSales]', 'OrderDateKey', '20010101', 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Reseller Orders'	,'[dbo].[FactResellerSales]', 'OrderDateKey', '20010101', 'Reseller',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Reseller Sales'		,'[dbo].[FactResellerSales]', 'OrderDateKey', '20010101', 'Reseller',NULL, NULL)

INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Sales Orders'		,'
(SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      ''Reseller'' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + ''Line '' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      ''Internet'' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + ''Line '' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales) X
', 'OrderDateKey', '20010101', 'Sales',NULL, NULL)

INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Sales Reasons'		,'[dbo].[FactInternetSalesReason]', NULL		, NULL		, 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Sales Summary'		,'
(SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      ''Reseller'' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + ''Line '' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      ''Internet'' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + ''Line '' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales) X
', 'OrderDateKey', '20010101', 'Sales',NULL, NULL)
INSERT INTO config.SSASPartitionsBase(CubeName, MeasureGroup, FactTableName, PartitionFieldName, TableStartDateKey, LoadType1, LoadType2, LoadType3)
 VALUES('Adventure Works','Sales Targets'		,'(SELECT EmployeeKey, TimeKey AS OrderDateKey, CalendarQuarter, CalendarYear, SalesAmountQuota FROM FactSalesQuota) X'		, NULL			, NULL		, 'Sales',NULL, NULL)
go
-- SELECT * FROM config.SSASPartitionsBase;


IF OBJECT_ID('config.Date') IS NOT NULL DROP TABLE config.Date;
CREATE TABLE config.Date(
	  DateKey	int NOT NULL PRIMARY KEY
	, [Date]	datetime NOT NULL
	, MonthKey	int NOT NULL
	, YearKey	int NOT NULL
	)
go
-- Will populate Date table with values for years 2001-2004
SET NOCOUNT ON
DECLARE @d datetime
SET @d = CONVERT(datetime, '20010101', 112)
WHILE @d <= CONVERT(datetime, '20040731', 112)
BEGIN
 INSERT INTO config.Date(DateKey, [Date], MonthKey, YearKey)
	SELECT CONVERT(int, CONVERT(varchar, @d, 112)) AS DateKey
		, @d AS [Date]
		, YEAR(@d) * 100 + MONTH(@d) AS MonthKey
		, YEAR(@d) AS YearKey
 SET @d = @d + 1
END
SET NOCOUNT OFF
go
-- SELECT * FROM config.Date

IF OBJECT_ID('config.CurrentLoadDate') IS NOT NULL DROP TABLE config.CurrentLoadDate;
CREATE TABLE config.CurrentLoadDate(
	  DateKey	int
	)
go
-- Table config.CurrentLoadDate will always have just one record
INSERT INTO config.CurrentLoadDate(DateKey) VALUES(20040731);
go

IF EXISTS (SELECT name FROM sysobjects WHERE  name = N'CalculateSSASPartitions' AND type = 'P')
    DROP PROCEDURE dbo.CalculateSSASPartitions
GO

CREATE PROCEDURE dbo.CalculateSSASPartitions
 @Action varchar(50) = ''
  WITH RECOMPILE 
 AS 
BEGIN

 SET NOCOUNT ON
 PRINT 'Executing SP: CalculateSSASPartitions'

 IF ISNULL(@Action,'') NOT IN ('Sales','Reseller')
 BEGIN
  PRINT 'Current parameter @Action value: ' + ISNULL(@Action,'')
  PRINT '  Expected values: Sales | Reseller'
  RAISERROR('Incorrect @Action parameter value in SP: CalculateSSASPartitions',18,1)
  RETURN
 END

 -- ---------------------------------------------------------------------------------------
 -- ---------------------------------------------------------------------------------------
 -- ---------------------------------------------------------------------------------------
 IF OBJECT_ID('tempdb..#tmpPart') IS NOT NULL DROP TABLE #tmpPart
 CREATE TABLE #tmpPart (
	  CubeName varchar(100) NOT NULL
	, MeasureGroup varchar(100) NOT NULL
	, [Partition] varchar(200) NOT NULL
	, [SQL] varchar(3000) NOT NULL
	, MinDateKey int NOT NULL
	, MaxDateKey int NOT NULL
	)

 INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
 SELECT P.CubeName, P.MeasureGroup
	, P.MeasureGroup + ' ' + CONVERT(varchar, D.PartitionName) AS Partition
	, 'SELECT * FROM ' + P.FactTableName + ' WHERE ' + P.PartitionFieldName
		+ CASE	WHEN MinDateKey = MaxDateKey THEN '=' + CONVERT(varchar, MinDateKey)
				ELSE ' BETWEEN ' + CONVERT(varchar, D.MinDateKey) + ' AND ' + CONVERT(varchar, D.MaxDateKey)
		  END
	   AS SQL
--	, P.AggregationID
	, CASE WHEN D.MinDateKey < P.TableStartDateKey THEN P.TableStartDateKey ELSE D.MinDateKey END AS MinDateKey
	, D.MaxDateKey
 FROM config.SSASPartitionsBase P
	JOIN (
	SELECT MonthKey AS PartitionName, MIN(DateKey) AS MinDateKey, MAX(DateKey) AS MaxDateKey
	  FROM config.Date
	 WHERE YearKey = (SELECT FLOOR(DateKey/10000) FROM config.CurrentLoadDate)
	 GROUP BY MonthKey
	UNION ALL
	SELECT  YearKey AS PartitionName, MIN(DateKey) AS  MinDateKey, MAX(DateKey) AS MaxDateKey
	  FROM config.Date
	 WHERE YearKey < (SELECT FLOOR(DateKey/10000) FROM config.CurrentLoadDate)
	 GROUP BY YearKey
	) D ON D.MaxDateKey >= P.TableStartDateKey
WHERE P.PartitionFieldName IN ('OrderDateKey')
  AND (P.LoadType1 = @Action OR P.LoadType2 = @Action OR P.LoadType3 = @Action)

-- -------------------------------------------------
-- Add measure gruops that will not be partitioned
 INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
 SELECT P.CubeName, P.MeasureGroup, P.MeasureGroup AS Partition
	, 'SELECT * FROM ' + P.FactTableName AS SQL
	, 0 AS MinDateKey, 999999 AS MaxDateKey
  FROM config.SSASPartitionsBase P
 WHERE ISNULL(PartitionFieldName,'') = ''
   AND (P.LoadType1 = @Action OR P.LoadType2 = @Action OR P.LoadType3 = @Action)

-- Safety. If we miss any measure groups, add records as if they were not partitioned.
 INSERT INTO #tmpPart(CubeName, MeasureGroup, [Partition], [SQL], MinDateKey, MaxDateKey)
  SELECT CubeName, MeasureGroup, MeasureGroup AS [Partition], 'SELECT * FROM ' + FactTableName AS [SQL]
	, 0 MinDateKey, 999999 MaxDateKey
    FROM config.SSASPartitionsBase B
   WHERE NOT EXISTS(SELECT * FROM #tmpPart T WHERE T.MeasureGroup = B.MeasureGroup)
   AND (B.LoadType1 = @Action OR B.LoadType2 = @Action OR B.LoadType3 = @Action)

 -- ----------------------------------------------------------------------------------------------------------------------------------

 DELETE FROM config.SSASPartitions
    FROM config.SSASPartitions P
		JOIN config.SSASPartitionsBase B ON B.CubeName = P.CubeName AND B.MeasureGroup = P.MeasureGroup
   WHERE (B.LoadType1 = @Action OR B.LoadType2 = @Action OR B.LoadType3 = @Action)
	 AND NOT EXISTS(SELECT * FROM #tmpPart T WHERE T.CubeName = P.CubeName AND T.MeasureGroup = P.MeasureGroup
					   AND T.Partition = P.Partition)
 PRINT 'Deleted partitions that do not exists in new list. Record count: ' + CONVERT(varchar, @@ROWCOUNT)

 INSERT INTO config.SSASPartitions(CubeName, MeasureGroup, [Partition], [SQL]
	, MinDateKey, MaxDateKey, NeedsReProcessing)
 SELECT CubeName, MeasureGroup, [Partition], [SQL]
	, MinDateKey, MaxDateKey, 1 AS NeedsReProcessing
   FROM #tmpPart T
  WHERE NOT EXISTS(SELECT * FROM config.SSASPartitions P 
					WHERE P.CubeName = T.CubeName AND P.MeasureGroup = T.MeasureGroup
					  AND P.Partition = T.Partition
				  )
 PRINT 'Inserted partitions that do not exists. Record count: ' + CONVERT(varchar, @@ROWCOUNT)
END
go

-- SELECT * FROM config.SSASPartitions
 TRUNCATE TABLE config.SSASPartitions
 EXEC dbo.CalculateSSASPartitions @Action='Sales'
 EXEC dbo.CalculateSSASPartitions @Action='Reseller'
go