To retrieve the last member of a set you can use the TAIL() function.
The following example returns the total turnover over the last member in 2020-02 for company 4711.
SELECT {
TAIL(NONEMPTY([DatumStapel].[JMT].[T].Members, { ([Datenherkunft].[Datenherkunft].&[200], [Company].[Company].&[4711], [DatumStapel].[JMT].[M].&[2020]&[2]) }))
} on columns
, { [Measures].[Umsatz] } on rows
FROM [Finance]
You want to create a Calculated Member that displays the Totals of the previous year using ParallelPeriod.
See below for a Year- and at the end of the post for a Month-based version.
It is important to mention, that the Hierarchy used in ParallelPeriod function is the same Hierarchy used on an axis!
And another clue is, to use CurrentMember property on the hierarchy’s level and not below on a Sub-Level (here: J, M or T)!
WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
([Measures].[Umsatz Negiert],
ParallelPeriod([DatumStapel].[JMT].[J]
, 1
,[DatumStapel].[JMT].CurrentMember)
)
'
SELECT
{ [DatumStapel].[JMT].[J].&[2019] : [DatumStapel].[JMT].[J].&[2020] } ON columns
,{
[Measures].[Umsatz Negiert],
Measures.UmsatzNegiertLY } ON rows
FROM Finance
WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
([Measures].[Umsatz Negiert],
ParallelPeriod([DatumStapel].[JMT].[M]
, 12
,[DatumStapel].[JMT].CurrentMember)
)
'
SELECT
{ [DatumStapel].[JMT].[M].&[2019]&[1] : [DatumStapel].[JMT].[M].&[2020]&[12] } ON columns
,{
[Measures].[Umsatz Negiert],
Measures.UmsatzNegiertLY } ON rows
FROM Finance
To use the OpenQuery() procedures within SQL Server to query a SQL Server Analysis Services Cube, you need to add a Linked Server with Credentials of a valid Domain User, as below:
USE [master] GO /****** Object: LinkedServer [CUBE_FINANCEV2] Script Date: 10/15/2019 11:53:16 AM ******/ EXEC master.dbo.sp_addlinkedserver @server = N'CUBE_FINANCEV2', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'mp-bi', @catalog=N'Finance-v2' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CUBE_FINANCEV2',@useself=N'False',@locallogin=NULL,@rmtuser=N'MUC\mp_BI_SQL_AS',@rmtpassword='########' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CUBE_FINANCEV2',@useself=N'True',@locallogin=N'MUC\mp_BI_SQL_AS',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'connect timeout', @optvalue=N'45' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'CUBE_FINANCEV2', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO
To Query the Processing Status or the last date and time of processing of the Cube, you may create and execute the following Stored Procedure (that uses the created Linked Server from above):
USE [DWH]
GO
/****** Object: StoredProcedure [etl].[GetCubeProcessingStatus] Script Date: 14.10.2019 18:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Cordt Bien
-- Create date: 29.05.2019
--
-- Last Modified: Date, Name (short reason.)
--
-- Description: Returns the date and time of the last processing of a cube or perspective. May return multiple rows.
--
-- Info: EXEC etl.GetCubeProcessingStatus 'Finance-v2', NULL
-- OR EXEC etl.GetCubeProcessingStatus 'Finance-v2', 'Datev-Finance'
-- =============================================
CREATE PROCEDURE [etl].[GetCubeProcessingStatus]
@cubeDatabaseName sysname = 'Finance-v2', -- must not be null!
@cubeOrPerspectiveName sysname = NULL -- optional to limit returned result to one row.
--WITH EXECUTE AS 'InternalExecutor' -- (IF YOU USE THE "WITH EXECUTE AS" clause, the Execution will fail due to Security Context Exceptions of the Linked Server)
AS
BEGIN
SET NOCOUNT ON;
-- Check Preconditions
BEGIN TRY
IF @cubeDatabaseName IS NULL RAISERROR('cubeDatabaseName must not be null!', 11, 1);
END TRY
BEGIN CATCH
EXEC internal.ReThrowError @@PROCID;
RETURN -1;
END CATCH
-- Implementation
BEGIN TRY
IF @cubeDatabaseName = 'Finance-v2' BEGIN
SELECT DatabaseName, CubeOrPerspectiveName, CubeOrPerspectiveDisplayName, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), LastProcessDate) as LastProcessDate, BaseCubeName
FROM OpenQuery(CUBE_FINANCEV2
, N'SELECT [CATALOG_NAME] AS DatabaseName,[CUBE_NAME] AS CubeOrPerspectiveName,[CUBE_CAPTION] AS CubeOrPerspectiveDisplayName,[LAST_DATA_UPDATE] AS LastProcessDate,[BASE_CUBE_NAME] AS BaseCubeName FROM $system.mdschema_cubes WHERE CUBE_SOURCE=1')
WHERE (@cubeOrPerspectiveName IS NULL OR CAST(CubeOrPerspectiveName as nvarchar(128)) = @cubeOrPerspectiveName);
END
ELSE BEGIN
RAISERROR('A cubeDatabaseName other than "Finance-v2" is currently not supported or implemented! DEV-Info: Add a new linked server to the requested cube and insert it to this stored procedure (etl.GetCubeProcessingStatus).', 11, 1);
SELECT TOP 0 DatabaseName, CubeOrPerspectiveName, CubeOrPerspectiveDisplayName, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), LastProcessDate) as LastProcessDate, BaseCubeName
FROM OpenQuery(CUBE_FINANCEV2
, N'SELECT [CATALOG_NAME] AS DatabaseName,[CUBE_NAME] AS CubeOrPerspectiveName,[CUBE_CAPTION] AS CubeOrPerspectiveDisplayName,[LAST_DATA_UPDATE] AS LastProcessDate,[BASE_CUBE_NAME] AS BaseCubeName FROM $system.mdschema_cubes WHERE CUBE_SOURCE=1')
WHERE (@cubeOrPerspectiveName IS NULL OR CAST(CubeOrPerspectiveName as nvarchar(128)) = @cubeOrPerspectiveName);
END
END TRY
BEGIN CATCH
EXEC internal.ReThrowError @@PROCID;
RETURN -2;
END CATCH
END
Scenario: You have a small Dimension containing a Hierarchy. You need all the child members as well as the parent members to be sorted.
Solution excerpt: Add the Sort Attribute by which the Parent Attribute should be ordered by as a new Attribute Relationship to the Dimension and then set the OrderBy property to Attribute Key and the OrderByAttribute to the Sort Attribute
| Source and Version | Multidimensional | Tabular | Excel Power Pivot | ||||||
| 2008 R2 | 2012 | 2014 | 2016 | 2012 | 2014 | 2016 | 2016 Direct Query | ||
| SQL Server 7.0 / 2000 | yes | no | no | no | no | no | no | no | no |
| SQL Server 2005 | yes | yes | yes | (no)? | yes | yes | (no)? | (no)? | yes |
| SQL Server 2008 – 2012 | yes | yes | yes | yes | yes | yes | yes | yes | yes |
| SQL Server 2014 | ? | (yes)* | yes | yes | (yes)* | (yes)* | yes | yes | (yes)* |
| SQL Server 2016 | ? | (yes)* | (yes)* | yes | (yes)* | (yes)* | yes | yes | (yes)* |
| SQL Azure Database | yes | yes | yes | yes | yes | yes | yes | yes | yes |
| SQL Server Parallel Data Warehouse (PDW) / Analytics Platform System (APS) | yes | yes | yes | yes | yes | yes | yes | yes | yes |
| Oracle relational 9i, 10g, 11g | yes (9.0) | yes | yes | yes | yes | yes | yes | yes | yes |
| Oracle relational 12g | ? | ? | ? | yes | ? | ? | yes | yes | ? |
| Teradata relational V2R6, V12 | yes (v2R6) | yes | yes | yes | yes | yes | yes | yes | yes |
| IBM DB2 relational V8.1 | yes (with EE only) | yes | yes | yes | yes | yes | yes | no | yes |
| Informix relational V11.10 | no | yes | yes | yes | yes | yes | yes | no | yes |
| Sybase relational | no | yes | yes | yes | yes | yes | yes | no | yes |
| Text Files (.CSV, .TAB, .TXT) | no | no | no | no | yes | yes | yes | no | yes |
| Microsoft Excel 97-2007 (.xlsx, .xlsm, xlsb, xltx, xltm) | no | no | no | no | yes | yes | (no)? | no | yes |
| Microsoft Excel 2010 (.xlsx, .xlsm, xlsb, xltx, xltm) | no | no | no | no | yes | yes | yes | no | yes |
| Microsoft Excel 2013-2016 (.xlsx, .xlsm, xlsb, xltx, xltm) | no | no | no | no | (no)? | (no)? | yes | no | ? |
| Excel documents in SharePoint | no | no | no | no | no | no | no | no | no |
| Microsoft Access 2003 | yes (x86 only) | yes | ? | ? | yes | yes | no | no | yes |
| Microsoft Access 2007 | yes (x86 only) | yes | yes | ? | yes | yes | no | no | yes |
| Microsoft Access 2010 | ? | yes | yes | yes | yes | yes | yes | no | yes |
| Microsoft Access 2013 | ? | ? | yes | yes | (no)? | ? | yes | no | ? |
| Microsoft Access 2016 | ? | ? | ? | yes | (no)? | ? | yes | no | ? |
| Access documents in SharePoint | no | no | no | no | no | no | no | no | no |
| Power Pivot Workbook published to SharePoint | no | no | no | no | yes | yes | yes | no | yes |
| SSAS Analysis Services Cube (2005) | no | no | no | no | yes | yes | (no)? | no | yes |
| SSAS Analysis Services Cube (2008-2008R2) | no | no | no | no | yes | yes | yes | no | yes |
| SSAS Analysis Services Cube (2012-2014) | no | no | no | no | (yes)* | (yes)* | yes | no | (yes)* |
| SSAS Analysis Services Cube (2016) | no | no | no | no | ? | ? | yes | no | (yes)* |
| Data Feeds Atom 1.0 format (exposed via WCF Data Service) (.atomsvc or .atom) | no | no | no | no | yes | yes | yes | no | yes |
| Office Database Connection files | no | no | no | no | yes | yes | yes | no | yes |
Give it the name “DeliveryDays“.
2. Enter following formula:
=IF(isblank([OrderDate]);0;IF([ShipDate]<[OrderDate];0;[ShipDate]-[OrderDate]))*1
3. Go to the measure grid (bottom) and enter a new measure to the previously created column:
Average of DeliveryDays:=AVERAGE([DeliveryDays])
4. Create KPI by right clicking on the newly created Average measure:
5. A new dialog will pop up and you have to define the KPI details:
After hitting OK you will notice a KPI Indicator Symbol in your measure grid:
6. Analyze the Results in Excel:
This was a quick tutorial of how to create a KPI using a new calculated column in a Tabular Project using SSDT Visual Studio 2013.
This tutorial gives you an overview of common dialogs and windows used to configure Analysis Services Tabular Projects.
The created file will be named TabularProject1.smproj.
Further information on Compatibility Levels MSDN here.
If enabled: An ABF file is automatically created when a BIM file is saved. For further information on how to a restore a .abf file see mssqltips here. For contents of the backuped .abf file see MSDN here.
Benefits if enabled: Data will always be up-to-date. Data sets can be huge (larger than RAM of the SSAS instance). May be faster due to query acceleration in the data source (xVelocity column indexes for example). Security can be enforced (for example row-level security in RDBMS database). Complex formulas might be faster if the back-end can optimize the query plan. Restrictions: All your data in the Tabular Model must come from a single relational data source (either SQL Server, Oracle or Teradata). You must not use data from other sources. If you need data from elsewhere you first have to create a DWH that contains all data. Then you can use this DWH as the only source for your Tabular Model. Excel in DirectQuery mode: MDX queries from Excel to a Tabular Model is only supported in SQL Server 2016 (or above) and the compatibility level is set to 1200. For further information see MSDN here.
Keep in memory: The workspace database (that includes the model metadata, imported data and credentials) will be kept in memory after closing a model. Your model will load faster but you might lose all of your memory. Unload from memory: The workspace database will be written to disk on closing and reloaded into memory on reload. Should be used on a remote workspace database or if you have a limited RAM amount. Delete workspace: The workspace database will be deleted on closing your model. Your model will always need more time to load as it must restore any settings. Use this option when you are rarely working with your model.
This section describes the steps to import related Fact and Dimension tables from Adventureworks DW 2014. Go to the menu bar in SSDT, click on MODEL and then Import From Data Source…
The Table Import Wizard opens.
Select the relational data source instance and Database to connect to.
Enter the credentials of a user specific to this project to connect to the data source. You should always try to create an Active Directory user for each Project you are creating to be able to give project specific permissions to this user in the data source. Don’t use a “sa” account.
Decide whether to select data from given tables or views or by specifying your own SQL Query.
Select the Tables/Views. In this example I checked the FactInternetSales table and then used the handy Select Related Tables button.
Preview & Filter dialog:
The Data is transferred and the selected tables are imported into your model. Hit Close.
When you switch to Diagram View, you will see the following result:
For an (currently in-)complete property overview see MSDN here.
We will create a Hierarchy on the Date Dimension as follows. First you will have to click the “Create Hierarchy” button on the upper right of a table:
Then you will give it a name: “Years and Parts”. After that you drag & drop the following three columns onto the Years and Parts hierarchy column:
We will now analyze the results and the effects of the created hierarchy in Excel. Go to the menu bar in VS2013, open the MODEL menu and click Analyze in Excel.
Leave the “Current Windows User” option set in the following dialog and hit OK.
Excel opens with the following result:
You have SQL Server 2014 or SQL Server 2012 (SSAS/SSRS/SSIS)
You have SQL Server 2016 CTP (SSAS/SSRS/SSIS)
All of these SSDTs also support SQL Server 2008 and 2008 R2.
If you only have SQL Server 2008 or SQL Server 2008 R2
If you picked the SSDT-BI for VS2013 version, the installation will look like this:
