SSAS MDX: Converting a Dimension Value to Calculated Measure

I have a dimensional attribute "Target TOS Monthly", in order to convert this dimensional attribute to measure added a calculated measure with below code.


SUM([Account].[Target TOS Monthly].[Target TOS Monthly].MEMBERS, [Account].[Target TOS Monthly].MEMBER_VALUE)

How to filter MDX result Set by Current Year?

Below MDX query is to perform dynamic monthly comparison of a measure with current and previous year months,  to achieve this, a current year parameter is applied as like below

WITH
MEMBER [Measures].[ActiveLearnerPercentPreviousYear] as ([Measures].[Active Learner %],
PARALLELPERIOD ([MonthlyPeriod].[Calendar Period].[Month],12,[MonthlyPeriod].[Calendar Period].CurrentMember)),  FORMAT_STRING ='Percent'
MEMBER [Measures].[ActiveLearnerPercentCurrentYear]   as([Measures].[Active Learner %])
SELECT  {[Measures].[ActiveLearnerPercentCurrentYear] , [Measures].[ActiveLearnerPercentPreviousYear] } ON COLUMNS ,
([MonthlyPeriod].[Calendar Period].[Month], [MonthlyPeriod].[Month No].[Month No]) ON ROWS
FROM [MonthlyMetrics]
WHERE (
      strtomember("[MonthlyPeriod].[Year].&[" + Format(now(), "yyyy") + "]")
      )

Creating Named Set for Current Month, Last 6 Months, and Parallel Period Months

Assume you have a date attributes in the following format:

[Date].[Calendar Date].[Year].&[1999].&[1].&[1].&[01/16/1999]

First create a named set [Current month], with below script. Then followed by  Last 6 Months and parallel period.

Current Month:
StrToMember('[Date].[Calendar Date].[Date].&[' + FORMAT(Now(), "MM/dd/yyyy") + ']').Parent

 Last 6 Months:
TAIL({NULL: [Current Month].ITEM(0)}, 6)

 Last 6 Months - Previous Year
TAIL({NULL: PARALLELPERIOD([Date].[Calendar Date].[Year], 1, [Current Month].ITEM(0))}, 6)

MDX Named Set to Get Months of Current and Previous Years

MDX to Create Named Set to Get Months of Current and Previous Years

WITH

SET [Previous Year Months]

AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[12]'))

SET [Current Year Months]

AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[12]'))

SELECT [Previous Year Months] ON COLUMNS

FROM [CUBE]

SSAS Error: No Mapping between Account Names and Security IDs was Done

We may come across the below error mostly while we are deploying SSAS cube in to one server:

It is because of difference in domain between development server and deployment server. In that cases Goto Roles --> Membership tab, and remove non-supportive members from the role.

ERROR: SSAS Cube has no linked measure groups

When we work on deploying SSAS cubes, we may come across the following error:

Errors in the metadata manager. The cube has no linked measure groups. Errors in the metadata manager. 
An error occurred when loading the ..... cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales_sample.0.db\Sales ~.2.cub.xml'.

To resolve this issue perform the below operation:

1. Stop Analysis Service 
2. Navigat to the respective folder where you store Analysis service data "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\".
3. Delete the main folder of the Analysis Service database, e.g., if your analysis service db name is Sales. Then delete the folder named Sales in the path.
4. Also delete Sales.cub.xml file ion the path.
5. Restart the Analysis service.
6.  Deploy the cube.

Example MDX Calculation for SSAS Cube

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock - Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock - Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period - Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period - Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])             , ([Time].CurrentMember, [Measures].[Unit Sales])               < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales]))) / Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members      , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members        , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select       {{{[Measures].[Products Down]}         * {[Customers].DefaultMember}         * {[Product].DefaultMember}}} on columns      , {[Time].&[1998].&[Q1].&[1]         , [Time].&[1998].&[Q1].&[2]         , [Time].&[1998].&[Q1].&[3]         , [Time].&[1998].&[Q2].&[4]         , [Time].&[1998].&[Q2].&[5]         , [Time].&[1998].&[Q2].&[6]         , [Time].&[1998].&[Q3].&[7]         , [Time].&[1998].&[Q3].&[8]         , [Time].&[1998].&[Q3].&[9]         , [Time].&[1998].&[Q4].&[10]         , [Time].&[1998].&[Q4].&[11]} on rows  from      [Sales_MDX2]

MDX Query

select     {[Measures].[Unit Sales]      , [Measures].[MA]} on columns     , {Descendants([Time].[Year].&[1997], [Month])} on rows from     [Sales_MDX2]

Convert Seconds to hours, minute, seconds (hh:mm:ss) in MDX

WITH MEMBER [Measures].[TimSpent (HH:MM:SS)] AS '[Measures].[Timespent]/ 86400', FORMAT_STRING = "hh:mm:ss"

SELECT [Assignment].[Element].[Element] ON ROWS, {[Measures].[Timespent], [Measures].[TimSpent (HH:MM:SS)]} ON COLUMNS

FROM [Activity]

FIX: A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Most often we may come across issues like below when tried to connect Analysis Service Data Connection:
"A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)"

To fix this follow below steps:
Step 1: Navigate to Programs|SQL Server XXXX|Configuration tools|SQL Server Surface Area Configuration

Step 2: Change the service account to local system as shown below:

DimDate - Date Dimension

The below script helps to create valid date dimension attribures:


USE

[DatabaseName]

GO

IF OBJECT_ID('Date','U') IS NOT NULL

DROP TABLE Date

GO

　

CREATE TABLE [dbo].[Date](

[DateSK] [int] NOT NULL,

[FullDate] [datetime] NOT NULL,

[DateName] [char](11) NOT NULL,

[DayOfWeek] [tinyint] NOT NULL,

[DayNameOfWeek] [char](10) NOT NULL,

[DayOfMonth] [tinyint] NOT NULL,

[DayOfYear] [smallint] NOT NULL,

[WeekdayWeekend] [char](7) NOT NULL,

[WeekOfYear] [tinyint] NOT NULL,

[MonthName] [char](10) NOT NULL,

[MonthOfYear] [tinyint] NOT NULL,

[CalendarQuarter] [tinyint] NOT NULL,

[CalendarYear] [smallint] NOT NULL,

[CalendarYearMonth] [char](7) NOT NULL,

[CalendarYearQtr] [char](15) NOT NULL,

CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)

) ON [PRIMARY]

GO

RAISERROR('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime

-- Set StartDate and EndDate as per your requirement

SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )

BEGIN

INSERT INTO Date

SELECT

CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK

,@StartDate AS [Date]

,CONVERT(varchar(20),@StartDate,106) AS DateName

,DATEPART(DW,@StartDate) [DayOfWeek]

,DATENAME(DW,@StartDate) [DayNameOfWeek]

,DATENAME(DD,@StartDate) [DayOfMonth]

,DATENAME(DY,@StartDate) [DayOfYear]

,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'

ELSE 'WeekDay' END [WeekdayWeekend]

,DATEPART(WW,@StartDate) [WeekOfYear]

,DATENAME(MM ,@StartDate) [MonthName]

,DATEPART(MM ,@StartDate) [MonthOfYear]

,DATEPART(QQ,@StartDate) [CalendarQuarter]

,DATEPART(YY ,@StartDate) [CalendarYear]

,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2) [CalendarYearMonth]

,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

SET @StartDate = @StartDate +1

END

GO