Pages

Thursday, October 19, 2017

SSAS + Forecast calculations MDX

Following forecast member returns forecast figures for a given measure based on the entire history.

WITH MEMBER 
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
AS LinRegPoint(
    Rank(
        [Period].[Extended Year - Quarter - Month].CurrentMember,
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members
        ),
    Exists(
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members,
        [Period].[Calendar Year - Quarter - Month].[Month].Members),

    ([Measures].CurrentMember, [Measures Utility].[Measure Calculations].DefaultMember),
    Rank(
    [Period].[Extended Year - Quarter - Month].CurrentMember,
    [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members)
)

SELECT

([Measures].[RXA Value] *
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]

) on 0,
NON EMPTY {[Period].[Extended Month].Children} 

ON 1
FROM [Cube Name]


Forecasts based on 3 months data points
CREATE MEMBER CURRENTCUBE.[Measures Utility].[Measure Calculations].[Linear Regression Forecast - 3 Data Points]
AS LinRegPoint(
    Rank([Period].[Operational Year - Quarter - Month].CurrentMember,
            {
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Lag(2):
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Parent.Parent.LastChild.LastChild
            } --[Output Period]
         ),  -- Measures.OutputX (Current Financial Year)
         Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3), -- [Input Period] -- 3 Months
    (    [Measures].CurrentMember,
        [Measures Utility].[Measure Calculations].DefaultMember), -- Measures.Input Y
    Rank(
        [Period].[Operational Year - Quarter - Month].CurrentMember,
        Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3) --[Input Period] -- 3 Months
        ) -- Measures.InputX
    ),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;

Find SQLserver sessions to a DB and Kill all in one goal

Get the list of connections

Select * from master.dbo.sysprocesses
where dbid = db_id('databaseName')
 Kill all the connection to a given database
Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End
GOReference : http://stackoverflow.com/questions/1154200/when-restoring-a-backup-how-do-i-disconnect-all-active-connections













Unzip all the zip files in a folder + PowerShell


PARAM 
(
    [string] $ZipFilesPath = "E:\TEMP\TEMP DSE",
    [string] $UnzipPath = "E:\TEMP\TEMP DSE"
)
$Shell = New-Object -com Shell.Application
$Location = $Shell.NameSpace($UnzipPath)
 $ZipFiles = Get-Childitem $ZipFilesPath -Recurse -Include *.zip
 
$progress = 1
foreach ($ZipFile in $ZipFiles) {
    Write-Progress -Activity "Unzipping to $($UnzipPath)" -PercentComplete (($progress / ($ZipFiles.Count + 1)) * 100) -CurrentOperation $ZipFile.FullName -Status "File $($Progress) of $($ZipFiles.Count)"
    $ZipFolder = $Shell.NameSpace($ZipFile.fullname)
 
 
    $Location.Copyhere($ZipFolder.items(), 1040)
    $progress++
}

Function & SSIS Expression to Retrieve MonthNumber in YYYYMM Format

TSQL Function
Following function can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.

CREATE FUNCTION dbo.[Retrieve MonthNumber(YYYYMM)](@MonthName Varchar(50), @MonthNameFormat Varchar(50))
RETURNS INT AS
BEGIN
    DECLARE @MonthNumber INT;
    SET @MonthName = UPPER(@MonthName);
    RETURN CASE
        WHEN UPPER(@MonthNameFormat) = 'MON-YYYY' OR UPPER(@MonthNameFormat) = 'MON YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 3) = 'JAN' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'FEB' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAR' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'APR' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAY' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUN' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUL' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'AUG' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'SEP' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'OCT' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'NOV' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'DEC' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
        WHEN UPPER(@MonthNameFormat) = 'MM/YYYY' OR UPPER(@MonthNameFormat) = 'MM-YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 2) = '01' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '02' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '03' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '04' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '05' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '06' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '07' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '08' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '09' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '10' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '11' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '12' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
    ELSE
        0
    END
END;
GO



Following qeury can be used to test / execute above function.
SELECT dbo.[Retrieve MonthNumber(YYYYMM)]('01/2015', 'MM/YYYY')


SSIS Expression
Following expression can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.
(DT_I4)(SUBSTRING(Month,4,2) + RIGHT(Month,2) +
    (    FINDSTRING(Month,"JAN",1) > 0 ? "01" :
        FINDSTRING(Month,"FEB",1) > 0 ? "02" :
        FINDSTRING(Month,"MAR",1) > 0 ? "03" :
        FINDSTRING(Month,"APR",1) > 0 ? "04" :
        FINDSTRING(Month,"MAY",1) > 0 ? "05" :
        FINDSTRING(Month,"JUN",1) > 0 ? "06" :
        FINDSTRING(Month,"JUL",1) > 0 ? "07" :
        FINDSTRING(Month,"AUG",1) > 0 ? "08" :
        FINDSTRING(Month,"SEP",1) > 0 ? "09" :
        FINDSTRING(Month,"OCT",1) > 0 ? "10" :
        FINDSTRING(Month,"NOV",1) > 0 ? "11" :
        FINDSTRING(Month,"DEC",1) > 0 ? "12" :
        "00"
        )
    )

Monday, March 20, 2017

Use SQL to read XML data - Example


There are a few ways to read XML data using SQL and following example demonstrate how to read a simple XML file in tabular format.

 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
DECLARE @MyXML XML
-- SET @MyXML = (SELECT CONVERT(xml, BulkColumn, 2) xml 
-- FROM OPENROWSET(Bulk 'C:\sample.XML', SINGLE_BLOB) [rowsetresults])
SET @MyXML = '
<Order xmlns="XMLSchema">
  <Database>
    <Tables>
      <Table Name="Product">
        <Files>
          <File FileName="Product.txt" NumberOfRows="17" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="ATC_1_CD" Length="30" DataType="nvarchar" />
    <Column Name="IMS_PROD_SHRT_NM" Length="80" DataType="nvarchar" />
          <Column Name="PACK_DESC" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
   <Table Name="Corporation">
        <Files>
          <File FileName="Corporation.txt" NumberOfRows="5" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="CORP_ID" Length="30" DataType="nvarchar" IsPrimaryKey="true" />
          <Column Name="CORP_SHRT_NM" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
    </Tables>
  </Database>
</Order>
'
;WITH XMLNAMESPACES (DEFAULT 'XMLSchema') -- Define Default XML schema (i.e. xmlns )
SELECT 
 FileName  = files.x.value('@FileName','varchar(200)'),
 TableName  = tabs.x.value('@Name','varchar(200)'),
 ColumnName  = cols.x.value('@Name','varchar(200)'),
 ColumnDataType = cols.x.value('@DataType','varchar(200)'),
 ColumnLength = cols.x.value('@Length','varchar(200)')
FROM
     @MyXML.nodes('/Order[1]/Database[1]/Tables[1]/Table') tabs(x) 
CROSS APPLY tabs.x.nodes('Columns[1]/Column') cols(x) -- Loop through Columns level
CROSS APPLY tabs.x.nodes('Files[1]/File') files(x) -- Loop through Files Level

Result


Reference