Earlier this week I had to take ownership of a new SSAS system due to one of our offices shutting down. I've heard of it exisiting but never was able to take a look at it until just a few days ago.

  

I had heard there were performance problems and when I opened up the cube in BIDS, I came across this...

 

 

It's been awhile since I've run into a "bang the head against the wall" problem, however today I ran into a doozy.

 

I had a measure group (no aggs, two summed measures) associated with three dimensions. There were some modifications done to the fact table that would now allow this measure group to be associated with seven dimensions. Great, more detail! Since these dimensions were already in the database and in the cube, all I'd have to do is just set up a few relationships through the dimension designer, reprocess, and BAM.

 

I do just that and validate the numbers against the fact table. While measure number one looked correct and returned a sum of 29,875.40, measure two returned incorrect numbers. The fact table had 34,450.60 and the cube was returning 34,448.20 - only a difference of 2.40. I process the indexes, process update the dimensions, reprocess the entire cube database, drop and recreate the partitions, and a few other things - NOTHING. It didn't make any sense why would one number would match and the other didn't - they were both coming from the same measure group and fact table.

 

One by one I removeed the new dimensionlal relationships in the measure group. By removing the first dimension the difference drops to 2.15, dimension number two - 1.87, dimension number three - 1.19, ect. There was no rhyme or reason to the difference narrowing.

 

After four hours I open up the cube live through Visual Studio, and on the Cube Measure, tab I rearrange the order of the two measures. Just drop and drag the bottom measure above the other. Reprocess, check the numbers..and...what do you know...the numbers finally match up like they should.

 

Totally frustrating.

Late July I took the 70-448 (SQL Server 2008, Business Intelligence Development and Maintenance) Beta Exam. It looks like the results are now being released being that I just received an email notifying me that I had passed. Woot woot!

 

My prep for the test was zilch. My real world experience was enough for me to pass. Cool eh? In all it took me about an hour and a half to go through the test.

 

I was initially hesitant in taking the exam, but certainly glad I did!

 

 

 

 

More info here:  http://www.microsoft.com/learning/en/us/exams/70-448.mspx

 

With the good weather subsiding here in Seattle for a few days I'm feeling motivated to post something today.

 

Recently there was a post on LinkedIn regarding large dimensional updates (millions of members) in SSAS. The release of Analysis Services 2005 greatly increased the ability to process large dimension compared to AS 2000. Dimensional updates that weren't possible in 2000 are now easily done in 2005.

 

I've dealt with somewhat large dimensions (five to twenty million members) so I thought I'd share some tips that have allowed me to perform daily updates in an acceptable manner.

 

When procssing dimensions, it's not the processing of the dimension members that takes a lot of time, but rather it's the rebuilding of the aggregates. (set ProcessAffectedObjects to True).

 

One of our larger dimension has ten million members and when no data shifts the processing of the dimension takes 15 minutes. If data does shift the reprocessing takes three hours. We also have a dimension with only 30 members that will take 15 seconds to reprocess if there is no data movement; two hours if there is. Data will move around if you have flexible attribute relationships.

 

Things to keep in mind:

1. Carefully examine all flexible relationships, just don't set everything to flexible. This is set by the RelationshipType property on an attribute.

2. Process all your dimensions in parallel so the aggs are rebuilt at once. If you process dimensions in serial this may happen: dimension A processed, aggs are rebuilt, dimension B processed, data moves around, dimension C processed, aggs need to be rebuilt again. Always try to process dimensions in parallel. ALWAYS.

3. Be creative with your partitioning and aggregation designs. Say you have five years of data and you create a partition for each year. Chances are the most recent year or two will be queried differently than the data from four or five years ago. The partitions for years one and two should use a different aggregation design than years four and five. Rebuilding aggregates can take awhile (especially with larger dimensions) and the less you have to rebuild the faster the dimensional update will take.

For example: I have a cube (partitioned by day) with a rolling one year of data with the majority of queries only being run against the last 90 days. After ninety days I assign a different aggregation design to any partition older than that ninety days and reprocess. The last 90 days use an aggregation design with eight aggs, days 90 and older have a design with only three aggs.

4. Get the fastest box you can. From what i've experienced the real limiting factor in fast dimension updates isn't memory, it's CPU.

 

5. Only process dimensions that need to be processed. It's easy to be lazy and process all the dimensions in a cube every single time, however if the data hasn't been changed, why update the dimension? I have a stored procedure that takes the last ETL load time for a table and compares that to the last process time for that corresponding SSAS dimension. If the ETL time is later than the cube process time then the SSAS dimension is processed. This greatly cuts down on processing time.

 

6. To implement most of these suggestions you'll need to write AMO code. If you want to process your cubes in any sort of efficient way the built-in SSAS tasks within SSIS just don't cut it.  You'll need custom code.

You are invited to take beta exam 71-452: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008. If you pass the beta exam, the exam credit will be added to your transcript and you will not need to take the exam in its released form. The results will not appear on your transcript until several weeks after the final form of the exam is released. The 71-xxx identifier is used for registering for beta versions of MCP exams, when the exam is released in its final form the 70-xxx identifier is used for registration.

71-452: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 counts as credit towards the following certification(s).

· Microsoft Certified IT Professional: Business Intelligence Developer 2008. In order to earn this certification you must also pass exam 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Find exam preparation information: http://www.microsoft.com/learning/exams/70-452.mspx

Registration begins: August 8, 2008

Beta exam period runs: August 13, 2008– September 10, 2008

Registration Information

Please use the following promotional code when registering for the exam: 3568C

You must register at least 24 hours prior to taking the exam.

To register in North America, please call:

· Prometric: (800) 755-EXAM (800-755-3926)

Outside the U.S./Canada, please contact:

· Prometric: http://www.register.prometric.com/ClientInformation.asp

More info here: http://blogs.msdn.com/gerryo/archive/2008/08/08/sql-server-2008-beta-exam-71-452-designing-a-business-intelligence-infrastructure-using-microsoft-sql-server-2008.aspx 

 

Had the chance to go take the beta 71-448 for free this morning. Took me about an hour and a half. Since the exam is still in beta you won't receive your score for at least a few weeks. Bummer.

 

Definitely worth a shot, especially if you've never had the opportunity to take a MS exam test.

 

Only good until 7/31.

 

 

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

Registration is now open for the 2008 BI Conference here in Seattle. I was able to attend last year and to say it was worth it would be an understatement. Definitely attend if the opportunity presents itself.

 

With SQL PASS also taking place in Seattle, it's quite a busy few months in the SQL Server world here.

 

Register by August 8th you'll be able to get it at the $995 price, after the 8th it goes up to $1295.

 

 

If anyone needs tips on where to stay in the Seattle area (I live a seven minute walk from the convention center), let me know.

 

 

From time to time I'll need to recreate the dev/beta environment and I have to do that from production. I want an exact copy of production minus all the partitions that have been created with the exception of the template partitions.

 

At first I would go in and change the XMLA script manually to remove those partitions. However, if there were a lot of measuregroups (50-100), this could take upwards of an hour. Being that it was such a pain I probably didn't keep dev and beta as up-to-date as I should have.

 

An Analysis Services Stored Procedure is perfect to handle this task. Works beautifully and what took me an hour to do before now takes 30 seconds.

 

I decided to use arrays within the procedure even though you could write the proc without them.

 

 

 

*objDatabase is a database object I've set in another function

 

 

  Public Sub DeleteAllPartitions()

 

      Dim oCube As Cube

      Dim oMeasureGroup As MeasureGroup

      Dim oPartition As Partition

      Dim i As Integer

      Dim j As Integer

      Dim k As Integer

      Dim AryCubes As String()

      Dim AryCubesSize As Integer

      Dim AryMeasureGroups As String()

      Dim AryMeasureGroupsSize As Integer

      Dim AryPartitions As String()

      Dim AryPartitionsSize As Integer

      'Create the cube array (AryCubes)

      AryCubesSize = (objDatabase.Cubes.Count - 1)

      ReDim AryCubes(AryCubesSize)

 

      'Loop through each of the cubes in the database and throw the cube names in an array

      For Each oCube In objDatabase.Cubes

          AryCubes(i) = oCube.Name

          i = i + 1

      Next oCube

 

      'Loop through the cube array

      For i = 0 To AryCubesSize

 

          'Set the cube object to the current item in AryCubes

          oCube = objDatabase.Cubes.GetByName(AryCubes(i))

 

          'Create the measuregroup array (AryMeasureGroups)

          AryMeasureGroupsSize = (oCube.MeasureGroups.Count - 1)

          ReDim AryMeasureGroups(AryMeasureGroupsSize)

 

          'Set j back to zero for the current item

          j = 0

 

          'Loop through each of the measure groups in the cube and throw the measure group names in an array

          For Each oMeasureGroup In oCube.MeasureGroups

              AryMeasureGroups(j) = oMeasureGroup.Name

              j = j + 1

          Next oMeasureGroup

 

          'Loop through the measure group array

          For j = 0 To AryMeasureGroupsSize

 

              'Set the measure group object to the current item in AryMeasureGroups

              oMeasureGroup = oCube.MeasureGroups.GetByName(AryMeasureGroups(j))

 

              'If the measuregroup is linked then don't delete partitions

              If oMeasureGroup.IsLinked = False Then

 

                  'Create the partition array (AryPartitions)

                  AryPartitionsSize = (oMeasureGroup.Partitions.Count - 1)

                  ReDim AryPartitions(AryPartitionsSize)

 

                  'Set k back to zero for current item

                  k = 0

 

                  'Loop through each of the partitions in the measure group and throw the name in an array

                  For Each oPartition In oMeasureGroup.Partitions

                      AryPartitions(k) = oPartition.Name

                      k = k + 1

                  Next oPartition

 

                  'Loop through the partition array and drop the partition if its not the template partition

                  For k = 0 To AryPartitionsSize

                      If AryPartitions(k) like "template" Then         

                          oPartition = oMeasureGroup.Partitions.GetByName(AryPartitions(k))

                          oPartition.Drop()

                      End If

                  Next k

 

              End If

 

          Next j

 

      Next i

 

  End Sub

About six months ago I had a discussion with another guy about what my preferred data warehouse schema was: snowflake or star. Without hesitation I said snowflake. He looked at me with befuddlement and asked why. I told him that OLAP processes dimensions more efficiently against a snowflaked schema instead of a star. We had nearly a twenty minute discussion exactly why Analysis Services likes snowflakes better than stars but I failed to convince him.  He firmly believed that the star schema was superior and anything short of me taking his firstborn hostage wouldn't change his belief in that. Star vs snowflake usually initiates that type of steadfastness.

 

To back up my believe I put together a test. I created a dimension with three levels with each level having two attributes that were outside of the "Advertiser-Ad Campaign-Banner Ad" hierarchy. A total of nine attributes in the dimension.

 

1. Advertiser (15k rows)

2. Sales Campaign (500k rows)

3. Banner Ad (12 million rows)

An Advertiser has Sales Campaigns and a Sales Campaign has Banner Ads with Banner Ads being the attribute key. 

 

-In star schema land this would all be put into a single table having nine columns across.

-In the snowflake world this ends up in nine different tables.  There'd be an Advertiser table with three columns: Advertiser/PaymentType/Status, a PaymentType table with a single column PaymentType, a Status table with a single column Status, ect (generically speaking).

This dimension has more levels and more attributes but I decided to pare it down for simplicity. The test was run on an Intel Xeon 2.8 with 4GB of RAM with SQL Server RDBMS and Analysis Services on the same box. I created two different dimensions: one based off of a star schema and the other off of a snowflake schema (separate DSV's). Each dimension was processed nine times (three Process Full, six Process Update) and the times averaged.

 

The star based dimension averaged a total of 8:35 per process vs 6:42 for the snowflaked based dimension. Why the big difference?

 

Each attribute runs a SELECT DISTINCT against the source dimensional table. Take the AdType attribute under the Banner Ad level. Against a star schema this SELECT DISTINCT query would execute against a table with 12 million rows, however, against a snowflake schema it would execute on a table with only four rows.

 

For smaller dimensions this doesn't matter much, however if you have large dimensions and update quite frequently such as we do (hourly) a snowflake schema can make a world of difference.

 

Snowflakes are harder to read and tougher for the ETL guy to write, however dimensions process much faster against them. Also, if you're building a dimension using a wizard (shame on you!) the wizard will be able to detect natural hierarchies whereas a star schema won't.

 

Of course this test was done in an afternoon and not under the most scrutinizing conditions so I'm curious as to what others have experienced or think about the subject. What are your thoughts?

 

 

A Trio of Interesting Snowflakes by Ralph Kimball:
http://www.intelligententerprise.com/010629/warehouse1_1.jhtml;jsessionid=Y5BJ04CASJZKMQSNDLOSKHSCJUNN2JVN?_requestid=147558

  

OLAP: How to Index Star/Snowflake Schema Data:
http://support.microsoft.com/kb/199132

 

 

Surprisingly I get a fair amount of hits for books related to Analysis Services. Being that I've bought nearly all of them, I thought I'd give a short opinion on each of them.

 

 

1. Applied Microsoft Analysis Services 2005 - Teo Lachev

 

 

This was the first book released on Analysis Services 2005 and it's pretty decent. It can't be used as a reference book since it's not deep enough; it's more of a tutorial book.

 

If you're looking to learn Analysis Services 2005, I'd start with this book. It has easy to follow exercises and isn't overwhelming (for a beginner) like a few of the other books.

 

 

 

2. Microsoft SQL Server 2005 Analysis Services - Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman

 

 

This is THE BOOK for SSAS2005.  Excellent book, and the one I use the most. It was written by long time members (before MS bought the technology) of the SSAS team over at MS and it's obvious that's the case.

 

There are entire chapters devoted to AMO, Memory Managment, Query Execution, and the Physical Data Model. These chapters cover subjects you won't find on MSDN or BOL. Gets really into the internals of Analysis Services.

 

Get this book. If I could only have one book on my shelf this would be this one. I'm currently reading it cover to cover.

 

FYI: The link for the Foodmart 2005 sample are no longer valid, so get them here: http://www.e-tservice.com/downloads.html

 

 

3. Professional SQL Server Analysis Services 2005 with MDX by Sivakumar Harinath, Stephen R. Quinn  

I recently saw a post on the newgroup about the StartTime in the OLAPQueryLog being incorrect. We have the same problem here (StartTime is eight hours ahead) and it's something I accepted but never looked into the cause.

 

It appears the insert is using GetUTCDate, not GetDate. GetDate uses the OS time zone. I haven't found a way to change this behavior.

 

You could add another column to store the value, however I just wrote a view on top of the table. From what I can tell this should (at least is does right now) handle daylight savings time correctly; hardcoding the hour difference may not.

 

 

SELECT

       MSOLAP_Database,

       MSOLAP_ObjectPath,

       MSOLAP_User,

       Dataset,

       (DATEADD(HOUR,(DATEDIFF(HOUR, GetUTCDate(), GetDate() )),StartTime)) as StartTime,

       Duration

FROM

       OlapQueryLog

 

I work for an online media company which allows our customers to login to a website and check their numbers. For the last several years all their activity reports have been pointed at SQL Server aggregated tables. While this worked, it wasn't the ideal solution. Reports often timed out and the database server would have a high load throughout the day, specifically early month while everyone was checking month end numbers.

 

Eventually my boss approached me about speeding up the reports and possibly pointing them at a cube. However, there could be no changes to the report files or how the data was being called (no front end changes). All the modifications had to be done in the stored procedure.

 

After initial testing, by pointing at cubes rather than aggregated tables, most of the reports would be sped up by a factor of 5-10x. A definite improvement and well worth the work involved.

 

Here are the steps I had to take in order for it to happen:

 

1. Read http://support.microsoft.com/kb/931192 and install the hotfix on your dev box or desktop. You don't have to do this if you'll be working directly on the server.

 

2. Follow the steps here: http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!329.entry. Double hop authentication is funky with SSAS.

 

3. Create a linked server on the same server the stored procedure resides. Use the fully qualified domain name (not optional).

Example:

/****** Object:  LinkedServer [MYCUBESERVER_SalesCubeDatabase]    Script Date: 02/12/2008 13:17:57 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'MYCUBESERVER_SalesCubeDatabase', @srvproduct=N'cubes', @provider=N'MSOLAP', @datasrc=N'MYCUBESSERVER.IPADDY.COM', @location=N'MYCUBESSERVER.IPADDY.COM', @catalog=N'Sales Cube Database'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYCUBESERVER_SalesCubeDatabase',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MYCUBESERVER_SalesCubeDatabase', @optname=N'use remote collation', @optvalue=N'true'

 

 

4. If your database server is running SQL Server 2000 and you are pointing at a 2005 OLAP database (which was our case), you'll need to install the 9.0 OLAP provider on that box.

 

5. Here's a generic example of what the stored procedure looks like (removed error trapping, nocounts, ect)

 

CREATE  PROCEDURE [dbo].[MsrsWebsiteAdCampaignHitsPerDay]

  @CustomerID INT,

  @BeginDate DATETIME,

  @EndDate DATETIME

 

AS

 

--Generate the dynamic SQL

DECLARE @sqlcode VARCHAR(5000)

SELECT @sqlcode =

'

SELECT

       "[Date].[Date].[Day].[MEMBER_CAPTION]" AS ActivityDate

       ,' + (CAST(@CustomerID AS VARCHAR(10))) + ' AS CustomerID

       ,"[Sales].[Customer to AdCampaign].[AdCampaign].[MEMBER_CAPTION]" AS AdCampaign

       ,CAST(convert(float, "[Measures].[Hits]") AS int) AS Hits

       ,CAST(convert(float, "[Measures].[Cost]") AS money) AS Cost

 

FROM Openquery(MYCUBESERVER_SalesCubeDatabase,

''

       SELECT NON EMPTY { [Measures].[Hits], [Measures].[Costs] } ON COLUMNS ,

       NON EMPTY { { { DESCENDANTS( [Sales].[Customer to AdCampaign].[Customer].&[' + (CAST(@CustomerID AS VARCHAR(10))) + '], [Sales].[Customer to AdCampaign].[AdCampaign] ) }  * { [Date].[Date].[Day].[' + @BeginDate + ']:[Date].[Date].[Day].[' + @EndDate + '] } } } ON ROWS 

       FROM [Sales]

'')

' 

 

--Create the temp table to insert the data produced by the MDX

CREATE TABLE #AdCampaignHitsPerDay

(

       ActivityDate NTEXT,

       CustomerID INT,

       AdCampaign NTEXT,

       Hits BIGINT,

       Cost MONEY NULL

)

 

--Insert the data

INSERT #AdCampaignHitsPerDay  EXEC(@sqlcode)

 

--Select for the MSRS report

SELECT

       CAST(CAST(ActivityDate AS VARCHAR(100)) AS SMALLDATETIME) AS ActivityDate,

    CAST((CAST((MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100)) + '/' + CAST((day((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100))) AS VARCHAR(61)) AS [Date],

       YEAR((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))) AS [YEAR],

       MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))) AS [MONTH],

       CAST(AdCampaign AS VARCHAR(50)) AS AdCampaign,

       SUM(Hits) AS Visitors,

       SUM(Cost) AS Cost

FROM

       #AdCampaignHitsPerDay  

WHERE

       Hits > 0     

GROUP BY

       CAST(AdCampaign AS VARCHAR(50)),

       YEAR((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))),

       MONTH((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME))),

       (CAST(CAST(.ActivityDate AS VARCHAR(100)) AS SMALLDATETIME)),

       (CAST((CAST((MONTH((CAST((CAST(.ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100)) + '/' + CAST((day((CAST((CAST(ActivityDate AS VARCHAR(100)))AS DATETIME)))) AS VARCHAR(100))) AS VARCHAR(61)))

ORDER BY

       AdCampaign, ActivityDate

ORDER BY

       AdCampaign, ActivityDate

 

 

DROP TABLE #AdCampaignHitsPerDay

 

exit_procedure:

 

 

6. That's really about it. 90% of the time was spent just trying to get the double hop authentication working.

 

 

I'm looking for something to blog about, any ideas?

 

jesperzz at hotmail dawt com

Yikes, nearly three months since I've updated this blog. I did have myself a nice mini-sabbatical in Spain during that time.

 

A few months ago I wrote about setting data slices on MOLAP partitions. Eric Jacobsen replied in the comment section stating that improvments had been made in Hot Fix   3186 and above. We installed 3200 and did see better performance.

 

Thanks to Eric and the guys over at MS for being on top of this stuff. I've found the Analysis Services team to be quite proactive in getting issues resolved.

Recently we had a problem with Analysis Services double hop authentication. Thanks to the uber DBA guy here we were able to find a solution.

 

Thought I would share his write up (he spent a lot of time searching out a solution).

 

 

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

 

As you know we have run into a problem with querying Analysis Services from another computer (our desktops for example) after connecting to a secondary server.

 

After working on this case with MS, we have an understanding of the problem and we have a solution.

The problem is the desktops not requesting Kerberos tickets from the domain controller. This is a bug in Kerberos dll and is fixed with a hotfix by Microsoft.

 

Solution:

1)     A Kerberos hotfix must be installed in our desktops.

Kerberos Hotfix canbe installed from (hotfix for IA64, x64, Win2003 also exist)

 

2)     The linked server from secondary server to target server (cubes) must be defined with Fully Qualified domain name.

 

I would like to specify a couple of more points here, since they are prerequisites for this double hop authentication to work.

 

1)     We need to define the servers with "Trust this computer for delegation to any service (Kerberos only)" in Active Directory as opposed to “Do not trust this computer for delegation”.

2)     We need to register the OLAP service with SPN as follows for the target server

a.     Setspn.exe -A MSOLAPSvc.3/CUBESSERVER USER\svc_db

b.    Setspn.exe -A MSOLAPSvc.3/CUBESSERVER USER\svc_db

 

About six months ago we had an issue with incorrect query results. (http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!250.entry). I had included in an aggregation a reference dimension with materialized set to False and SSAS didn't like this.

 

Well much to my chagrin I tried the same thing yesterday in Aggregation Manager and received this error:

 

 

Cool.

 

Be warned, you'll have to download the latest version of BIDS to get the newest Aggregation Manager. It's really been cleaned up since the first version shipped with SP2.

I'm sure these won't be available on Google books for long, however right now you can look at the pretty much the entire book at no cost. Very cool...even though I prefer to have a hard copy.

 

Microsoft SQL Server 2005 Analysis Services By Irina Gorbach, Edward Melomed, Alexander Berger, Py Bateman

 

 

Microsoft SQL Server 2005 Integration Services By Kirk Haselden

Last month I had a case open with MS and the subject of setting data slices came up. (http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!304.entry). I decided to do a little investigating myself and found some of our queries were doing a scan of all the partitions when they shouldn't have been. Not good. This was severely slowing down user queries.

After running queries and setting the data slices manually it became evident this was the problem. I'll set the slices via my AMO code. Well, it wasn't as easy as I initially thought it would be. All of our measure groups set slices on dates, however the date dimensions can have different names across cubes. Revenue Date, Sales Date, Customer Date ect.

 

We have a cube metadata table used by our AMO code which allows certain settings on a measure group by measure group basis. What type of partitoning is done, what fact table is used, whether to turn the processing off, if we want to only maintaining a rolling number of days, ect. A bunch of different settings, something like 25. I added to that table what slice should be used in that measuregroup.

 

 

 

 

Though the AMO code I then lookup that slice and set it.

 

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

'Day/Week/Month Slice Set
If sProcessingType = "Day" Or sProcessingType = "Week" Or sProcessingType = "Month" Then
   GetSlice(sProcessingType, sCalendarDWID, "")
   oNewPartition.Slice = strDateSlice
End If

'Hour Slice Set
If sProcessingType = "Hour" Then
   GetSlice(sProcessingType, sCalendarDWID, sTimeOfDayDWID)
   oNewPartition.Slice = "(" & strDateSlice & "," & strHourSlice & ")"
End If

objMeasureGroup.Partitions.Add(oNewPartition)
oNewPartition.Source =

New QueryBinding(objDatabase.DataSources(0).ID, sQueryText)
oNewPartition.Update()
oNewPartition.Process(ProcessType.ProcessFull)
objServer.CommitTransaction()

Public Sub GetSlice(ByVal sProcessingType, ByVal sCalendarDWID, ByVal sTimeOfDayDWID)

Dim cmd As New SqlCommand

If objConnection.State = ConnectionState.Closed Then
   objConnection.ConnectionString = strConn
   objConnection.Open()
End If

cmd.Connection = objConnection
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 600

'Day Slice
If sProcessingType = "Day" Then
Try
   cmd.CommandText = "select DaySlice from vDim_Calendar where calendardwid = " & sCalendarDWID
   strDateSlice = (cmd.ExecuteScalar())
   objConnection.Close()
   strDateSlice = strDaySlice & strDateSlice
Catch
   objConnection.Close()
   strKeepProcessing = False
End Try

End If

'Hour Slice
If sProcessingType = "Hour" Then
Try
   cmd.CommandText = "select DaySlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
   strDateSlice = (cmd.ExecuteScalar()) 
   cmd.CommandText = "select HourSlice from vDim_TimeOfDay where timeofdaydwid = " & sTimeOfDayDWID 
   strHourSlice = (cmd.ExecuteScalar()) 
   objConnection.Close() 
   strDateSlice = strDaySlice & strDateSlice
Catch 
   objConnection.Close() 
   strKeepProcessing = False
End Try
End If

'Week Slice
If sProcessingType = "Week" Then
Try
   cmd.CommandText = "select WeekSlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
  strDateSlice = (cmd.ExecuteScalar()) 
  objConnection.Close() 
  strDateSlice = strWeekSlice & strDateSlice
Catch
  objConnection.Close()
   strKeepProcessing = False
End Try
End If

'Month Slice
If sProcessingType = "Month" Then
Try 
   cmd.CommandText = "select MonthSlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
   strDateSlice = (cmd.ExecuteScalar()) 
   objConnection.Close() 
   strDateSlice = strMonthSlice & strDateSlice
Catch 
   objConnection.Close() 
   strKeepProcessing = False
End Try
End If

End Sub

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

 

 

To find what value the slice should be set to we pull the value from our Date dimension table. I decided to use the member name rather than the member key since our member keys differ throughout our databases.

 

Set slice on membername: [Date].[Day].[10/03/07]

Set slice on member key (add the ampersand): [Date].[Day].&[2433]

 

 

 

 

It's fairly easy, however it took a while to think of how to do this all in one central place with the slice dimensions having different names. It's controlled in that cube metadata table and requires no changes to our AMO code whenever we add a new measuregroup.  

 

If you have a database of a larger size definitely set the slice yourself. Don't depend on Analysis Services to do it for you. Everything I've read told me the opposite (automatic slice detection) but from what I experienced automatic slice detection is just not reliable.

 

 

Various links about data slices:

 

CAT SSAS Partition Slicing : http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

TechNet thread about slices: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1321947&SiteID=17