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
If you want to apply a WHERE clause to the Data of a table, for example to limit the result to only valid rows (where GETDATE() between ValidFrom and ValidTo) go to the Advanced Query Editor and add the Table.SelectRows() statement, as follows:
let
Quelle = Sql.Database("localhost\DWH_DEV", "DWH", [CommandTimeout=#duration(0, 0, 5, 0)]),
etl_Datastore = Quelle{[Schema="etl",Item="Datastore"]}[Data],
ds = Table.SelectRows(etl_Datastore, each [ValidFrom] <= DateTime.Date(DateTime.LocalNow()) and [ValidTo] >=DateTime.Date(DateTime.LocalNow()))
in
ds
If you want to display an Icon within a Table instead of a boolean True/False Text, go ahead and apply the screenshotted solution.
Remember that you have to use a Measure as basement field (Basierend auf Feld). I’ve included an example to the right in the grayed-out rectangle (Anz_Failed = … ).
Possible result may be the following…
What is the “best” PacketSize argument to establish a connection to a SQL Server?
The answer is: It depends (on your very specific network)!
In our case a setting of a value higher than 1500 Bytes does not have a positive outcome. That is because the MTU of the Network has a maximum of 1.500 Bytes per Packet. This is often the case, because the implementation of the IP Protocol v2 uses this as the maximum packet size.
To figure out, what is the maximum value in your network, use the following Power Shell Script, originally authored by Thomas Stringer (http://sqlsalt.com), found in this great article by Robert L Davies (http://sqlsoldier.net/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith):
$UpperBoundPacketSize = 1500
if ($args -eq "-Q") {$QuietMode = 1}
else {$QuietMode = 0}
$IpToPing = "mp-bi"
do {
if ($QuietMode -eq 0) {Write-Host "Testing packet size $UpperBoundPacketSize"}
$PingOut = ping $IpToPing -n 1 -l $UpperBoundPacketSize -f
$UpperBoundPacketSize -= 1
} while ($PingOut[2] -like "*fragment*")
$UpperBoundPacketSize += 1
$Mtu = $UpperBoundPacketSize + 28
Write-Host "MTU: $Mtu" -ForegroundColor Green
If the installation took place using the Windows Installer, you need to do the following:
-- Text File Content of C:\Temp\mysql_reset_root.txt
ALTER USER 'root'@'localhost' IDENTIFIED BY '{YOURNEWPASSWORDHERE}';
-- Run Cmd with Administrator rights... C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini" --init-file=C:\\Temp\\mysql_reset_root.txt
For more information go here… https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html
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
When you script your database and want to include the dbo.SYSSSISLOG table, because you’ve created Views or procedures referencing it, the following piece of code does the job:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssislog]')) CREATE TABLE [sysssislog] ( [id] [int] NOT NULL IDENTITY PRIMARY KEY, [event] [sysname] NOT NULL, [computer] [nvarchar] (128) NOT NULL, [operator] [nvarchar] (128) NOT NULL, [source] [nvarchar] (1024) NOT NULL, [sourceid] [uniqueidentifier] NOT NULL, [executionid] [uniqueidentifier] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [datacode] [int] NOT NULL, [databytes] [image] NULL, [message] [nvarchar] (2048) NOT NULL,) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] go CREATE PROCEDURE [dbo].[sp_ssis_addlogentry] @event sysname, @computer nvarchar(128), @operator nvarchar(128), @source nvarchar(1024), @sourceid uniqueidentifier, @executionid uniqueidentifier, @starttime datetime, @endtime datetime, @datacode int, @databytes image, @message nvarchar(2048)AS INSERT INTO sysssislog ( event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, message ) VALUES ( @event, @computer, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message ) RETURN 0 go execute [sp_MS_marksystemobject] N'[dbo].[sp_ssis_addlogentry]' go execute [sp_MS_marksystemobject] N'[sysssislog]' go
The previous script is the same code that Visual Studio or SSIS itself executes when running a ETL Package the first time.
Use a code similar to this one in VBA:
Include following References: Microsoft ActiveX Data Objects 6.* Library
Be sure to return values as “datetime” instead of “date” or “datetime2” to get recognized as a date value in Excel.
Public Sub GetDwhData()
Dim wksTarget As Worksheet: Set wksTarget = Worksheets(1)
Dim cellAddressStart As String: cellAddressStart = "B2"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrHandling
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=tcp:localhost,1344;Initial Catalog=DWH;Integrated Security=SSPI"
objMyConn.Open
strSQL = "select TOP 1000 * from dbo.Datasource"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL
'Copy Data to Excel...
wksTarget.Range(cellAddressStart).CopyFromRecordset (objMyRecordset)
objMyRecordset.Close
objMyConn.Close
wksTarget.Calculate 'Formeln neu berechnen
ErrHandling:
Application.Calculation = xlCalculationSemiautomatic
Application.ScreenUpdating = True
End Sub
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
Solution:
If your .dtsx XML file (search for LogProv) does not contain the tag “DTS:ConfigString=“, you missed to populate the Connection Configuration DropDown in the Logging-Wizard dialog:
<DTS:LogProviders>
<DTS:LogProvider
DTS:ConfigString="CON_DWH"
DTS:CreationName="Microsoft.LogProviderSQLServer"
DTS:Description="Schreibt Protokolleinträge für Ereignisse in eine SQL Server-Datenbank."
DTS:DTSID="{9FE46402-8C22-49AB-887C-056C89DD00CE}"
DTS:ObjectName="SSIS-Protokollanbieter für SQL Server">
<DTS:ObjectData>
<InnerObject />
</DTS:ObjectData>
</DTS:LogProvider>
</DTS:LogProviders>
Fehler im OLAP-Speichermodul: Ein doppelter Attributschlüssel wurde bei der Verarbeitung gefunden:
Tabelle: TABELLE,
Spalte: SPALTENNAMEINTABELLE,
Wert: 0.
Das Attribut ist ‘ATTRIBUTENAMEINDIMENSION‘.
Mögliche Lösung:
Gehe zu ATTRIBUTENAMEINDIMENSION und entferne die Name-/ValueColumn-Einträge.
Ursache m.M.n: “Bug/Fehlverhalten” in SSAS
Weitere Hilfe, Warnungsfehler in Hierarchien hier:
https://social.msdn.microsoft.com/Forums/sqlserver/de-DE/91e6b21c-e9ad-43ea-8eb8-d6b5f543d27d/ssas-defaulteigenschaften-von-dimensionen-fhren-zu-warnungen
| 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.
You retrieve the Exception:
Microsoft Visual Studio
The operation could not be completed. No such interface supported.
Go to Visual Studio Menu TOOLS | Options…
Under Project and Solutions | General check the Option to Save a Project during creation which is by default unchecked:
This workaround is only a solution to new projects. For your current project you may go to the temporary folder (Right Click on your Project and Open Path in Explorer) and copy the contents of your project to another place.
Surrogate Keys or IDs uniquely identify a record in a data set (table). Typically they (should) consist of only one column. Best example: ID column that is automatically incremented.
A Surrogate Key is also known as Meaningless Key.
Surrogate Keys should be used for internal purposes inside a relational database. They should be hidden to end users. To display unique identifiers to end users you may want to introduce an Alternate Key.
Surrogate Keys are “artificial” or a “substitute“. Some folks say a Surrogate Key is always a substitute for something and if there is nothing to substitue they cannot use the term Surrogate Key. I encourage you to be as precise as necessary.
Alternate Keys usually are a readable identifier for humans. You may want to include Alternate Keys if you need to display readable identifiers for a record to end users. It also may contain certain “meaning” in its value. This might be the case if it is derived from multiple columns of a record.
Alternate Keys may be artificial but are not limited to this. For example: A book record may contain an internal ID. It may also carry a ISBN number that may uniquely identify this book. Another example could be a product that has a Product No used in publications and order systems. This column may also be used as an Alternate Key column.
Natural Keys are columns that out-of-nature create a unique identifier for a record. They contain readable text. Natural Keys may be created across multiple columns. Examples for a natural key are an ISBN number, a social insurance number or the tuple {FirstName, LastName, Address, BirthDate}.
Natural Keys are fine and you may want to use them as a basis for your Alternate Keys for display purposes.
But !NEVER! use Natural Keys as the Primary Key column to create relations between tables!! Many years ago I worked in a fashion company in which they used readable product numbers (consisting of a category and other stuff) as Primary Keys and after 25 years of business the numbers went out. Nice work followed :-).
An ID or Identifier column is usually a non-readable internal column used to uniquely identify a record and to create relations between tables.
I recommend naming an Identifier column with only the two letters ID. Following the DRY principle you should not repeat the table name in all columns. Sticking to this principle will also make your queries easier to read. The ID column will always be addressed by a prefix table like myTableName.ID.
A Foreign Key column is always named following the schema: Table name + ID.
If your Product table has its own ID column and a ProductCategoryID column then it is easy to understand what might be the PK (*) and what is a FK to another table.
Imagine you have a large table with 20 Foreign Keys to other tables and its own ID column. It’s easy using Intellisense to distinguish the ID column from all the other Foreign Keys without having to find out the name of the column that is the ID column of that table (as this must not be the Primary Key and therefore marked with a yellow key symbol).
I encourage you to use the term ID in relational databases and omitting the term Key from being used in an OLTP system.
You may use the term Key as a substitue for the term ID in Data Warehouse scenarios. But please decide on which term you exclusively want to use.
Don’t start mixing Key with ID columns in the same database!
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:
