SSAS basics – building my first cube

Task: to give a basic look at the building of my first data cube by means of SSDT by Microsoft Visual Studio

To build a data cube we will need to follow these few steps:

1. Creating a data source
To connect to this data source the service account is being used. Using the service account (the account that runs the SQL Server Analysis Server service) is common even in production. Make sure that the service account has privileges to read this data source.

Figure 1: Creating a data source – Configuration

2. Creating a data source view
Select FactInternetSales and click on the Add Related Tables button.

Figure 2a: Creating a data source view – Configuration

Figure 2b: Creating a data source view – Diagram of facts and dimensions

3. Building my first cube
Select the data source from the first step, then click on the Suggest button and at the end select measures and dimensions.

Figure 3a: Building my first cube

Figure 3b: Tweaking dimensions for user-friendly appearance

Any warnings appear after tweaking. We will ignore warnings for this basic example.

4. Deploying and querying the cube
At the last step, click on the Deploy Solution item on the Build menu and publish the cube.

Figure 4: Querying the cube

Double click on the cube and navigate to the Browser tab. For example, drag and drop measures such as ExtendedAmount and dimensions and hierarchies such as Fiscal Quarter – Month hierarchy under the Due Date dimension into the query pane. We will retrieve requested data.

Source code: SSAS Package (SQL Server 2008 R2 and BIDS; Adventure Works DW2008R2)
Note: do not forget to customize connection strings etc

Additional references:

• SQLAuthority News – Download Whitepaper – Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
  by Pinal Dave
• SQL SERVER – 2005 – Analysis Services Query Performance Top 10 Best Practices
  by Pinal Dave
• SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
  by Pinal Dave
• SQL SERVER – Guest Post – Architecting Data Warehouse – Niraj Bhatt
  by Niraj Bhatt
• SQL SERVER – Languages for BI – MDX, DMX, XMLA
  by Pinal Dave
• SQL SERVER – SSAS – Multidimensional Space Terms and Explanation
  by Pinal Dave

Advertisement

SSIS basics – import CSV to a SQL Server table

Task: to give a basic look at the import of a CSV file to a SQL Server table by means of SSDT by Microsoft Visual Studio

Sales per region,Africa,Asia,Europe
January,34,67,56
February,36,87,78
March,31,56,88
April,29,67,92
May,54,71,68
June,68,71,54
July,54,71,68
August,68,71,54
September,92,67,29
October,88,56,31
November,78,87,36
December,56,67,34

Source code 1: CSV data

USE [Test]
GO

CREATE TABLE [dbo].[SSIS_CSV_Import]
(
    [Month]      [varchar](9)    NOT NULL,
    [SaleAfrica] [numeric](2, 0) NOT NULL,
    [SaleAsia]   [numeric](2, 0) NOT NULL,
    [SaleEurope] [numeric](2, 0) NOT NULL
)
GO

Source code 2: Table structure

Figure 1: Flat File Connection Manager Editor – General

Figure 2: Flat File Connection Manager Editor – Columns

Figure 3: Flat File Connection Manager Editor – Advanced

Figure 4: SQL Destination Editor – Connection Manager

Figure 5: SQL Destination Editor – Mappings

Figure 6: SSIS Package - Data Flow

SELECT [Month], [SaleAfrica], [SaleAsia], [SaleEurope]
FROM   [dbo].[SSIS_CSV_Import]
GO
/* Result:
   Month       | Africa | Asia | Europe
   January       34       67	 56
   February      36       87     78
   March         31       56     88
   April         29       67     92
   May	         54       71     68
   June	         68       71     54
   July	         54       71     68
   August        68       71     54
   September     92       67     29
   October       88       56     31
   November      78       87     36
   December      56       67     34
*/

Source code 3: SQL query for result verification

Additional references:

• Comma-separated values
  by Wikipedia
• SQL SERVER – Import CSV into Database – Transferring File Content into a Database Table using CSVexpress
  by Pinal Dave
• SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
  by Pinal Dave
• SQL SERVER – Powershell – Importing CSV File Into Database – Video
  by Laerte Junior
• SQL SERVER – Running SSIS Package in Scheduled Job
  by Pinal Dave

Parallel query processing in SQL Server

Task: to give a basic look at the subsystem for parallel query processing in SQL Server

/* Testing laptop: ProBook 6540b, Intel Core i5 */

USE [AdventureWorks2012]
GO

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   4                   4	           1
*/

Source code 1: System configuration

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value, i.e. 5 seconds
   max degree of parallelism        0 => default value, i.e. parallelism
                                         with an automatic count of
                                         processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO

Source code 2: Parallel query processing providing that the cost threshold parameter equals to 5

Figure 1: Execution plan for parallel query processing regarding the cost threshold parameter

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 10
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   10 => custom value i.e., 10 seconds
   max degree of parallelism        0  => default value i.e., parallelism
                                          with an automatic count of
                                          processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO


EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 5
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value i.e., 5 seconds
   max degree of parallelism        0 => default value i.e., parallelism
                                         with an automatic count of
                                         processors
*/

Source code 3: Serial query processing providing that the cost threshold parameter equals to 10

Figure 2: Execution plan for serial query processing regarding the cost threshold parameter

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]
OPTION   (MAXDOP 1) -- Note: query hint for one processor
GO

Source code 4: Execution plan – costs comparison for parallel and serial with OPTION (MAXDOP 1) query processing

Figure 3: Execution plan - cost 40% for parallel vs. 60% for serial with OPTION (MAXDOP 1) query processing

Figure 4: Setting of physical and logical CPU count simulation

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   16                  4	           4
*/

Source code 5: Simulating 4 physical and 16 logical CPU count

Note: Express Edition of SQL Server does not support simulating physical CPU count

General recommendations:

• OLTP: on Pure OLTP system, where the transactions are smaller and queries are not long but very quick usually, set the “Maximum Degree of Parallelism” to 1 (one).
• Data-warehousing / Reporting server: as queries will be running for long time, it is advised to set the “Maximum Degree of Parallelism” to 0 (zero).
• Mixed System (OLTP & OLAP): the right balance has to be found. To set the “Maximum Degree of Parallelism” to 2, which means the query still uses parallelism but only on 2 CPUs. However, to keep the “Cost Threshold for Parallelism” very high.

Additional references:

• Execute T-SQL in Parallel
  by Kevin Kline
• Execute T-SQL Scripts in Parallel
  by James Ma
• Flash Tip: Simulating Parallel Plans
  by Microsoft TechNet
• How SQL Server 2005 Express Edition determines the CPU count and uses the CPUs during processing
  by Microsoft Support
• Parallel Data Warehouse
  by Microsoft SQL Server
• Parallel execution in SSIS
  by James Serra
• Parallel Query Processing
  by Microsoft MSDN
• SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
  by Pinal Dave
• SQL SERVER – Partition Parallelism Support in expressor 3.6
  by Pinal Dave
• SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video
  by Pinal Dave
• SQL Server 2012’s Information on Parallel Thread Usage
  by Joe Sack
• SQLAuthority News – Download Whitepaper – Understanding and Controlling Parallel Query Processing in SQL Server
  by Pinal Dave