SQL Business Intelligence

Terms: Data Warehouse and SSAS

Karan Gulati (SSAS Maestro) — Mon, 29 Apr 2013 13:08:09 GMT

Data warehouse and ssas terms from Karan Gulati

DAX: Using filter and summarize in same Query

Karan Gulati (SSAS Maestro) — Mon, 29 Apr 2013 12:58:31 GMT

In this SQL Query we are grouping Sales by Year and Color plus adding a filter of 5000

select CalendarYear,color,sum(SalesAmount) from [DimProduct] join FactInternetSales
on DimProduct.[ProductKey]=FactInternetSales.[ProductKey]
join DimDate
on dimdate.datekey=FactInternetSales.OrderDatekey
Group by CalendarYear,color
Having sum(salesamount)>5000
order by CalendarYear,color

Equivalent Dax of achieving same is:

evaluate( filter (summarize ('Internet Sales', 'Date'[Calendar Year],
'Product'[Color] ,"Sales Amount",sum('Internet Sales'[Sales Amount]))
,Calculate(sum('Internet Sales'[Sales Amount]))>5000))
order by 'Date'[Calendar Year],
'Product'[Color]

PowerShell:How to List Database Roles and their Members

Karan Gulati (SSAS Maestro) — Mon, 29 Apr 2013 12:56:51 GMT

   1:  [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

   2:  $ServerName = ".\sql2008r2"

   3:  $DB = "Adventure Works DW 2008"

   4:  $Server = New-Object Microsoft.AnalysisServices.Server

   5:  $Server.Connect($ServerName)

   6:  $SSASDatabase = $Server.Databases.Item($DB)

   7:  $SSASDatabase.Roles | Select Name, Members

HDInsight on Windows: Building PowerPivot Report from Hive in a Few Easy Steps (PDF)

Karan Gulati (SSAS Maestro) — Mon, 29 Apr 2013 12:52:11 GMT

Download Step by Step PDF from Slide Share

HDInsight on Windows: Building PowerPivot Report from Hive in a Few Easy Steps from Karan Gulati

SSAS Crashing Intermittently: Caused by Monitoring / AV Scans

Karan Gulati (SSAS Maestro) — Wed, 10 Apr 2013 10:17:45 GMT

Problem Description:

Analysis Services is crashing intermittently and also producing min-dumps.

Assessment:

This could be one of the issues. For getting full analysis of Mini-Dump you can involve Microsoft Customer Support Services and ask them to analyze dumps.

In this case we found:

The issue is SMS client (CcmExec) does software inventory periodically, and it scans the data folder of Analysis Services. When a job needs to commit, it has to delete older version of data files. At the moment, the SMS client has a file handle on some of the database files, causing SSAS unable to delete the older version of the database, so commit fails and crashes SSAS

As you can see in this Process Monitor – CcmExec is browsing through SQL Folders:

Resolution

We have resolved issue by making an exception in SMS client not to browse / scan SSAS Folders
- Data
- Temp
- Config
- Log

Recommendations

Exclude Analysis Services folders from Virus Scans, File Monitoring Tools, Systems Management Server Client – CcmExec.exe or any other 3p File Monitoring or File Backup Tool.

For SQL engine follow recommendations are given in this link - http://support.microsoft.com/kb/309422

Hardware Sizing a Tabular Solution (SQL Server Analysis Services)

Karan Gulati (SSAS Maestro) — Fri, 11 Jan 2013 08:28:04 GMT

Applies to: SQL Server 2012 Analysis Services, Tabular Solutions

Summary: Provides guidance for estimating the hardware requirements needed to support processing and query workloads for an Analysis Services tabular solution.

Download from here (Hardware Sizing a Tabular Solution (SQL Server Analysis Services) Word document.

SSAS Synchronization: Across different versions

Karan Gulati (SSAS Maestro) — Wed, 24 Oct 2012 12:01:43 GMT

Problem Statement: Synchronization between different builds or versions of Analysis Services.

Solution: The Synchronize Database Wizard makes two Microsoft SQL Server Analysis Services databases equivalent by copying the data and metadata from a source server to a destination server. This read more

SSAS encryption

Karan Gulati (SSAS Maestro) — Tue, 23 Oct 2012 15:27:38 GMT

Description: Customer asking if AS supports TDE like encryption we got in SQL Engine.

Solution:

SSAS doesn’t support any data encryption on disk, in simple words – SSAS doesn’t do any kind of encryption.

What it supports:

HTTPS –

You can use Analysis Services using PUMP and configure pump with HTTPS

Windows File System encryption -

You can use Windows File read more

SSAS Data Collection: Collecting Perfmon for SSAS

Karan Gulati (SSAS Maestro) — Mon, 01 Oct 2012 14:20:01 GMT

Applies to Windows7, Windows 2008/R2 and Vista

SQL Server all builds >= SQL 2005

Generally, for troubleshooting SSAS Issues, we recommend to collect below mentioned Perfmon Counters

- All Counters Related to SSAS (MSAS_ or MSOLP$)

For default instance SSAS Counter Name suffix with MSAS.n and for named instance it start with MSOLAP$Instance Name

- System, Process, Processor, Physical Disk, Logical Disk, Memory , Page File

· Got to Run & type Perfmon

· Under Data Collector Set, right click on User Defined and Click New Data Collector Set

· Create New Data Collect Set and Give name as MSTrace & Select Create Manually, Click Next

· Click on Add required counters (Sample Interval as 10 Seconds)

· Here you can specify the Run as user which has Admin Access over box (in case you have logged with credential which is non admin)

By default Location of Trace file is - %systemdrive%\PerfLogs\Admin\ but you can change it.

Right Click on Newly Created Trace – Mstrace and select Properties – In Properties under directory tab you can specify the location also you can schedule as per your requirement.

Once you are done with that click OK and Run trace as shown below

Reproduce the issue and collect traces, once you are done just stop traces and check for traces in location which you had provided in Properties.

Getting Deadlock Error During Processing

Karan Gulati (SSAS Maestro) — Thu, 27 Sep 2012 12:41:04 GMT

Issue:

Intermittently Getting below mentioned error during processing
OLE DB error: OLE DB or ODBC error. Transaction (Process ID n) was deadlocked on thread| communication buffer resources with another process and has been chosen as the deadlock victim.

Cause:
A deadlock is a circular blocking chain, where two or more spids are each blocked by the other so that no one can proceed.

You don’t have control over the Select Statement generated by SSAS Processing job (If you are not using Named Query in DSV), in that case you need take care of things from SQL Engine Perspective.

Here are few recommendations, which can reduce deadlock situation: