특정 차원 멤버로 필터링된 측정값 생성

CREATE MEMBER CURRENTCUBE.[Measures].[남성고객수]

AS SUM({ [성별].[성별].&[M]}, [Measures].[고객고유카운트]);


CREATE MEMBER CURRENTCUBE.[Measures].[여성고객수]

AS SUM({ [성별].[성별].&[F]}, [Measures].[고객고유카운트]);

Analysis Services Linked Server error (The peer prematurely closed the connection)

분석서버 연결된 서버 생성 후 에러 메세지

연결된 서버 ""의 OLE DB 공급자 "MSOLAP" 이(가) 메시지 "피어에서 연결을 중간에 닫았습니다" 을(를) 반환했습니다. (Microsoft SQL Server, Error: 7303)

메세지 창

영문메세지

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Connectionlnfo)

Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server " ",

OLE DB provider “MSOLAP” For linked server “ ” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “ ’ returned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)

 EXEC MASTER.DBO.SP_ADDLINKEDSERVER
  @server = N'LinkedServerName',
  @srvproduct=N'MSOLAP',
  @provider=N'MSOLAP',
  @datasrc=N'domainName',
  @catalog=N'catalogName'
EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN
  @rmtsrvname=N'LinkedServerName',
  @useself=N'False',
  @locallogin=NULL,
  @rmtuser=N'domainName\administrator',
  @rmtpassword='password'  

대용량 데이터베이스에서 효과적인 과거 데이터 삭제

8. 과거데이터 효과적으로 삭제하기

# DELETE FROM ~ WHERE ~ 와 같은 방법으로 데이터를 삭제하지 않는다.
(LOCK, LOGGING에대한 문제가 발생하며 위와 같은 쿼리 실행중 취소를 하면 롤백 또한 길어진다.)

# 일반적으로 인덱스가 잡히지 않은 테이블에 적재 하는 것이 빠르다.
 (인덱스 없는 테이블에 적재 > 인덱스 생성 > 테이블 대체(테이블명을 수정하여 원래의 테이블로)

# 데이터를 삭제하는 방법으로 루프문 내에 delete top (1000) 과 같은 쿼리를 반복 수행하는 방법도 있다.

# 삭제여부와 같은 컬럼을 두어 삭제대상 데이터에 삭제여부 컬럼을 업데이트 한 후
빠른처리를 하지 않아도 될 시간에 해당 데이터를 삭제하는 것도 하나의 방법이다.
(ex>새벽에 일배치가 완료된 후 여유가 되는시간에 삭제)

참조 >>  Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

SSAS Clear Cache

XMLA command for clearing the Analysis Services cache
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW</DatabaseID>
  </Object>
</ClearCache>

Dimension default_member

ALTER CUBE CurrentCube
    UPDATE DIMENSION [dim_type].[type]
    , DEFAULT_MEMBER = [dim_type].[type].&[1];

Percentage over all dimension and axis

계산된 측정값 (모든 차원에 대한 비율)

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sells %]
AS iif(
([Measures].[Sells],
Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent)=0,
null,
[Measures].[Sells]/
([Measures].[Sells],Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent) ),
FORMAT_STRING = "Percent",
VISIBLE = 1 ;

메타데이터 관리자 오류 / Errors in the metadata manager.

>> 에러메세지 

메타데이터 관리자에서 오류가 발생했습니다. 트랜잭션의 작업으로 인해 ID가 ‘큐브ID’이고 이름이 ‘큐브명’인 CUBE이(가) 무효화되었습니다.

Errors in the metadata manager. The cube with the ID of ‘CUBE_ID', Name of 'CUBE_NAME' was invalidated by operations in the transaction. 

이미 Processing이 완료된 차원의 특성관계(Attribute Relationships)를 수정하고 저장, 또는 처리시 위와같은 에러메세지를 띄우고 저장 및 처리를 하지 못한다.

이러한 경우 해당 차원파일을 삭제(또는 이름변경) 후 다시 저장하면 된다.

C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\DBName.0.db\

위의 위치에서 차원명과 동일한 xml 파일 , 폴더를 삭제또는 이름변경하고 차원을 저장 & 처리한다.

(보통 xml파일은  차원명.0.dim.xml  폴더명은 차원명.0.dim 와같은 형태임)

Development Edition에서 되었으나, Server Standard Edition에서는 가능하지 않았다.

0으로 나누기 오류로 인한 -1.#INF 값 표시하지 않기.

0으로 나누기 오류로 인해 그리드에 -1.#INF로 표시되는 데이터는

다음과 같이 처리한다.

EX) 매출수량에 0이 포함된 경우가 있을 경우.

IIF([Measures].[매출수량] = 0, Null,  [Measures].[매출금액]/[Measures].[매출수량])

The size specified for a binding was too small

Error : 

Warning 1 Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated.

해결 : 차원테이블과 팩트테이블간의 Key Column간의 데이터타입이 일치하도록 조정.

간단한 전체대비 비율구하기 계산된 측정값

CREATE MEMBER CURRENTCUBE.[Measures].[Calculated Member]
 AS Case
When IsEmpty
     (
       [Measures].[매출액]
     )
Then Null

Else ( [상품].[상품계층].CurrentMember,
       [Measures].[매출액]  )
     /
     ( Root ( [상품] ),  [Measures].[매출액]    )
End,
VISIBLE = 1  ;

운영서버의 ASDB관리

큐브처리중에도 처리완료 이전 시점까지의 분석디비에 접근이 가능하도록
두개의 디비로 운영한다.

ASDB1 >> 큐브처리용
ASDB2 >> 서비스용

배치작업에서는 ASDB1을 처리하고 처리가 완료되면 ASDB1을 백업하고
백업한 ASDB1파일을 ASDB2로 복원하는 JOB을 생성하여 배치수행을 한다.
복원중에 ASDB에 접근할 수 없긴하지만 큐브처리시간동안 접근할 수 없는 시간보다는 짧다.

큐브처리시 오류구성

BIDS에서 큐브처리 시 오류구성이 저장되지 않아 매번 오류구성에서 오류무시 옵션을 체크하고 큐브처리를 했었는데 SSMS상에서 오류구성옵션을 바꾸면 해당 설정대로 다음번에도 처리가 가능하다.

큐브상의 Rowcount가 실제테이블과 일치하지 않을 때

큐브 처리 후 팩트 테이블의 실제 건 수 만큼의 데이터를 보여주지 않을 때

체크해봐야 할 사항.

1. 파티션에서 필터링을 하는지 체크

  쿼리로 처리를 하는지. 테이블로 처리를 하는지. 

2 . 큐브처리 시 캐쉬를 삭제한 후 다시 처리를 해 본다. 

 아래와 같은 쿼리로 캐쉬 삭제가 가능

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>ASDB_NAME</DatabaseID>

  </Object>

</ClearCache>

3. 차원 생성시 기본적으로 UNKNOWN MEMBER를 사용하도록 설정이 되지만

간혹 설정이 되지 않고 생성되는 경우가 있다. 

UNKNOWN MEMBER의 사용이 VISIBLE 이 보통이나. NONE으로 바껴 있다면

차원키 에러가 났을 경우 해당 데이터를 제외하고 보여주기 때문에 실제 데이터보다 적은 데이터가 보여질 수 있다.

40여개의 차원중 두 개가 NONE으로 설정되어 있었다.  

(이 경우때문에 며칠을 헤매었다. SSAS의 버그인가 싶다.)

 이렇게 바꿔 줌

큐브처리 옵션

처리 옵션

다음 표에서는 Analysis Services에서 사용 가능한 처리 방법을 설명하고 각 방법이 지원하는 개체를 식별합니다.

기본값 처리

개체의 처리 상태를 검색하고 필요한 처리를 수행하여 처리되지 않거나 부분적으로 처리된 개체를 완전히 처리된 상태로 전달합니다. 이 처리 옵션은 큐브, 데이터베이스, 차원, 측정값 그룹, 마이닝 모델, 마이닝 구조 및 파티션에 대해 지원됩니다.

전체 처리

Analysis Services 개체와 이 개체에 포함된 모든 개체를 처리합니다. 이미 처리된 개체에 대해 전체 처리를 실행하면 Analysis Services에서는 개체의 모든 데이터를 삭제한 다음 개체를 처리합니다. 이 처리 유형은 특성 계층이 추가, 삭제 또는 이름이 바뀐 경우와 같이 개체 구조가 변경된 경우에 필요합니다. 이 처리 옵션은 큐브, 데이터베이스, 차원, 측정값 그룹, 마이닝 모델, 마이닝 구조 및 파티션에 대해 지원됩니다.

증분 처리

사용 가능한 팩트 데이터를 새로 추가하고 관련 파티션만 처리합니다. 이 처리 옵션은 측정값 그룹 및 파티션에 대해 지원됩니다.

업데이트 처리

데이터 다시 읽기 및 차원 특성 업데이트를 강제로 수행합니다. 관련 파티션의 융통성 있는 집계와 인덱스가 삭제됩니다. 예를 들어 이 처리 옵션은 차원에 새 멤버를 추가하고 전체 데이터 다시 읽기를 강제로 수행하여 개체 특성을 업데이트할 수 있습니다. 이 처리 옵션은 차원에 대해 지원됩니다.

인덱스 처리

처리된 모든 파티션에 대한 인덱스 및 집계를 만들거나 다시 빌드합니다. 이 옵션은 처리되지 않은 개체에 대해 오류를 발생시킵니다. 이 처리 옵션은 큐브, 차원, 측정값 그룹 및 파티션에 대해 지원됩니다.

데이터 처리

집계나 인덱스를 작성하지 않고 데이터만 처리합니다. 파티션에 데이터가 있는 경우 이 데이터를 삭제한 후 해당 파티션에 원본 데이터를 다시 채웁니다. 이 처리 옵션은 차원, 큐브,측정값 그룹 및 파티션에 대해 지원됩니다.

처리 안 함

지정한 개체 및 모든 하위 요소 개체의 데이터를 삭제합니다. 삭제한 데이터는 다시 로드되지 않습니다. 이 처리 옵션은 큐브, 데이터베이스, 차원, 측정값 그룹, 마이닝 모델, 마이닝 구조 및 파티션에 대해 지원됩니다.

구조 처리

큐브가 처리되지 않은 경우 Analysis Services에서는 필요에 따라 큐브의 모든 차원을 처리한 다음 큐브 정의만 만듭니다. 마이닝 구조에 이 옵션을 적용하면 원본 데이터로 마이닝 구조를 채웁니다. 이 옵션이 전체 처리 옵션과 다른 점은 마이닝 모델 자체까지 처리를 반복하지는 않는다는 것입니다. 이 처리 옵션은 큐브 및 마이닝 구조에 대해 지원됩니다.

구조 지우기 처리

마이닝 구조에서 모든 학습 데이터를 제거합니다. 이 처리 옵션은 마이닝 구조에 대해서만 지원됩니다.

스크립트 캐시 처리

이 기능은 다음 버전의 Microsoft SQL Server에서 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 말고, 현재 이 기능을 사용하는 응용 프로그램은 가능한 한 빨리 수정하십시오

Dimension Members Count

차원 멤버의 수

매출 테이블에서 상품개수를 구할 경우 상품코드를 Distinct Count를 사용하여 구할 수도 있지만

상품차원이 있다면 이는 곧 상품차원멤버수와 동일함. 

Calculated Measure로 아래와 같은 계산식을 사용하여 구할 수 있다. 

IIF(Count(CrossJoin({[Measures].[측정값]},[차원].[계층].Members), ExcludeEmpty) =0

,null

,Count(CrossJoin({[Measures].[측정값]},[차원].[계층].Members), ExcludeEmpty)-1 )

큐브처리 중 쿼리 제한 시간 에러 (Query timeout expired HY008)

증상)

큐브처리 중 

'OLE DB error: OLE DB or ODBC error: Query timeout expired HY008'

라는 메세지를 띄우며 큐브처리 중 에러가 나는 경우가 있다.

큐브처리시 해당 파티션의 크기가 큰 경우 기본설정(60분)시간 내에

파티션 처리가 완료되지 않아 쿼리제한시간 만료 에러를 띄운다.

해결)

1. 파티션을 좀더 작은 단위로 나눈다.

2. 쿼리제한시간을 늘려준다. 

 ( SQL Server Enterprise 버전의 경우 파티션 개수에 제한이 없으나 Standard 버전에서는 3개의 파티션까지 생성이 가능하므로 3개의 파티션으로도 해결이 되지 않을 경우 사용한다.)

 - SSMS에서 ASDB Property의 ExternalCommandTimeout 옵션을 조정 

   (고급 속성 모두 표시 체크박스에 체크를 해야 옵션이 나타난다.)

BIDS에서 큐브처리를 할 경우엔 해당 프로젝트에서 솔루션 탐색기의 가장 상위의 

DB를 선택 후 도구>옵션에서 제한 시간을 조정한다.

SSAS Time Duration to measure

시간을 측정값으로 사용하기


1. 대상컬럼 변환

시간컬럼을 측정값을 numeric형태로 변환 .

ETL_LOG라는 테이블의 작업시간 컬럼의 데이터타입은 TIME(7).

이것을 초단위로 바꾸고 하루(86,400초)로 나눔.

예)
SELECT

             WTIME                                                                                    AS [작업시간(HH:MM:SS.MS)]

,             (LEFT(CONVERT(VARCHAR,WTIME),2)*3600                    

+           SUBSTRING(CONVERT(VARCHAR,WTIME),4,2)*60

+           SUBSTRING(CONVERT(VARCHAR,WTIME),7,2) ) / 86400.0000          AS [작업시간(초)/1DAY(초)]

FROM ETL_LOG

ORDER BY 1 DESC  

---- Result

2. 계산된 측정값 추가

큐브 생성 시 위에서 추가한 시간측정값컬럼을 측정값에 포함시키고 계산된 측정값 탭에서

스크립트를 추가 (스크립트 뷰모드에서 추가)

---- Script 추가

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].[소요시간]

AS [Measures].[WDURATION],

FORMAT_STRING = IIF(

      [Measures].[WDURATION] < 1

     ,'"0 day"  hh:mm:ss'

     ,'"' + cstr(int([Measures].[WDURATION])) + ' day" hh:mm:ss'),

VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = '로그측정값'  ;

노란색으로 표시된 부분만 변경.

소요시간 > 새로만들 계산된 측정값 명

WDURIATION > 작업시간 측정값 명

로그측정값 > 측정값그룹 명

3. 확인

SSMS 상에서 확인.

---- SSMS에서 확인

Analysis Services Distinct Count Optimization Using Solid State Devices

A Real Practices Case Study at Xbox LIVE
Authors: Joseph Szymanski, Tyson Solberg, Denny Lee
Technical Reviewers: Lindsey Allen, Akshai Mirchandani, Heidi Steen

Executive Summary

To expand on the distinct count optimization techniques provided in the Analysis Services Distinct Count Optimization white paper, this technical note shows how using solid state devices (SSDs) can improve distinct count measures. We recount the experiences of the Microsoft Entertainment and Devices Data Warehousing Team (known for Xbox, Xbox LIVE, XNA, and Zune) in our analysis of applying SSDs to a real-world, distinct count heavy, Microsoft SQL Server Analysis Services customer environment. The key conclusion is that enterprise SSD devices, when combined with a well optimized Analysis Services MOLAP cube, will drastically improve the performance and scalability of the cube when it accesses distinct count measures. It can also improve non-distinct count measures, if the calculations being performed rely heavily on storage-engine calculations.

Purpose

Analysis Services distinct count measures are extremely expensive in all aspects of an Analysis Services solution – the time requirements for processing the data, the long query durations, and large storage space requirements. Often the best approach is to convince the analysts using your cubes to use alternate measures or calculations. However, in many cases the distinct count-based Key Performance Indicators (KPIs) are key components of business analytics systems. In such cases, the focus has to move from "Are you sure you need distinct count?" to "How can we make distinct count queries fast(er)?"
One way to improve the performance of distinct count measures is to change the business problem that they attempt to measure (for example, limiting the time range for distinct counts to specific years or months rather than all years). However, when you have exhausted your ability to reduce the solution’s complexity by simplifying business processes, you can turn to other techniques to improve your solution. This paper endeavors to share several that we came across in real-world enterprise usage scenarios.
As originally described in Analysis Services Distinct Count Optimization, distinct count calculations are Storage-Engine-heavy, resulting in heavy disk I/O utilization (that is, higher latencies and slower throughput). This leads us to the first and foremost among those techniques: the use of solid state devices (SSDs), which have taken the world by storm. Given the high initial cost, relative newness, and lower capacity per device, it is often difficult to find a good fit for them in the enterprise. However, Analysis Services dovetails well with the SSD story because SSDs offer ultra-high read speeds for both random and sequential I/O. Analysis Services queries generally access a large amount of relatively static data and SSDs provide the speed and flexibility required. For example:

• Analysis Services scenarios are well suited for SSDs because most are designed for fast read performance.
• The biggest benefit that SSDs offer over physical disks is that they provide random I/O read speed that is nearly as high as sequential I/O read speed – orders of magnitude faster than spin disks.
• Data stored in MOLAP cubes is an ideal target for SSDs: most SSDs are rather low in capacity, but Analysis Services MOLAP cubes are generally small in size when compared to their data warehouse source. For example, in our sample case we have a 10-terabyte warehouse, which would cost an extraordinary amount of money to move to SSDs, but a 160 GB cube, which would be very easy and inexpensive to move to SSDs.
• While the initial costs of SSDs are higher than those of spin disks, the overall lifetime costs of SSDs are comparable to spin disks because of cooling costs, differences in power consumption, and general maintenance costs associated with your storage (SSDs typically have lower maintenance costs).

Scenario

This paper covers the business scenario in which business users seek to improve the performance of their Analysis Services distinct count operations. As an example, the Microsoft SQLCAT team worked with the people at the Microsoft Entertainment and Devices Data Warehousing who needed to build their critical KPIs based on distinct counts. We decided that a significant research effort to find a way to make their distinct count queries run faster through design methodologies and the use of SSDs was worthwhile.
The engineering team, examining the scenario at hand, endeavored to find out the following:
a) Can distinct counts be made to run faster: a. Given a faster I/O system? b. Given better cube design?
b) Will such improvements be cost effective?
c) Will such improvements improve or hinder scalability, compared with the current design?

Datasets

The database is a real-world production dataset with the following characteristics.

Dataset	Sizes
MOLAP Cube	120 GB
SQL DW	10.0 terabytes

The cube design, for the distinct counts, is relatively simplistic, as noted in Figure 1.

Figure 1: Usage Cube Schema

OLAP Query Characteristics

One of the primary business drivers that needed an answer is: "How many distinct users used the service for <a time period>?" To get this answer, we focused on a single simple MDX statement and sought to make it run as fast as possible from cold cache (that is, we cleared all existing caches to force the data to load from the pertinent device).

SELECT [Measures].[Online Unique Users] ON 0,

[Date].[Date].[Date] ON 1

FROM [Usage]

WHERE

[Date].[Calendar Month].[Calendar Month].&[2009]&[1]

The measure "Online Unique Users" is a distinct count measure that, for the time period of the month selected, scans through 300 million SQL records in the MOLAP cube dataset.

Test Hardware

We utilized a number of servers to perform our query tests.
User Acceptance Testing Server
HP DL580 G5 4-socket, quad core, 2.4 GHz, 64 GB RAM server connected to a high end SAN array; Chart Label: "UAT SAN"

Development Server
Dell R710 2-socket, quad core, 2.93 GHz, 72 GB RAM
Disk Arrays

• 1 Fusion-io ioDrive PCI-E SSD device; Chart Label: "Dev SSD"
• Two Dell MD1000 enclosures (16 x 750 GB 7200RPM drives); Chart Label: "Dev Hard Drives"

SQL Customer Advisory Team Server
Dell R905 4-socket, quad core, AMD server with 4 Fusion-io ioDrive PCI-E SSD device; Chart Label: "SQLCAT SSD"

We’d like to thank Fusion-io, Dell, and Hewlett-Packard for the use of and support with their hardware.

Analysis

As originally described in Analysis Services Distinct Count Optimization, distinct count calculations are Storage-Engine-heavy, resulting in heavy disk I/O utilization. Therefore, the original hypothesis for this case study was modeled after the reasonable argument: "If we are disk I/O bound, and Analysis Services provides a 100 percent random read load, SSDs should drastically improve performance."

Initial Query Performance Comparison Between SSDs and Spin Disks

Let’s start by comparing the query performance between SSDs and spin disks (local hard drives and SAN).

Figure 2: Initial Performance Optimization (Lower Is Better)
As can be seen in Figure 2, the use of Fusion-io SSDs resulted in dramatically faster query performance when compared to local drives or SAN drives. For example, for the seven-month query, SSDs were 5.5 times faster than the SAN hardware.
But as with any successful BI performance improvement, Analysis Services users also asked more resource-intensive questions such as: "For the last year, how many distinct people did <X>?" That is, these questions resulted in distinct count queries that covered a year’s worth of data across a substantially larger dataset. In an attempt to find further performance improvements, the engineering team profiled the cube during query time, in production, and found a very odd thing: Disk usage and CPU usage were nowhere near the limits of what the system was able to handle.

This finding was counterintuitive, because all tests were run on a system that was dedicated to this exercise (that is, the system had no other users or processes running concurrently); it was not a matter of resource contention. We then worked with the SQLCAT team and other experts to find out why Analysis Services was not using more system resources to query the cube, starting with an investigation of how the partitions were defined.

The Move to an Optimally Parallel Cube

As noted in Analysis Services Distinct Count Optimization, partitioning significantly improves distinct count query performance. By creating distinct buckets based on distinct value and time, you can significantly improve distinct count query performance by forcing the Analysis Services Storage Engine to fire off many more threads – one for each partition – and therefore more quickly calculate the distinct value. But if partitions are designed with an uneven number of distinct values (such as in Figure 3), the query may ultimately become single-threaded (even though all four partitions are being queried) because the Analysis Services Storage Engine is waiting for the largest partition (data file with values from 1,500 to 20,000) to complete its calculations. This behavior explained the puzzling results around disk and CPU consumption in the earlier tests.

Figure 3: Unevenly Distributed Partitions
A more evenly distributed set of distinct values within the partitions results in all four threads completing at approximately the same time, resulting in minimal spinning and wait time while the calculations are completed. Clearly, parallel queries across the partitions had to be part of our solution.

Figure 4: Evenly Distributed Partitions
Figure 4 shows the even distribution of data among the partitions, which is a key concept for distinct count query optimization. The Analysis Services Storage Engine will initially query the header file to determine which data partitions to query for the range of distinct count values. This way, the storage engine queries only partitions that have the values required to complete the calculation.
After extensive testing, we rediscovered some important rules and added them to the distinct count partitioning strategy to ensure all distinct count queries are optimally parallel:
The distinct count measure must be directly contained in the query.
If you partition your cube by the hash of a UserID distinct value, it is important that your query perform a distinct count of the hash of the UserID – not the distinct count of the UserID itself. For fast distinct count query performance, it is important for the distinct count value itself to be placed in its own periodic partition (for example, User 1 repeatedly shows up in only month 1 partition 1, User 100 in month 1 partition 2, and so on) and for the values to be non-overlapping (for example, Users 1-99 in month 1 partition 1, Users 100-199 in month 1, partition 2, and so on). The hashing will cause the records in the same range to be distributed across multiple partitions, therefore losing the non-overlapping behavior. Even if the UserID and the hash of the UserID have the same distribution of data, and even if you partition data by the latter, the header files contain only the range of values associated with the hash of the UserID. This ultimately means that the Analysis Services Storage Engine must query all of the partitions to perform the distinct on the UserID. For more information about these concepts, see the white paper Analysis Services Distinct Count Optimization.

The distinct count values need to be continuous.
As implied in Figures 3 and 4, each partition has a continuous range of values so that the partition contains the values from 100 – 20,000 (in this example). Based on the empirical evidence we gathered in our testing for this case, it appears that distinct count query performance improves if the values within the partitions are continuous.
After we followed these two rules, we were easily able to improve query parallelism with very few changes.
More specifically, we analyzed our data size, selected a month as the coarse time grain for the distinct count measure group partitions, and then sub-selected the data, per month, into <n> partitions, where n is the number of physical CPU cores on the OLAP hardware. We made this decision after we identified a number of options, tested them, and found this particular grain to be the best for our set of data. Other than the partition changes, the cube design stayed the same, and we did not alter any aggregations for this cube.  Note, we had followed the established guidelines of the SQLCAT white paper Analysis Services Distinct Count Optimization.
Note: To allow for more repeatable distinct count query comparison, the cube used here contained no aggregations on distinct count measures.
The following lists various combinations of measure group slicer queries and distinct count measures.  

SQL Query WHERE clause	Analysis Services distinct count member	Is the query optimally parallel?  If not, why?
WHERE userid % 16 = 0	userid	NO: Query does not return a continuous dataset.
WHERE CAST(HASHBYTES('SHA1',CAST(userid AS VARCHAR)) AS BIGINT) BETWEEN a AND b	userid	NO: The Analysis Services member "userid" is not contained directly in the query.
WHERE userid BETWEEN a AND b	userid	YES
WHERE userid % 16 = 0	CAST(HASHBYTES('SHA1',CAST (userid AS varchar)) AS bigint)	NO: Query does not return a continuous dataset.
WHERE CAST(HASHBYTES('SHA1',CAST(userid AS VARCHAR)) AS BIGINT) BETWEEN a AND b	CAST(HASHBYTES('SHA1',CAST (userid AS varchar)) AS bigint)	YES
WHERE userid BETWEEN a AND b	CAST(HASHBYTES('SHA1',CAST (userid AS varchar)) AS bigint)	NO: The Analysis Services member <Hash of Userid> is not directly in the query.

Now That We Have an Optimally Parallel Cube…

The results were stunning, as shown by the "V2" lines for both SSDs and hard disk drives (HDDs) (where "V2" is the version 2 cube, which follows the optimizations discussed earlier in this paper).

Figure 5: Performance after enabling "multi-threaded mode".
(Note that in Figure 5, the V2 cube performed at the same speed, on SSDs, in all environments. We show only the SQLCAT line for simplicity.)
The conclusion is that by adding enough I/O (through SSDs so that I/O was no longer a bottleneck), we were able to find and resolve the algorithmic issues, enabling incredible performance. The key is that we never would have found these algorithmic issues without first removing the I/O channel bottlenecks by the use of SSDs.

But Wait, Processes Get in the Way!

At this point, due to operational issues, we were initially unable to go live with the SSD servers. It was agreed that given these results, we should go with the easier-to-implement DAS HDD solution, which offered similar performance. Specifically, our challenge to going live was that, due to a lack of SSD enterprise standards as of mid-2009, the supportability story was too complicated to be sustainable across a large number of servers.
In retrospect, not waiting for SSDs was a serious mistake. The cube went live and user satisfaction plummeted. What went wrong? The results shown in Figure 5 were correct, but somehow, performance was awful.

Parallel User Load

Figure 5, though accurate, shows query response times for a single user only. It does show accurately that, with a solidly parallel cube, good DAS can be nearly as fast as SSDs for reasonably large MOLAP cubes. But a more in-depth analysis found that, in our initial analysis, we failed to consider a highly parallel user load and failed to benchmark a large enough multi-user parallel Analysis Services query load.
Had this been done sooner, the patterns illustrated in Figures 6 and 7 would have been found.

Figure 6: Distinct count querying comparison of different access patterns

Figure 7: Distinct count query comparison of different access patterns (time to respond to first query)
To execute the parallelization scenario, the engineering team used a tool to execute multiple Analysis Services queries in parallel, with the following characteristics:

• Each query selected a distinct dataset – no overlaps between data.
• Each query was run in two concurrency modes: all at the same time, and with 30 seconds between queries, to simulate a real user load.
• Three access patterns were selected and timed: Running six queries serially, running six queries concurrently, and running twelve queries concurrently.
• Each test run was executed multiple times, clearing caches between runs; the times indicated are averages.
• Two measures were recorded: the time for the first submitted query to complete, and the total time for all queries to complete.

After we captured and analyzed this data, our conclusion was clear: Single-user queries benefitted from the use of SSDs. As soon as the number of concurrent business users increased, the benefit of SSD became even more apparent.
From a technical perspective, SSDs allow many more threads of execution to run in parallel without incurring huge I/O wait times, because their random I/O throughput is basically the same as its sequential I/O throughput. This benefit is relevant because multiple independent queries, serviced simultaneously, implicitly cause random I/O at the disk level, and unlike rotating disks, SSD devices do not slow down under random I/O. Though rotating disks slow down to a three-digit number of I/O operations per second when access is random, high-end SSD devices continue to deliver five-digit number of I/O operations per second, sequential or random. This directly translates into more parallel queries, and therefore more concurrent users, per server when its I/O system is based on high end SSD technology.

Recommendations

Here is a list of conclusions we drew from the work we did for the Microsoft Entertainment and Devices Data Warehousing Team. Most have been discussed in this paper, but some just general best practices we want to share with you:

• Remove the I/O bottlenecks by adding fast enough underlying disk I/O. Their absence makes it easier to find algorithmic bottlenecks in SQL Server Analysis Services implementations.
• The Analysis Services workload is well suited, for distinct counts (and Storage-Engine-heavy query loads), to an SSD I/O backend.
• When you evaluate changes to an Analysis Services cube, testing single-user query performance is not enough. If you do not create an independently parallel load, you are not properly simulating usage patterns, because your users are creating parallel loads.
• It is critical to be aware of your production workload, to monitor the queries being run and the performance of the system servicing the queries.
• Even a simple query can stress Analysis Services distinct count performance – it is critical to consider the size of the dataset that a distinct count query returns to accurately assess the query’s performance.
• Follow these rules for making sure Analysis Services can parallelize distinct count queries, in addition to the existing standards and practices for partitioning:
  • Make sure that the distinct count attribute is directly used in the partitioning query.
  • Make sure that the partitioning query function (for all sub-partitions in a single time period) is continuous. Using a hash function and BETWEEN is one way to do this that works well.
• When benchmarking, if you are testing cold-cache scenarios, be sure that you run multiple times and clear all caches between runs. Don’t accept a result as true until you can reproduce it.

Summary

The results of our analysis are clear: SSD technology has significant benefits for MOLAP-based Analysis Services solutions. Because concurrent users implicitly create random I/O patterns, solid-state devices enable greater scalability and per-user performance. In the past, before the advent of SSD technology, getting very high end parallel random I/O performance required a complex and very expensive solution. SSDs offer these benefits without the prohibitively high cost.


http://sqlcat.com/technicalnotes/archive/2010/09/20/analysis-services-distinct-count-optimization-using-solid-state-devices.aspx