SSAS MDX Queries part 2

Children Property

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].children on rows
from
[Adventure Works]

Exists Function

select
exists([Customer].[Customer Geography].[Customer].[Crystal Zheng],[Customer].[Customer Geography].members) on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Parent Function

select
[Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.parent on rows
from
[Adventure Works]

Ancestor Function

select
[Measures].[Internet Sales Amount] on columns,
ancestor([Customer].[Customer Geography].[Customer].
[Crystal Zheng],[Customer].[Customer Geography].[City]) on rows
from
[Adventure Works]

select
[Measures].[Internet Sales Amount]on columns,
ancestor([Customer].[Customer Geography].[Customer].[Crystal Zheng],2) on rows
from
[Adventure Works]

Ascendants Function

 Returns all the ancestors of a specified member and the member itself up to the root of the member's hierarchy. Note that the specified member is returned first, then its parent, its grandparent and so on.

Example :

select
[Measures].[Internet Sales Amount]on columns,
ascendants([Customer].[Customer Geography].[Postal Code].&[OX14 4SE]&[Oxford]&[ENG]) on rows
from
[Adventure Works]

Ascendants with Hierarchize Function

Select
[Measures].[Internet Sales Amount]on columns,
hierarchize(ascendants([Customer].[Customer Geography].[Customer].[Crystal Zheng])) on rows
from
[Adventure Works]

FirstChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.firstchild on rows
from
[Adventure Works]

LastChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.lastchild
on rows
from
[Adventure Works]

Lastsibling  Function

select [Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].lastsibling on rows
from [Adventure Works]

Descendants Function 

Syntax:

1. DESCENDANTS( «Member»[, «Level»[, «Desc_flags»]] )
2. DESCENDANTS( «Member»,«Distance»[, «Desc_flags»] ) 

According to MSDN, descendants MDX function returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Desc_flags has 8 different flags option for returning set of descendant members. Here is description of each option mentioned in MSDN.
 
SELF Returns only descendant members from the specified level or at the specified distance. The function includes the specified member, if the specified level is the level of the specified member.

AFTER Returns descendant members from all levels subordinate to the specified level or distance.

BEFORE Returns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.

BEFORE_AND_AFTER Returns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.

SELF_AND_AFTER Returns descendant members from the specified level or at the specified distance and all levels subordinate to the specified level, or at the specified distance. S

SELF_AND_BEFORE Returns descendant members from the specified level or at the specified distance, and from all levels between the specified member and the specified level, or at the specified distance, including the specified member.

SELF_BEFORE_AFTER Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member. 

Examples :

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[City])on rows
from [Adventure Works]

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country] .[France],[Customer].[Customer Geography].[City],self) on rows
from
[Adventure Works]

Descendants with after

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],after)on rows
from
[Adventure Works] 

Descendants with before 

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],before) on rows
from [Adventure Works]

Range Function 

select
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q3 CY 2003]
on columns,[Measures].[Reseller Sales Amount]on rows
from [Adventure Works] 

Head(Top) Function 

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,head([Customer].[Customer Geography].[Country],2)
on rows from [Adventure Works] 

--It will display first 2 countries from the dimension

Tail(Bottom) Function 

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,tail([Customer].[Customer Geography].[Country],3)
on rows from [Adventure Works]

--it will show last three members of the Country level.

SSAS MDX Queries part 1

Simple MDX query 

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Category].MEMBERS ON ROWS
FROM [Adventure Works]

Hiding Nulls

select non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]}
on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}
on rows 

from [Adventure Works]

Introducing .members

select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Individual Members

select
[Product].[Category].[Category].[Bikes] on columns,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

Multiple Members

select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on columns ,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

User Hierarchies

User hierarchies are created when dimensions are designed in BIDS. A user hierarchy is a
hierarchy that has three or more levels (including the All level at the top).

select
{[Product].[Product Categories].Allmembers}on columns
,{[Product].[Category].Allmembers} ON ROWS
from
[Adventure Works]WHERE [Measures].[Sales Amount]

Same Non-measure Dimension on Two Axes with Differing Hierarchies

select
[Date].[Calendar].[Calendar Year] on columns,
[Date].[Month of Year].[Month of Year] on rows
from [Adventure Works]

Cross Joins

If you crossjoin two sets of members from
the same dimension, the two sets must be based on different hierarchies within the
same dimension. You can even crossjoin two different dimension

Example 1 :
select
NONEMPTY({[Product].[Product Categories].Allmembers})on columns,
NONEMPTY(crossjoin([Date].[Calendar].[Calendar Year], [Date].[Month of Year].[Month of Year])) on rows
from
[Adventure Works] WHERE [Measures].[Internet Sales Amount]

Example 2:

select
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from [Adventure Works]

Example 3:

select
crossjoin([Product].[Product Categories].[Category],{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]}) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 4(Crossjoin on Two Separate Non-measure Dimensions)

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category]) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 5:(Nested Cross Jons)

select
crossjoin([Sales Territory].[Sales Territory].[Country],crossjoin([Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Alternate for Cross Joins

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})on columns,
([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])on rows
from
[Adventure Works]

How to resolve when Distribution Database is growing huge

huge database is kind of relative, but generally if you see Distribution database growing more the 25gig it means the Cleanup processes is having a hard time deleting replicated transactions.  I’ll cover the how and why on Cleanup processes later, but for now I wanted to post a technique we’ve used to purge rows from the Distribution database.  This solution involves modifying the SQL Replication stored procedures to increase the number or rows being deleted per transaction.  If you’re uncomfortable making the code change, skip down to STEP 7).

This first posting coverage a “conservative�� approach.  Later I’m post steps for a more “aggressive” solution.

1) script msrepl_commands cleanup proc and save original sp code

sp_helptext  sp_MSdelete_publisherdb_trans

2) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_publisherdb_trans

3) change all 3 locations from 2000 to 100000 rows

DELETE TOP(2000) MSrepl_commands . . .

4) script msrepl_transaction cleanup proc and save original sp code

sp_helptext sp_MSdelete_dodelete

5) change from CREATE to ALTER

ALTER PROCEDURE sp_MSdelete_dodelete

6) change both locations from 5000 to 100000 rows

delete TOP(5000) MSrepl_transactions . . .

7) Determine oldest day containing transactions

--(shows breakout by day, by hour.  Took 2 hours on 350million rows, 100gb distribtuion db)
SELECT T.[publisher_database_id]
,datepart(mm,[entry_time]) 'month'
, datepart(dd,[entry_time]) 'day'
, datepart(hh,[entry_time]) 'hour'
    ,count(C.[xact_seqno]) 'count of commands'
FROM [distribution].[dbo].[MSrepl_transactions](nolock) T
JOIN [MSrepl_commands](nolock) C
ON T.[xact_seqno] = C.[xact_seqno]
GROUP BY T.[publisher_database_id]
  ,datepart(mm,[entry_time])
  , datepart(dd,[entry_time])
  , datepart(hh,[entry_time])
order by 1,2,3,4
--Or, just select oldest 10 rows and note the entry_time stamp.
--(select took 5 minutes on 350million rows, 100gb distribtuion db)
SELECT TOP 10 * FROM [distribution].[dbo].[MSrepl_transactions](nolock)

8) Execute cleanup via SSMS or a TSQL job to delete JUST oldest day.  (24 hours @ 5 days = 120), then continue to reduce the @max_distretention valued by a few hours for each run.

   EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 120 

Example output: (4 hours to removed 340million rows)

Removed 3493 replicated transactions consisting of 343877158 statements in 15043 seconds (22859 rows/sec).

Hope you found this helpful,

SQL server Date Time Function

Datetime functions allow manipulating columns with DATETIME/SMALLDATETIME data types. SQL server DateTime Function:

GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE Functions are Nondeterministic function. Both functions returns the current date and time. GETDATE returns current system date and time of the computer where SQL Server is running.

GETUTCDATE returns current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

DATEADD Functions

DATEADD function is Deterministic function. DATEADD Function adds a certain interval of time to the specified date and time value.

Syntax: DATEADD (datepart , number, date )

DATEADD returns a new date time value based on adding an interval to the specified date and time value.

DATEDIFF Function

DATEDIFF function is Deterministic function. DATEDIFF () gives the difference between the two date values.

Syntax: DATEDIFF ( datepart , startdate , enddate )

DATEDIFF returns number of date and time boundaries crossed between two specified dates. In DATEDIFF function start date is subtracted from end date. If start date is later than end date, a negative value is returned.

DATEPART Function

To retrieve any part of date and time use DATEPART function.

Syntax: DATEPART ( datepart , date )

DATEPART function takes two arguments 1)part of the date that you want to retrieve and 2)date itself. The DATEPART function returns an integer that represents date part of specified date.

DATEPART	Output
SELECT DATEPART(year, '2009-02-13 18:35:06.523')	2009
SELECT DATEPART(quarter, '2009-02-13 18:35:06.523')	1
SELECT DATEPART(month, '2009-02-13 18:35:06.523')	2
SELECT DATEPART(dayofyear, '2009-02-13 18:35:06.523')	44
SELECT DATEPART(day, '2009-02-13 18:35:06.523')	13
SELECT DATEPART(week, '2009-02-13 18:35:06.523')	7
SELECT DATEPART(weekday, '2009-02-13 18:35:06.523')	6
SELECT DATEPART(hour, '2009-02-13 18:35:06.523')	18
SELECT DATEPART(minute, '2009-02-13 18:35:06.523')	35
SELECT DATEPART(second, '2009-02-1 18:35:06.523')	6
SELECT DATEPART(millisecond, '2009-02-1 18:35:06.523')	523

DATENAME Function

DATENAME Function returns a character string that represents date part of the specified date.

Syntax: DATENAME ( datepart , date )

DATENAME function takes two arguments same as DATEPART function 1) part of the date that you want to retrieve and 2) date itself.

If you want the name of month
SELECT DATENAME (month, '2009-02-13 18:35:06.523')  => Output : February

If you want the name of week day
SELECT DATENAME (weekday, '2009-02-13 18:35:06.523')  => Output : Friday

DAY, MONTH, and YEAR Functions

All of this DAY, MONTH, and YEAR functions takes a single date value as a argument. Each of this function returns an integer that represents respective portions of the date.

SELECT   DAY('2009-02-1 18:35:06.523') as 'Day', MONTH('2009-02-1 18:35:06.523') as 'Month', YEAR('2009-02-1 18:35:06.523') as 'Year'

Each of this functions equivalent to DATEPART function. Like DAY is equivalent to DATEPART (dd, date), MONTH is equivalent to DATEPART (mm, date) and YEAR is equivalent to DATEPART (yy, date).

How to Archive your SQL Errorlogs into a Table

Have you noticed your SQL Server errorlogs taking up a lot of disk space?  Ever wonder what was logged a few weeks ago, but the logs have already rolled over?  Why not create a SQL Agent job and archive your SQL Agent and SQL errorlogs into a table then recycle the error logs using the SQL statements below.

--Create Tables statements only need to be executed once
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[MS_SQLerrorlog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MS_SQLerrorlog](
    [LogDate] [datetime] NULL,
    [ProcessInfo] [varchar](10) NULL,
    [Text] [varchar](max) NULL
) ON [PRIMARY]
END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[MS_SQLAgentlog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MS_SQLAgentlog](
    [LogDate] [datetime] NULL,
    [ProcessInfo] [varchar](10) NULL,
    [Text] [varchar](max) NULL
) ON [PRIMARY]
END
GO


--Save current SQL Server Errorlog
INSERT INTO MS_SQLerrorlog
      EXEC ('sp_readerrorlog')

Go 
Exec msdb.dbo.sp_cycle_errorlog
Go

--Save current SQL Agent log
INSERT INTO MS_SQLAgentlog
    EXEC ('sp_readerrorlog -1, 2')
Go
Exec msdb.dbo.sp_cycle_agent_errorlog 
Go