1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
EXEC master.dbo.Sp_addlinkedserver
@server = 'SSAS',
@srvproduct = '',
@provider = 'MSOLAP',
@datasrc = '[DB-NAME]';
EXEC master.dbo.Sp_serveroption
@server='SSAS',
@optname='rpc out',
@optvalue='true';
CREATE SCHEMA ssas;
CREATE PROCEDURE ssas.Usp_execute (@XMLA XML)
AS
DECLARE @Command VARCHAR(max) = CONVERT(VARCHAR(max), @XMLA);
EXEC (@Command) at ssas;
CREATE PROCEDURE ssas.Usp_processdatabase (@CubeName NVARCHAR(50),
@CubeID NVARCHAR(50),
@MeasureGroupID NVARCHAR(50),
@DataSourceID NVARCHAR(50),
@StartDT DATETIME
--'YYYY-MM-DD'
,
@EndDT DATETIME,
@PartitionPreFix NVARCHAR(20),
@SqlQuery NVARCHAR(max) =''
/*'SELECT FK_AS_Produkt_ID, FK_AS_Produkt_productId_BK, FK_AS_Vertrag_ID, FK_AS_Vertrag_bookingId_BK, FK_AS_Inserent_ID, FK_AS_Inserent_accountId_BK, FK_AS_Objekt_ID, FK_AS_Objekt_vehicleId_BK,
FK_CD_Zeit_Zeit_ID, FK_CD_Zeit_Zeit_DIM_ZEIT_KEY_BK, [Datum Start], [Datum Ende], [Datum Start Booking], [Status ID], [Anzahl Plätze], [Anzahl verwendete Plätze], Preis, MwSt,
privateInserters_distict_cnt_PD, privateInsertersReturners1_distict_cnt, privateInsertersReturners12_distict_cnt, privateInsertersReturners48_distict_cnt, soldMemberSlotsFixdate_sum_PD,
privateInsertersMoto_sum_PD, vehiclesPrivatInsertersAuto_sum_PD, revenuePrivateInsertersListingsAuto_sum_PD, revenuePrivateInsertersAdditionalsAuto_sum_PD, vehiclesPrivatInsertersMoto_sum_PD,
revenuePrivateInsertersAdditionalsMoto_sum_PD, SA_SourceSystem_ID, SA_Load_ID, CA_Load_ID, DW_Load_ID, SCD_TransactionDate, DS_Load_ID
FROM DM.AS_BookingStats
WHERE FK_CD_Zeit_Zeit_ID BETWEEN <#=dateFrom#> AND <#=dateTo#>'*/
)
AS
DECLARE @myXMLA XML,
@myXMLA_before XML,
@myXMLA_delete XML
DECLARE @value NVARCHAR(30),
@dateFrom VARCHAR(10),
@dateTo VARCHAR(10),
@year NVARCHAR(4)
DECLARE @replSqlQuery NVARCHAR(max)
SET @dateFrom=Format(Dateadd(month, Datediff(month, 0, @StartDT), 0),
'yyyyMMdd') --First day of month
SELECT @value = @PartitionPreFix
+ Format (@StartDT, 'yyyyMM') + '_before'
SELECT @replSqlQuery = Replace(@SqlQuery,
'WHERE FK_CD_Zeit_Zeit_ID BETWEEN <#=dateFrom#> AND <#=dateTo#>'
, 'WHERE FK_CD_Zeit_Zeit_ID < '
+ @dateFrom)
--delete standard
SET @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>' + @CubeName + '</DatabaseID> <CubeID>' + @CubeID
+ '</CubeID> <MeasureGroupID>' + @MeasureGroupID
+ '</MeasureGroupID> <PartitionID>' + @MeasureGroupID + '</PartitionID> </Object> </Delete>'
PRINT CONVERT(NVARCHAR(max), @myXMLA_delete)
EXEC ssas.Usp_execute
@myXMLA_delete;
PRINT ' '
--delete first partition before
SET @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>' + @CubeName + '</DatabaseID> <CubeID>' + @CubeID
+ '</CubeID> <MeasureGroupID>' + @MeasureGroupID
+ '</MeasureGroupID> <PartitionID>' + @value + '</PartitionID> </Object> </Delete>'
PRINT CONVERT(NVARCHAR(max), @myXMLA_delete)
EXEC ssas.Usp_execute
@myXMLA_delete;
PRINT ' '
SET @myXMLA =N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ParentObject> <DatabaseID>' + @CubeName + '</DatabaseID> <CubeID>' + @CubeID
+ '</CubeID> <MeasureGroupID>' + @MeasureGroupID + '</MeasureGroupID> </ParentObject> <ObjectDefinition> <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400"> <ID>'
+ @value + '</ID> <Name>' + @value + '</Name> <Source xsi:type="QueryBinding"> <DataSourceID>'
+ @DataSourceID + '</DataSourceID> <QueryDefinition> ' + @replSqlQuery + ' </QueryDefinition> </Source> <StorageMode>Molap</StorageMode> <ProcessingMode>Regular</ProcessingMode> <ProactiveCaching> <SilenceInterval>-PT1S</SilenceInterval> <Latency>-PT1S</Latency> <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval> <ForceRebuildInterval>-PT1S</ForceRebuildInterval> <Source xsi:type="ProactiveCachingInheritedBinding" /> </ProactiveCaching> </Partition> </ObjectDefinition> </Create>'
PRINT CONVERT(NVARCHAR(max), @myXMLA)
EXEC ssas.Usp_execute
@myXMLA;
PRINT ' '
WHILE @StartDT < @EndDT
BEGIN
SET @dateFrom=Format(Dateadd(month, Datediff(month, 0, @StartDT), 0),
'yyyyMMdd') --First day of month
SET @dateTo =Format(Dateadd(month, Datediff(month, -1, @StartDT), -1),
'yyyyMMdd') --Last Day of month
SELECT @value = @PartitionPreFix
+ Format (@StartDT, 'yyyyMM')
SELECT @replSqlQuery = Replace(Replace(@SqlQuery, '<#=dateTo#>',
@dateTo),
'<#=dateFrom#>', @dateFrom)
SET @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>' + @CubeName + '</DatabaseID> <CubeID>' + @CubeID
+ '</CubeID> <MeasureGroupID>' + @MeasureGroupID
+ '</MeasureGroupID> <PartitionID>' + @value + '</PartitionID> </Object> </Delete>'
PRINT CONVERT(NVARCHAR(max), @myXMLA_delete)
EXEC ssas.Usp_execute
@myXMLA_delete;
PRINT ' '
SET @myXMLA =N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ParentObject> <DatabaseID>' + @CubeName + '</DatabaseID> <CubeID>' + @CubeID
+ '</CubeID> <MeasureGroupID>' + @MeasureGroupID + '</MeasureGroupID> </ParentObject> <ObjectDefinition> <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400"> <ID>'
+ @value + '</ID> <Name>' + @value + '</Name> <Source xsi:type="QueryBinding"> <DataSourceID>'
+ @DataSourceID + '</DataSourceID> <QueryDefinition> ' + @replSqlQuery + ' </QueryDefinition> </Source> <StorageMode>Molap</StorageMode> <ProcessingMode>Regular</ProcessingMode> <ProactiveCaching> <SilenceInterval>-PT1S</SilenceInterval> <Latency>-PT1S</Latency> <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval> <ForceRebuildInterval>-PT1S</ForceRebuildInterval> <Source xsi:type="ProactiveCachingInheritedBinding" /> </ProactiveCaching> </Partition> </ObjectDefinition> </Create>'
PRINT CONVERT(NVARCHAR(max), @myXMLA)
EXEC ssas.Usp_execute
@myXMLA;
PRINT ' '
SET @StartDT = Dateadd(month, 1, @StartDT)
END
go
|