SSAS Cube Calculations (YTD, BTD, PY, PYTD)

Actual YTD

AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[Financial Year],[Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual BTD

AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[(All)],[Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual PY
( ParallelPeriod ( [Financial Date].[Calendar FY-M].[Financial Year], 1, [Financial Date].[Calendar FY-M].CurrentMember ), [Measures].[Actual] )

Actual PYTD
AGGREGATE ( PERIODSTODATE( [Financial Date].[Calendar FY-M].[Financial Year]
    , ParallelPeriod ( [Financial Date].[Calendar FY-M].[Financial Year], 1, [Financial Date].[Calendar FY-M].CurrentMember ) )

Collation Conflict between databases (tempdb and user databases)

Msg 468, Level 16, State 9, Line 21
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Use the COLLATE DATABASE_DEFAULT suffix on VARCHAR columns:

SELECT *
FROM hif_dim_account AS a
INNER JOIN #temp_hierarchy AS t
  ON t.account COLLATE DATABASE_DEFAULT = a.account COLLATE DATABASE_DEFAULT
INNER JOIN hif_dim_hierarchy AS h ON h.business_description COLLATE DATABASE_DEFAULT = t.business_description COLLATE DATABASE_DEFAULT

CUBEVALUE() and CUBEMEMBER() and CUBESET(), oh my!

Excel Cube functions (reference)

Return a value from the cube.

=CUBEVALUE(connection,member_expression1,member_expression2…)

=CUBEVALUE("Cube Connection",$B$4,$B$5,$B$52,$B$50,$A40)

=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")

Returns a member or tuple from the cube.

=CUBEMEMBER(connection, member_expression, [caption])

=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&[MAR FY2013])

=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&["&B6&"]")

Defines a calculated set of members or tuple. Can be used as a member expression by the CUBEVALUE() function.

=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

=CUBESET("Cube Connection", "FILTER( [Invoice Date].[Hierarchy].members, [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) > "&B47&" AND [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) < 65000)", "(Multiple Items)")

=CUBESET("Cube Connection","{[Invoice Date].[Hierarchy].&[2012],[Invoice Date].[Hierarchy].&[JAN FY2013],[Invoice Date].[Hierarchy].&[FEB FY2013],[Invoice Date].[Hierarchy].&[MAR FY2013]}","(Multiple Items)")

Return the Key value from selected member

[Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED)

SQL Server System Information returned by SERVERPROPERTY()

Copied from a post on SQLserverCentral.com by Gokhan Varol.
http://www.sqlservercentral.com/scripts/87561/

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
 @SqlPath NVARCHAR(255),
 @InstName VARCHAR(16) = @@SERVICENAME,
 @value_name NVARCHAR(20),
 @LoginMode_Value INT,
 @LoginMode NVARCHAR(15),
 @RegLoc VARCHAR(100),
 @ProcessorInfo VARCHAR(256)
--#region Retrieve Port Number
SET @RegLoc = CASE WHEN @InstName = 'MSSQLSERVER' THEN 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' ELSE 'Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\' END
DECLARE
 @RegRead TABLE(
                 VALUE VARCHAR(4000),
                 DATA VARCHAR(4000))
INSERT INTO @RegRead(
            Value,
            Data)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort';
--#endregion
--#region Get processor description from Windows Registry
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString', @ProcessorInfo OUTPUT, N'no_output'
--#endregion
--#region Find Service Accounts
DECLARE
 @ServicePath NVARCHAR(256) = N'SYSTEM\CurrentControlSet\Services\' + CASE
                                                                            WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'MSSQLSERVER'
                                                                         ELSE 'MSSQL$' + @@SERVICENAME
                                                                        END,
 @AgentServicePath NVARCHAR(256) = N'SYSTEM\CurrentControlSet\Services\' + CASE
                                                                                 WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLSERVERAGENT'
                                                                             ELSE 'SQLAgent$' + @@SERVICENAME
                                                                             END,
 @MSSQLServiceAccountName VARCHAR(256),
 @SQLAgentServiceAccountName VARCHAR(256)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @AgentServicePath, N'ObjectName', @SQLAgentServiceAccountName OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @ServicePath, N'ObjectName', @MSSQLServiceAccountName OUTPUT, N'no_output'
--#endregion
--#region Retrieve Security
IF @InstName IS NULL
    SET @RegLoc = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
ELSE
    SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegLoc, @value_name = @InstName, @value = @SqlPath OUTPUT
IF @InstName IS NULL
    SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\'
ELSE
    SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegLoc, @value_name = 'LoginMode', @value = @LoginMode_Value OUTPUT
IF @LoginMode_Value = 1
    SET @LoginMode = 'Windows'
IF @LoginMode_Value = 2
    SET @LoginMode = 'Mixed'
--#endregion
--#region Get System Manufacturer and model number from
IF OBJECT_ID('tempdb..#SystemManufacturer')IS NOT NULL
    DROP TABLE
         #SystemManufacturer
CREATE TABLE #SystemManufacturer(
             LogDate DATETIME NOT NULL,
             ProcessInfor VARCHAR(256),
             Text VARCHAR(MAX)NOT NULL)
INSERT INTO #SystemManufacturer
EXEC xp_readerrorlog 0, 1, "Manufacturer";
--#endregion
;
WITH sysInfo
    AS (SELECT
             cpu_count,
             hyperthread_ratio,
             sqlserver_start_time,
             (SELECT
                     ISNULL(CAST(NULLIF(DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) / 24, 0)AS VARCHAR) + ' days ', '') + RIGHT('0' + CAST(DATEDIFF(MINUTE, StartDateTime, GETDATE()) / 60 % 24 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE, StartDateTime, GETDATE()) % 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(second, StartDateTime, GETDATE()) % 60 AS VARCHAR), 2)
                FROM(SELECT
                            DATEDIFF(DAY, sqlserver_start_time, GETDATE())AS DayDiff)AS dd
                     CROSS APPLY(SELECT
                                        CASE
                                            WHEN DayDiff > 1 THEN DATEADD(DAY, DayDiff - 1, sqlserver_start_time)
                                         ELSE sqlserver_start_time
                                        END AS StartDateTime)AS b)AS SystemUpTime,
             total_physical_memory_kb / 1024 AS TotalPhysicalMemoryMB,
             available_physical_memory_kb / 1024 AS AvailPhysicalMemoryMB,
             system_memory_state_desc AS SystemMemoryState,
             physical_memory_in_use_kb / 1024 AS MemInUseMB,
             memory_utilization_percentage AS [MemUtil%]
        FROM sys.dm_os_sys_memory AS sm(NOLOCK)
             CROSS JOIN sys.dm_os_process_memory AS pm(NOLOCK)
                         CROSS JOIN sys.dm_os_sys_info AS si(NOLOCK))
    SELECT
         CONVERT(VARCHAR(128), SERVERPROPERTY('Servername'))AS Servername,
         CONVERT(VARCHAR(128), SERVERPROPERTY('MachineName'))AS MachineName,
         CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName'))AS InstanceName,
         @LoginMode AS AuthenticationMode,
         @MSSQLServiceAccountName AS SQLServiceAccount,
         @SQLAgentServiceAccountName AS SQLAgentServiceAccount,
         (SELECT
                 CASE
                     WHEN ISNUMERIC(Data) = 1 THEN CAST(Data AS INT)
                 END
            FROM @RegRead)AS Port,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))AS ComputerNamePhysicalNetBIOS,
         CASE
             WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
             WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
             WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
         END AS IsClustered,
         (SELECT TOP 1
                 Text
            FROM #SystemManufacturer)AS SystemManufacturer,
         @ProcessorInfo AS ProcessorInfo,
         s.*,
         CONVERT(INT, SERVERPROPERTY('ProcessId'))AS ProcessId,
         CONVERT(INT, SERVERPROPERTY('IsSingleUser'))AS IsSingleUser,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'))AS ProductVersion,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ProductLevel'))AS ProductLevel,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ResourceVersion'))AS ResourceVersion,
         CASE
             WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
             WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'
         END AS IsIntegratedSecurityOnly,
         CASE
             WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
             WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
             WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition'
             WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
         END AS EngineEdition,
         CONVERT(VARCHAR(128), SERVERPROPERTY('LicenseType'))AS LicenseType,
         CONVERT(VARCHAR(128), SERVERPROPERTY('NumLicenses'))AS NumLicenses,
         CONVERT(VARCHAR(128), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion,
         CONVERT(VARCHAR(128), SERVERPROPERTY('Collation'))AS Collation,
         CONVERT(VARCHAR(128), SERVERPROPERTY('CollationID'))AS CollationID,
         CONVERT(VARCHAR(128), SERVERPROPERTY('ComparisonStyle'))AS ComparisonStyle,
         CASE
             WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('EditionID')) = -1253826760 THEN 'Desktop Edition'
             WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express Edition'
             WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard Edition'
             WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup Edition'
             WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise Edition'
             WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal Edition'
             WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer Edition'
             WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Enterprise Evaluation Edition'
             WHEN SERVERPROPERTY('EditionID') = 1044790755 THEN 'Windows Embedded SQL'
             WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express Edition with Advanced Services'
         END AS ProductEdition,
         CASE
             WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
             WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
             WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
         END AS IsFullTextInstalled,
         CONVERT(VARCHAR(128), SERVERPROPERTY('SqlCharSet'))AS SqlCharSet,
         CONVERT(VARCHAR(128), SERVERPROPERTY('SqlCharSetName'))AS SqlCharSetName,
         CONVERT(VARCHAR(128), SERVERPROPERTY('SqlSortOrder'))AS SqlSortOrderID,
         CONVERT(VARCHAR(128), SERVERPROPERTY('SqlSortOrderName'))AS SqlSortOrderName
    FROM sysInfo AS s
GO

Row Counts for All Tables in a Database

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO

SELECT * FROM #T ORDER BY NumberOfRows DESC

Search Schema for Text (eg. column names)

SELECT DISTINCT OBJECT_NAME(id) AS ObjectName
FROM syscomments WHERE [text] LIKE '%business_day%'
ORDER BY 1

Select Date Formats Using CONVERT() Function

SELECT date_key
    ,date
    ,DAY(date) AS month
    ,MONTH(date) AS month
    ,YEAR(date) AS year
    ,CONVERT(CHAR(4),date,100) + CONVERT(CHAR(4),date,120) AS a
    ,CONVERT(CHAR(10),date,103) AS b
    ,CONVERT(CHAR(11),date,113) AS c
    ,CONVERT(CHAR(10),date,120) AS d
    ,CONVERT(CHAR(11),date,112) AS e
FROM dim_date
WHERE year = 2012

Concatenate Using FOR XML

SELECT STUFF((
    SELECT ',' AS [text()], CAST(month_name AS NVARCHAR(30)) AS [text()]
    --SELECT *
    FROM dim_date
    WHERE date >= '2013-01-31' AND date <= '2013-12-31' AND date = last_day_of_month
    FOR XML PATH('')
), 1, 1, '') AS months

List of Month Names Using CTE

WITH CTEMonth
AS
(
    SELECT 1 AS month_number
    UNION ALL
    SELECT month_number + 1 FROM CTEMonth WHERE month_number <= 11
)
SELECT month_number,DATENAME(MONTH, DATEADD(MONTH, month_number, 0) - 1) [month_name] FROM CTEMonth