Redgate – SSAS Compare

 

Background

Needing to compare Sql Server Analysis Server ( SSAS ) database across environments, goggled for tools that will facilitate my needs.

 

Redgate – SSAS Compare

SSAS Compare from Redgate is the tool that Goggle referenced the most.

 

Download

Downloaded the tool from here ( https://www.red-gate.com/products/experimental-tools/ssas-compare/index )

 

Install

Installation is straight forward…

 

Screen Shots

 

Selecting the tools you want to install

selectTheToolsToInstall_20171295_0419AM

 

End User Licensing Agreement

licenseAgreement_20171295_0420AM

 

Customizing the installation folder

customizingTheInstallationFolder_20171295_0421AM

 

Installing Tools

installing_20171295_0422AM

 

Installation Summary

installationSummary_20171295_0423AM

 

 

Usage

 

Options

The tool offers two pathways towards comparing Sql Server Analysis Server ( SSAS ) databases.

Our options are :-

  1. Directly comparing live/active databases
  2. Comparing scripted database file

 

Comparing Live/active databases

Image

New Comparison

newComparison_20171205_0435AM [brushedup]

 

 

Results
Results – Cubes

comparison_Cubes_20171205_0639AM

Results – Data Sources

comparison_DataSource_20171205_0634AM

Results – Data Source Views

comparison_DataSourceViews_20171205_0643AM

Results – Dimension

comparison_Dimensions_20171205_0631AM [brushedup]

 

Results – Roles

comparison_Roles_20171205_0645AM

 

 

Summary

Redgate’s SSAS Compare is a good, small front print, easily accessible tool.

SQL Server – Analysis Services – Error – “A connection could not be made to the data source”

Errors

Message
Process ALL Dimensions:Error: The following system error occurred: The user name or password is incorrect.
Process ALL Dimensions:Error: Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘HRDB’, Name of ‘HRDB’.

TroubleShooting

SQL Server Profiler

Checked via SQL Server Profiler, but nothing came up.

 

Integration Services Catalog

The errors tabulated above came up.

Images

Object Explorer – Integration Services Catalogs

Reports – Standard Reports – All Executions

 

SQL Server Analysis Services

Data Source Properties

Diagnosis

  1. For Data Source Properties, our “Security Settings – Impersonation Information” is set to “Impersonation Account“.
  2. The setting means a specific account is designated
  3. We reviewed the account and changed it from the cube’s developer to an Active Directory Service Account

Images

Original
Data Source Properties

Data Source Properties – Impersonation Information

 

Revised
Data Source Properties

Data Source Properties – Impersonation Information

 

SQL Server – Analysis Services – Cannot Connect ( Microsoft.AnalysisServices.AdomdClient )

Background

Users are having problems connecting to the Analysis Services Server.

 

Error Message

Image

Text


TITLE: Connect to Server
------------------------------

Cannot connect to ....sql01.

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, 
or established connection failed because connected host has failed to respond z.y.76.188:2383 (System)


Text


===================================

Cannot connect to d-isql01.

===================================

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

------------------------------
Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond xx.xx.xx.xx:2383 (System)

------------------------------
Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)


TroubleShooting

Server

Resource Monitor

If the server has Microsoft’s Resource Monitor built-in let us use to it to review Network Ports.

Image

Explanation

  1. Listening Ports
    • Image :-
      • msmdsrv.exe
    • Address :-
      • IPV4 unspecified
      • IPV6 unspecified
    • Port :-
      • 2383
    • Protocol
      • TCP
    • Firewall Status
      • Allowed, not restricted

 

Client

netstat

Script


netstat -an | find "SYN_SENT"

Sample Output

Explanation

  1. Request was sent to port 2383
  2. Status is SYN_SENT
    • SYN_SENT means waiting on a reply

Remediation

Open up TCP Port 2383.

 

Review

Once the Network Port, 2383, is opened up retry access with SSMS.

 

Network Flow

Also, it makes sense to review the Network Traffic to get a full list of ports to be opened.

WireShark

Output

 

Explanation

Seems Analysis Services exclusively relies on port 2383.

Analysis Services – Error – OLE DB Error : OLE DB or ODBC Error : Login failed for user

 

Background

Trying to build an Analysis Services’s Cube, but getting the error pasted below:

 

Error Message

Image

OLEDBOrODBCErrorFailed-20170309-1256PM [BrushedUp]

 

Textual

 

OLE DB error : OLE DB or ODBC Error : Login failed for user

A connection could not be made to the data source with the DataSourceID of ‘LocalHost’, Name of ‘LocalHost’

 

TroubleShooting

 

Analysis Server

Database

Data Source Properties

Image

DataSourceProperties-BIS-20170309-0124PM (Brushed-up)

 

Explanation
  1. Connections
    • Driver :- SQL Server Native Client
    • Data Source :- .
      • . means localhost
    • Authentication
      • Integrated Security=SSPI
    • Database
      • Initial Catalog
        • BIS
  2. Maximum Number of Connections is set to 10
  3. Isolation Level is Read Committed
  4. Impersonation is set to ImpersonateServiceAccount

SQL Server Error Log

Review SQL Server Error Log File

We have two sets of error.

The first set was before we granted SQL Server Instance privilege.

And, the next was after we granted SQL Server Instance privilege, but had yet to grant targeted database privileges.

Image

Could not find a login matching the name provided / Error 18456

 errorLog_20170308_0633PM_to_0635PM (BrushedUp)

 

Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason failed to open the explicit specified database / Error 18456 – Severity 14: State 38

errorLog_20170309_0118PM_to_0125PM (Brushed up)

Tabulate

 

Error Category Section  Message
 Login Not Granted SQL Login
Login Login failed for user ‘LAB\DBSQL$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Login  Error: 18456, Severity: 14, State: 5.
Login Not granted DB Privilege
 Login Login failed for user ‘NT Service\MSSQLServerOLAPService’. Reason: Failed to open the explicitly specified database ‘BIS’. [CLIENT: <local machine>]
 Login  Error: 18456, Severity: 14, State: 38.

 

 

Remediation

Grant Access to SQL Instance

Grant SQL Instance, Database Connection privileges to account that Analysis Service is running under.

Login

Local Machine

 
create login [NT Service\MSSQLServerOLAPService] from windows;
 

Grant Access to Individual Database

Local Machine

 
use [--database--]
go

create user [NT Service\MSSQLServerOLAPService] from login [NT Service\MSSQLServerOLAPService];
go
 

Grant Object Permission

Local Machine

 
use [--database--]
go

exec sp_addrolemember 'db_datareader',  [NT Service\MSSQLServerOLAPService];

SQL Server – Analysis Services – Granting Server level permissions

Background

Granting permissions to the SQL Server Engine is common practice.

But, granting permissions to downlevel services such as Integration, Reporting, and Analysis is not something the DBA does as often.

 

Analysis Service

In this post we will discuss how to use SQL Server Management Studio to do so.

SQL Server Management Studio ( SSMS )

Here are the steps:

  1. Launch SQL Server Management Studio ( SSMS )
  2. The Connect to Server dialog appears
    • In the Server Type :- Analysis Services
    • Server Name :- Enter Server name
  3. Access the Server’s property
    • Select the Server
    • Right click on your server
    • From the drop-down menu, select Properties
  4. In the “Analysis Server Properties” window
    • Select the Security Tab
    • Review the list of System Administrators
    • Click the Add button to add new Sys Admins

 

Connect To Server

connecttoserver-brushed-up

Server’s Property

accessserverproperties-brushedup

 

Analysis Server Properties – Security

analysisserverproperties-security-20170109-1053am

 

References

  1. Developer Network
    • Features and Tasks > Multidimensional Models > Roles and Permissions
      • Grant cube or model permissions (Analysis Services)
        Link

 

SQL Server – Analysis Services – MDX – Member References

Background

Reading through Sample MDX Statements and confused about the role of the ampersand (&).

And, so googled for help, but nothing quite came up.  And, so ventured down the more steady path of reading through Books.

 

Introduction

Members can be referenced via a variety of ways.  The basic pathway been through the Dimension name, its Hierarchy, and the attribute value.

In the case of the attribute value one can use the name value or the key value.

Keep in mind that each attribute has a couple of properties that are important.

Attribute Properties

The Attribute property definition affects how an Attribute is displayed and referenceable.

Column Definition Sample Data
Type User Defined Data type Calendar – Year
Calendar – Quarter
Calendar – Month
Calendar – Week
Calendar – Day
KeyColumns References the combination columns that make the Column values unique Year, Month
NameColumn Descriptive Name that will show up when an attribute such as Time.Month_Name is chosen then the contents of the Time.Month_Name such as January, March will show up
ValueColumn Underlying Value when an attribute such as Time.Month is chosen then the contents of the Time.Month such as 01, 03 will show up

 

 

 

Attribute Definition Values

Here is a quick snapshot of how our Attribute Definitions are specified:

Column Type NameColumn ValueColumn
Date Day Time.Date_Name (WChar) Time.PK_Date (Date)
DateAsYYYYMMDD Day Time.DateAsYYYYMMDD
(WChar)
Date_Name
Day_Of_Month DayOfMonth Time.Day_Of_Month_Name (WChar) Time.Day_Of_Month (Integer)
Day_Of_Year DayOfYear Time.Day_Of_Year_Name (WChar) Time.Day_Of_Year (WChar)
 Month Months Time.Month_Name (WChar) Time.Month (WChar)
 Year Years Time.Year_Name (WChar) Time.Year_Name (WChar)

 

Attribute Hierarchies

The Hierarchies defined on a Dimension also plays a role in how Dimension Attributes are “spelled” out.

AttributeHierarchies

 

Data

Data Contents

Here is screenshot that shows the data generated by the Dimension Wizard when we chose to use it to create a Time Dimension.

 

DataContents

 

 

 

Queries

Here are some sample code – Transact SQL and MDX Statements that shows subtle differences between Name and Key value references.

Queries – Get data for March – Third Month of the Year

Transact SQL


select
	  tblTime.[Month_Of_Year]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Month_Of_Year] = 3
group by
	  tblTime.[Month_Of_Year]


 

MDX – Using Name Value


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].[Month 3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].&amp;[3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

Queries – Get data for 2014

Transact SQL

select
		  tblTime.[Year]
		, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
		inner join [dbo].[SalesOrderHeader] tblSales
			on tblTime.DateAsYYYYMMDD 
                             = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Year] = '2014-01-01 00:00:00.000'
group by
	  tblTime.[Year]

 

MDX – Using Name Value


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	       (
		  [Time].[Year].[Calendar 2014]
	      )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Year].&amp;[2014-01-01T00:00:00] 
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

Queries – Get data for March 27th

Get data for March 27th (Dec 86) and Dec 25th (Day of Year = 359)

Transact SQL

select
  	  datepart(dy, tblTime.[PK_Date]) as [dayofYear]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where (
	     (tblTime.[Day_Of_Year] = 86) -- March 31st 
	  or (tblTime.[Day_Of_Year] = 359) --- December 25th
      )
group by
	  datepart(dy, tblTime.[PK_Date])

 

MDX – Using Name Value

select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (

		{
			  ([Time].[Day Of Year].[Day 95])
		 	, ([Time].[Day Of Year].[Day 359])
		}

	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		{
			  ([Time].[Day Of Year].&amp;[95])
		 	, ([Time].[Day Of Year].&amp;[359])
		}
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

Listening To

D’banj – Bother You
(Footage taken from the feature film “Half of a Yellow Sun” – Directed by Biyi Bandele)
Written by Oyebanjo Daniel Oladapo and Lionel Richie