Situations to use Multidimensional SSAS model over Tabular Model

4154_tabular_vs_multidimensional

I was introduced with SSAS modeling about 5 years ago. At that time, our team is recommended by Microsoft to use their new modeling technology called Tabular SSAS modeling. Since then, our team started to build multiple tabular models, and those models are growing larger and larger with more complicated calculations. We love tabular modeling because it uses xVelocity engines (Vertipaq – in memory analytic engine and memory optimized ColumnStore index) which means it is super fast because of the in memory storage and it is mulch easier to implement because it uses relational modeling structure which we are familiar with, the script language DAX is kind of like Excel formula to start, and we don’t need to consider set, MDX calculations, aggregations, and storage modes etc. which makes Multidimensional Modeling very complex and difficult to learn.

However, with more complicated requirements come in, we are facing some challenges with current Tabular modeling solution.

As of recently, I started to prepare Microsoft BI certification and started to look into Multidimensional modeling. I found out that we may be able to leverage some of its capabilities which already exists in multidimensional modeling which has been exist so many years. I listed some of those capabilities which are not exist in Tabular model yet.

  1. Remote Partitioning (partition can locate in other server)
  2. Multiple Storage mode selection (MOLAP to ROLAP)
  3. Write Back option – customer can write back to the model – only for aggregate function other than Sum (create “what-if analysis”
  4. Aggregations – pre-calculated (can change setting of how much to pre aggregated) need to consider the storage and maintenance required
  5. Many to many relationships
  6. Role play dimensions
  7. Customized Drill-through and other actions (for older version of Tabular we can use BIDS helper to do it. But, for tabular in SQL server 2016 version, it is disabled)
  8. Merge Partitions, ssms will automatically remove but the visual studio workspace project need to update manually.
  9. Calculation Template
  10. Build in Business Intelligent
  11. Process Index (build or rebuild)
  12. Partition Slice – know only pull the model slice information and provide fast finding of the right partition to use to calculate

Thanks,

Your Friend, Annie

 

Simple MDX Queries

— Single axis

select [Measures].[Sales Amount] on Columns

from [InternetSales];

— Double axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–two aggregation wrong no supported

Select [Measures].[Sales Amount] on 0,

[Dim Employee].[Reports To].[Level 02].ALLMEMBERS on 1,

[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–two aggregation right

Select [Measures].[Sales Amount] on 0,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country]*[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–Filter out emplty

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

–“ALLMEMBER”

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].&[Canada]) on 1

from [InternetSales];

–using tuples

Select NON EMPTY [Measures].[Sales Amount] on Columns,

NON EMPTY [Order Date].[Calendar Year].[Calendar Year] on rows

from [InternetSales];

Select NON EMPTY ( [Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year]) on columns

FROM [InternetSales];

–creating a tuple set

Select NON EMPTY {([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2006]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2007]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2008])

} on columns

FROM [InternetSales];

–Multiple Tuple Sets

Select [Measures].[Sales Amount] on Columns,

NONEMPTY(

([Dim Product].[Model Name].Allmembers, {[Order Date].[Calendar Year].[Calendar Year].&[2007]

,[Order Date].[Calendar Year].[Calendar Year].&[2008]}),[Measures].[Sales Amount]

) on rows

FROM [InternetSales];

–Functions

–TOP Percent

Select [Measures].[Sales Amount] on COLUMNS,

TopPercent([Dim Product].[Model Name].[Model Name],50,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–Top Sum (running total of category aggregattion exceed a certain amount)

Select [Measures].[Sales Amount] on COLUMNS,

TopSum([Dim Product].[Model Name].[Model Name],150000,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–use set functions

–Members and Allmembers (to see or not to see calculated members)

Select [Measures].[Sales Amount] on COLUMNS,

[Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–non emplty

Select [Measures].[Sales Amount] on COLUMNS,

Non empty [Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–nonemplty function against tuple

Select [Measures].[Sales Amount] on COLUMNS,

Nonempty( [Dim Product].[Model Name].[Model Name].Members, [Measures].[Sales Amount]

) on ROWS

from [InternetSales];

–Top/Bottom Count

Select [Measures].[Sales Amount] on COLUMNS,

TopCount( [Dim Product].[Model Name].[Model Name].Members, 5,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

Select [Measures].[Sales Amount] on COLUMNS,

BottomCount([Dim Product].[Model Name].[Model Name].Members, 31,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

— Specifying Axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–Not supported in SSMS

Select [Measures].[Sales Amount] on 0,

[Measures].[Discount Amount] on 1,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–use tuples instead has to start at 0

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–use Having clause

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS

Having [Measures].[Sales Amount] >=8000000 on 1

from [InternetSales];

–use slicers

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States];

–AND/OR not supported, use Tuples

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States]

AND [Order Date].[Calendar Year].&[2007];

–instead, from different hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ([Dim Sales Territory].[Sales Territory Country].&[United States]

,[Order Date].[Calendar Year].&[2007]);

–instead, from same hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ({[Dim Sales Territory].[Sales Territory Country].&[United States]

,[Dim Sales Territory].[Sales Territory Country].&[Canada]})

–Scope MDS Statement (create sub-cube)

Step 1: reconnect to server use “options”->”Additional Connection Parameters”

and set statement as Cube=”InternetSales”

Step 2: Run Scope Function one step a time

scope ([Measures].[Sales Amount],[Dim Sales Territory].[Sales Territory Country].&[Canada]);

this = [Measures].[Sales Amount]*1.1;

end scope;

–case statements

With Member [Measures].[Sales Targets] as

Case

when [Measures].[Sales Amount]>3000000 then “Achived”

when [Measures].[Sales Amount]<2000000 then “Below Expected”

ELSE “On Track”

End

Select {[Measures].[Sales Amount],[Measures].[Sales Targets]} on Columns,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country] on Rows

from [InternetSales]

 

 

 

5 useful SQL Server 2016 features

1. IF EXISTS

When we create or delete or alter any objects in SQL server versions before 2016, we have do use syntax like this

IF OBJECT_ID(‘[dbo].[V_ABC’) IS NOT NULL

BEGIN

DROP VIEW [dbo].[V_ABC];

END;

GO

With SQL server 2016, the syntax is much easier. And this applies to table, view, function, store procedure, etc.

DROP VIEW IF EXISTS [dbo].[V_ABC];

2. Split

I found split function especially useful when you have a column with values with you need to split into different categories. The new function called STRING_SPLIT comes very handy especially work with Cross Apply

Let’s say you have a service requests table, which has a column called REPORTING_SEGMENT and the value storied in each row of the report segment column contains multiple values. The requirement is to get the request counts for each REPORTING_SEGMENT.

SELECT value AS reporting_seg,

COUNT([ID]) AS countofrequests

FROM [Ad_Hoc].[dbo].[Service_Requests]

CROSS APPLY string_split([REPORTING_SEGMENT], ‘,’)

GROUP BY value;

3. Temporal tables

I found temporal tables are super valuable for auditing, SCD, history tracking purposes. Essentially, the SQL server is doing the job in the back-end which usually you will need to configure by yourself using SSIS package or Store Procedures. Also the history table of those temporal table are automatically configured with column store index and is compressed which provide fast read and saved storage space.

4. In-memory tables

With memory price becoming inexpensive compare years ago, now with SQL server 2016 in-memory table capability, we can use in-memory table which much faster for read and write.

5. Column-store indexes

Very glad to see that Microsoft implemented what the technology used in tabular Analysis service to SQL server. Which make query which depend highly on analytics (like aggregation) much faster than if use row-store indexes.

 

 

How to get Microsoft Professional Certifications

MCITP-logoWhy take certification exams?

  • Get overall systematic knowledge of the target profession
  • Get more confident on job as an expert
  • Gain latest updates information
  • Know my job and its relation to my co-workers job
  • Gain job security and flexibility
  • Feel fun and accomplished

Where to Find the information

How to take actions

  • Sign up now! ( you can cancel or change for free until 7 days before the exam)
  • Take 1 – 2 hours blocked time each day to focus on studying
  • Be mentally prepared (it will be hard but no matter what is the result I am learning something)

Resource to use

  • Buy the exam training kit book(if available)
  • Google “exam number + study guide” find the URL for www.mssqltips.com and follow the study guide
  • Cooperate learning site has tons of paid videos ( Anthem: My Learning)

Tips

  • Sign up through retake option (allow to retake the exam once if failed)
  • Use Video Courses online and listen to it when driving or walking
  • Join study groups (optional)

SQL to DAX – FILTER, Customized Measures

Filtering and Customized Measures

SQL Statements

–1. Filter result on female customer shopping behavior
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalSales
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
WHERE C.Gender = ‘F’
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(F.SalesAmount) IS NOT NULL;

–2 Define calculate – female sales percentage
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalInternetSales,
SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END) AS TotalFemaleInternetSales,
CAST(CAST((SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount))*100 AS DECIMAL(18, 2)) AS VARCHAR(5))+’ %’ AS FemaleInternetSalesPerc
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount) IS NOT NULL;

Corresponding DAX Statements

–1. Filter result based on female customer shopping behavior
–code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
DimCustomer[Gender] = “F”
)
— code one is the same as this one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(all(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)
— differnt behavior than code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(va(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)

–2, Define Measure and reuse, female sales percentage
DEFINE
MEASURE FactInternetSales[TotalInternetSales] =
SUM ( FactInternetSales[SalesAmount] )
MEASURE FactInternetSales[TotalFemaleInternetSales] =
CALCULATE ( SUM ( FactInternetSales[SalesAmount] ), DimCustomer[Gender] = “F” )
MEASURE FactInternetSales[FemaleSalesPerc] =
DIVIDE ( [TotalFemaleInternetSales], [TotalInternetSales], 0 )
EVALUATE
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalInternetSales”, [TotalInternetSales],
“TotalFemaleInternetSales”, [TotalFemaleInternetSales],
“FemaleSalesPercent”, FORMAT ( [FemaleSalesPerc], “Percent” )
),
[FemaleSalesPerc] <> 0
)

FROM SQL to DAX – SELECT, Grouping, Table Joins

SELECT GROUPING JOINS

  • SIX SQL Queries

–1.Select all columns from one table (not best practice)

SELECT *

FROM dbo.DimDate AS D;

–2.Select top 10 from one table

SELECT TOP 10 *

FROM dbo.DimDate AS D

ORDER BY D.DateKey;

–3.Select two columns in one table

SELECT DISTINCT

CalendarYear,

MonthNumberOfYear

FROM dbo.DimDate AS D;

–4.Select two columns and doing aggreation in the same table

SELECT CalendarYear,

MonthNumberOfYear,

COUNT(WeekNumberOfYear) AS countofWeeks

FROM dbo.DimDate AS D

GROUP BY CalendarYear,

MonthNumberOfYear;

–5.Select columns and aggregation in two tables

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear;

–6.Select columns and aggregation in two tables and filter out blank value

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear

HAVING SUM(F.SalesAmount) IS NOT NULL;

  • Corresponding Version Queries

–1.Select all columns from one table

Evaluate

DimDate

–2.Select top 10 all columns from one table

Evaluate

TOPN(10, DimDate,DimDate[DateKey])

–3.Select two(or few) columns from one table

EVALUATE

SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] )

–4.Select two columns and doing aggreation in the same table 3ms — you can use rollup syntax inside Summarize — summarize useful only select columns in single table

EVALUATE

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear],

“countofweeks”, COUNT ( Dimdate[WeekNumberOfYear] )

)

–or which performance better 2ms — also in Excel 2013 SSAS tabular 2016 PowerBI there is a better version SELECTCOLUMNS

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“countofweeks”, CALCULATE(COUNT ( Dimdate[WeekNumberOfYear] ))

)

–5.Select columns and aggregation in two tables

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE(sum(FactInternetSales[SalesAmount] ))

)

–6.Select columns and aggregation in two tables and filter out blank value

EVALUATE

FILTER (

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )

),

NOT ( ISBLANK ( [TotalSales] ) )

)

 

SSMS Change select top 1000 setting

When you right click a table/view and you get option “Select Top 1000 Rows” and “Edit Top 200 Rows”

select top 1000.PNG

Can you change the default number in the select top and edit top? Instead of 1000 and 200?

Yes you can. By go to ‘Tools” then Choose “Options”

tool options.PNG

In the “Options” window, you can select the last setting “SQL Server Object Explorer” and change the Table and View Options.

options setting.PNG

Click ‘OK’, now your default value for Edit top and Select top <n> Rows changed

after change setting

DAX – Isfiltered and Iscrossfiltered

Recently, a request come to me: Can DAX detect which fields has been filtered and based on that we selectively use measures?

Requirement:

Build a measure called received claims count:

If user selected any fields in the finalized specific table, [Received claims count] = [Finalized count]

If user selected any fields in the pending specific table, [Received claims count] = [Pending Count]

Other, [Received claims count] =  [Finalized count] + [Pending Count]

A simplified model looks like this:

BI diagram

So Isfiltered or Iscrossfiltered is the solution. There are two situations:

Situation No.1, if you only have one fields in the filtered table, you should use Isfiltered

[Received claims count] =

IF (

ISFILTERED (‘FinalizedSpecificTable'[Case Number]),

[Finalized Count],

IF (

ISFILTERED ( ‘PendingSpecificTable'[Case Number] ),

[Pending Count],

[Finalized Count]+[Pending Count]

)

)

Situation No. 2, If there are more than one fields in the filtered table, and you need detect all of possible filters, you need to use Iscrossfiltered. You can either use the foreign key in the fact table or the key fields in the dimension table. Yet, please use the key field in the dimension because it performance faster.

[Received claims count] =

IF (

ISCROSSFILTERED (‘FinalizedSpecificTable'[ClaimID]),

[Finalized Count],

IF (

ISCROSSFILTERED ( ‘PendingSpecificTable'[ClaimID] ),

[Pending Count],

[Finalized Count]+[Pending Count]

)

)

Automatically Create Model Partitions

Creating Tabluar model partitions through visual studio may be a headache task. If you manually typed wrong query in any partition, you may screw up the processing.

In this post, I will share some code I used before to help you automatically create SSAS model partitions.

Step 1: Using SQL management studuo, we need to create a table called PartitionList to provide some information about the partition including PartitionID, the dates which will be used in the partition query and etc.

IF OBJECT_ID(‘dbo.PartitionList’) IS NOT NULL

BEGIN

DROP TABLE dbo.PartitionList

END

CREATE TABLE dbo.[PartitionList]

([PartitionID] [INT] IDENTITY(1, 1)

NOT NULL,

[CreatedDate] [DATETIME] NOT NULL,

[Active] [INT] NOT NULL,

[PartitionNameSuffix] [VARCHAR](255) NOT NULL,

[PartitionStartDate] [DATE] NOT NULL,

[PartitionEndDate] [DATE] NOT NULL,

[PartitionStartDateID] AS (CONVERT( [INT], CONVERT([CHAR](8), [PartitionStartDate], (112)))) PERSISTED,

[PartitionEndDateID] AS (CONVERT( [INT], CONVERT([CHAR](8), [PartitionEndDate], (112)))

)

CONSTRAINT [PK_PartitionList] PRIMARY KEY CLUSTERED

(

[PartitionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE dbo.[PartitionList]

ADD CONSTRAINT [DF_PartitionList_CreatedDate] DEFAULT(GETDATE()) FOR [CreatedDate]

GO

ALTER TABLE dbo.[PartitionList]

ADD CONSTRAINT [DF_PartitionList_Active] DEFAULT((0)) FOR [Active]

GO

–DELETE dbo.[PartitionList]

–SELECT *

–FROM dbo.[PartitionList]

Step 2: After created the Partition List Table, we need to populate the list with data. Assuming you need to create weekly partitions for year 2016. And we will need a calendar table(most organization will have calendar table, and if you don’t have, you can find plenty of sources online. So here I assume you have one). 

DECLARE @dynamicSQL VARCHAR(MAX)

SET @dynamicSQL = ”

SELECT @dynamicSQL = @dynamicSQL+’INSERT INTO dbo.[PartitionList] (

[PartitionNameSuffix]

,[PartitionStartDate]

,[PartitionEndDate])

VALUES (”’+CAST(CalendarYear AS CHAR(4))+’W’+RIGHT(‘0’+CAST(CalendarWeekNumber AS VARCHAR(2)), 2)+”’

, ”’+CONVERT(VARCHAR(50), [Date])+”’

, ”’+CONVERT(VARCHAR(50), DATEADD(day, 6, [Date]))+”’)’

FROM Ancillary.DBO.V_Calendar x WITH (NOLOCK)

WHERE [Date] >= ‘2016-01-01’

AND [Date] < ‘2017-01-01’

AND ([Day Number Of Week] = 1

OR [Date] = ‘2016-01-01’)

ORDER BY x.[Date]

EXEC (@dynamicSQL)

UPDATE PartitionList

SET

active = 1

WHERE PartitionID = 1

UPDATE PartitionList

SET

PartitionEndDate = ‘2016-01-02’

WHERE PartitionID = 1

UPDATE PartitionList

SET

active = 1

WHERE PartitionID = 1

SELECT *

FROM PartitionList

Step 3: Create a table called Modelinfo which provide the information about the model and the table in the model you are partitioning at. 

IF OBJECT_ID(‘dbo.ModelInfo’) IS NOT NULL

BEGIN

DROP TABLE dbo.ModelInfo

END

CREATE TABLE dbo.ModelInfo

(ModelInfoID INT IDENTITY(1, 1)

NOT NULL,

DatabaseID VARCHAR(200),

CubeID VARCHAR(200),

MeasureID VARCHAR(200),

DatasourceID VARCHAR(200),

TableName VARCHAR(200),

PartitionField VARCHAR(200),

ServerName VARCHAR(200)

)

DELETE dbo.modelinfo

INSERT INTO dbo.ModelInfo

VALUES

(‘(modelName)‘,

(Usually you just need to put – Model)‘,

(ModelTableId which can be found in partition processing script)‘,

(DatasourceID which can be found when you trying to script on creating an new partition through management studio)‘,

(the table name in the database which your model table is trying to query at)‘,

(the field to partition at)‘,

(The server which the model is located at)

)

Step 3: Create a table called TableList which provide the information about partition id and queries on the targeted model table. Assume in our table has a field called [System] and we need to partition the table by system and by week

IF OBJECT_ID(‘dbo.TableList’) IS NOT NULL

BEGIN

DROP TABLE dbo.TableList

END

CREATE TABLE dbo.TableList

([PartitionID] [INT] NOT NULL,

[System] VARCHAR(255) NOT NULL,

[PartitionName] VARCHAR(255) NOT NULL,

[TableName] [VARCHAR](255) NOT NULL,

[PartitionQuery] [VARCHAR](MAX) NULL,

[Active] [INT] NOT NULL

)

ON [PRIMARY]

ALTER TABLE dbo.[TableList]

ADD CONSTRAINT [DF_Table_Active] DEFAULT((0)) FOR [Active]

Step 4: Populate the TableList with data regarding the tables partition queries

IF OBJECT_ID(‘TEMPDB..#TempModelInfo’) IS NOT NULL

BEGIN

DROP TABLE #TempModelInfo

END

SELECT *

INTO #TempModelInfo

FROM dbo.ModelInfo

WHILE(

(

SELECT COUNT(*)

FROM #TempModelInfo

) > 0)

BEGIN

DECLARE @ModelInfoID INT=

(

SELECT MIN(ModelInfoID)

FROM #TempModelInfo

)

DECLARE @TableName VARCHAR(200)=

(

SELECT TableName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @PartitionField VARCHAR(200)=

(

SELECT PartitionField

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @int INT= 1

WHILE(@int < 54)

BEGIN

IF OBJECT_ID(‘TEMPDB..#TempSystems’) IS NOT NULL

BEGIN

DROP TABLE #TempSystems

END

SELECT ‘SystemA’ AS SystemName

INTO #TempSystems

UNION

SELECT ‘SystemB’

UNION

SELECT ‘SystemC’

UNION

SELECT ‘SystemD’

WHILE(

(

SELECT COUNT(*)

FROM #TempSystems

) > 0)

BEGIN

DECLARE @System VARCHAR(50)=

(

SELECT MIN(SystemName)

FROM #TempSystems

)

DECLARE @FilterStart VARCHAR(50)= CAST(

(

SELECT PartitionStartDate

FROM dbo.PartitionList

WHERE PartitionID = @int

) AS VARCHAR(50))

DECLARE @FilterEnd VARCHAR(50)= CAST(

(

SELECT PartitionEndDate

FROM dbo.PartitionList

WHERE PartitionID = @int

) AS VARCHAR(50))

DECLARE @PartitionName VARCHAR(100)= @System +

(

SELECT PartitionNameSuffix

FROM dbo.PartitionList

WHERE PartitionID = @int

)

INSERT INTO dbo.TableList

(TableName,

PartitionID,

System,

PartitionName,

PartitionQuery

)

VALUES

(@TableName,

@int,

@System,

@PartitionName,

‘SELECT * FROM dbo.’+@TableName+’ WHERE ‘+’LTRIM(RTRIM([System])) =”’+@System+”’ and ‘+@PartitionField+’ between ”’+@FilterStart+”’ AND ”’+@FilterEnd+”’ ‘

)

DELETE FROM #TempSystems

WHERE SystemName = @System

END

SET @int = @int + 1

END

DELETE FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

END

Step 5: Create a store procedure, by excuting the store procedure it will create a job in your SQL server agent. You can double check the properties of the job and make sure everything looks good. By triggering the job, your partitions will created in the model you are targeted at

Create PROC [dbo].[CreatePartitions]

AS

DECLARE @UserName VARCHAR(200)=

(

SELECT CURRENT_USER

)

IF OBJECT_ID(‘TEMPDB..#TempModelInfo’) IS NOT NULL

BEGIN

DROP TABLE #TempModelInfo

END

SELECT *

INTO #TempModelInfo

FROM dbo.ModelInfo

WHILE(

(

SELECT COUNT(*)

FROM #TempModelInfo

) > 0)

BEGIN

DECLARE @ModelInfoID INT=

(

SELECT MIN(ModelInfoID)

FROM #TempModelInfo

)

DECLARE @ServerName VARCHAR(200)=

(

SELECT ServerName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @DatabaseID VARCHAR(200)=

(

SELECT DatabaseID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @CubeID VARCHAR(200)=

(

SELECT CubeID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @MeasureGroupID VARCHAR(200)=

(

SELECT MeasureID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @DatasourceID VARCHAR(200)=

(

SELECT DatasourceID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @TableName VARCHAR(200)=

(

SELECT TableName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @JobName VARCHAR(255)= ‘CreatePartition_’+@DatabaseID+’_’+@TableName

EXEC msdb.dbo.sp_add_job

@job_name = @JobName,

@enabled = 1,

@notify_level_eventlog = 0,

@notify_level_email = 0,

@notify_level_netsend = 0,

@notify_level_page = 0,

@delete_level = 0,

@category_name = N'[Uncategorized (Local)]’,

@owner_login_name = @UserName

IF OBJECT_ID(‘TEMPDB..#TempSystems’) IS NOT NULL

BEGIN

DROP TABLE #TempSystems

END

SELECT ‘System1’ AS SystemName

INTO #TempSystems

UNION

SELECT ‘System2’

UNION

SELECT ‘System3’

UNION

SELECT ‘System4’

WHILE(

(

SELECT COUNT(*)

FROM #TempSystems

) > 0)

BEGIN

DECLARE @int INT= 1

DECLARE @OnSuccess INT= 3

WHILE(@int < 54)

BEGIN

DECLARE @System VARCHAR(50)=

(

SELECT MIN(SystemName)

FROM #TempSystems

)

DECLARE @PartitionQuery VARCHAR(800)=

(

SELECT PartitionQuery

FROM [TableList] WITH (NOLOCK)

WHERE PartitionID = @int

AND [System] = @System

AND [TableName] = @TableName

)

DECLARE @PartitionName VARCHAR(200)=

(

SELECT PartitionName

FROM [TableList] WITH (NOLOCK)

WHERE PartitionID = @int

AND [System] = @System

AND [TableName] = @TableName

)

DECLARE @PartitionCmd VARCHAR(MAX)= ‘

<Alter AllowCreate=”true” ObjectExpansion=”ExpandFull” xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

‘+@DatabaseID+’

‘+@CubeID+’

‘+@MeasureGroupID+’

‘+@PartitionName+’

<ObjectDefinition>

<Partition xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:ddl2=”http://schemas.microsoft.com/analysisservices/2003/engine/2&#8243; xmlns:ddl2_2=”http://schemas.microsoft.com/analysisservices/2003/engine/2/2&#8243; xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100&#8243; xmlns:ddl200=”http://schemas.microsoft.com/analysisservices/2010/engine/200&#8243; xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200&#8243; xmlns:ddl300=”http://schemas.microsoft.com/analysisservices/2011/engine/300&#8243; xmlns:ddl300_300=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300&#8243; xmlns:ddl400=”http://schemas.microsoft.com/analysisservices/2012/engine/400&#8243; xmlns:ddl400_400=”http://schemas.microsoft.com/analysisservices/2012/engine/400/400″&gt;

<ID>’+@PartitionName+'</ID>

<Name>’+@PartitionName+'</Name>

<Source xsi:type=”QueryBinding”>

<DataSourceID>’+@DataSourceID+'</DataSourceID>

<QueryDefinition>’+@PartitionQuery+'</QueryDefinition>

</Source>

<StorageMode valuens=”ddl200_200″>InMemory</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<ddl300_300:DirectQueryUsage>InMemoryOnly</ddl300_300:DirectQueryUsage>

</Partition>

</ObjectDefinition>

</Alter>’

IF @int = 53

AND @System = ‘System4’

BEGIN

SET @OnSuccess = 1

END

EXEC msdb.dbo.sp_add_jobstep

@job_name = @JobName,

@step_name = @PartitionName,

@step_id = @int,

@cmdexec_success_code = 0,

@on_success_action = @OnSuccess,

@on_fail_action = 2,

@retry_attempts = 0,

@retry_interval = 0,

@os_run_priority = 0,

@subsystem = N’ANALYSISCOMMAND’,

@command = @PartitionCmd,

@server = @ServerName,

@database_name = N’master’,

@flags = 0,

@proxy_name = N’BAO_SSIS’

SET @int = @int + 1

END

–EXEC msdb.dbo.sp_start_job

— @job_name = @JobName

DELETE FROM #TempSystems

WHERE SystemName = @System

END

EXEC msdb.dbo.sp_update_job

@job_name = @JobName,

@start_step_id = 1

EXEC msdb.dbo.sp_add_jobserver

@job_name = @JobName,

@server_name = N’(put here the server name where your agent job at)

DELETE FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

END

Useful Scripts

SQL Server:

  • Change job schedule and ownership

USE [msdb]

EXECUTE [dbo].[usp_change_job_owner] ‘(jobname)’, ‘(userid)’

USE [msdb]

EXECUTE dbo.usp_change_job_schedule_owner

‘(job name)’,'(schedulename)’,'(userid)’

Analysis Server

Model Data Dictionary

  • Model All fields

SELECT

[CATALOG_NAME] as [DATABASE]

, [CUBE_NAME] AS [CUBE]

,[DIMENSION_UNIQUE_NAME] AS [DIMENSION]

, HIERARCHY_DISPLAY_FOLDER AS [FOLDER]

,HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE]

, HIERARCHY_IS_VISIBLE AS [VISIBLE]

FROM $system.MDSchema_hierarchies

WHERE [CUBE_NAME] =’Model’

  • model measures

select

[catalog_NAME]

,[MEASUREGROUP_NAME]

,[measure_name]

,[expression]

,[measure_is_visible]

from $System.MDSCHEMA_MEASURES

where cube_name = ‘Model’

  • Model Hierarchies

SELECT

*

FROM $system.MDSchema_hierarchies

WHERE CUBE_NAME =’Model’

  • Model Relationships

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

WHERE OBJECT_TYPE = ‘RELATIONSHIP’

Monitoring AS server user activities

Select * from $System.discover_sessions

order by session_used_memory desc

Select * from $System.discover_locks

order by lock_object_id

select * from $System.discover_object_memory_usage

–order by object_Parent_path

order by object_memory_nonshrinkable desc

select * from $System.discover_memoryusage

SELECT * FROM $SYSTEM.DISCOVER_COMMANDS

SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

order by session_spid

GO

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

GO

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

<SPID>123456</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

Blog at WordPress.com.

Up ↑