Analysis Services

Microsoft Analysis Services
ProcessAdd bug in AMO 2016
I saw the question below on the MSDN forum about processAdd not working in AMO 2016 and I thought it sounded strange so I did some investigation:https://socia... When I ran Redgate Reflector over the Microsoft.AnalysisServices.... I came across this little gem: Where it checks if the object is of a type IQueryBinding from the Miocrosoft.AnalysisServices... ......

Posted On Monday, November 14, 2016 9:26 AM | Comments (0)

SSAS Tabular–per table LastProcessed dates
I saw a question yesterday on the MSDN forums ask how to go about exposing the LastProcessed datetime per table. Marco has a great post here (https://www.sqlbi.com/arti... about a number of ways to get the last processed date at the database level. But none of these techniques work at the table level. Having a look through the various DMVs that are available and none of them seemed to have this information apart from DISCOVER_XML_METADAT which returns a large ......

Posted On Wednesday, May 13, 2015 9:52 PM | Comments (1)

DAX Studio 2.1 Released
Today I am pleased to announce the release of the latest update to DAX Studio – v2.1.0 You can get it from the releases page on codeplex: http://daxstudio.codeplex.c... Below is an outline of what’s new in 2.1. A big thanks to Daniele Perilli for his assistance with the graphics and Marco Russo for his work on the Query Plans and Server Timings tabs plus his help with testing this release. UI Refresh Thanks to assistance from Daniele Perilli on the graphics side we now have a lot more consistency ......

Posted On Wednesday, March 18, 2015 7:17 AM | Comments (4)

The perils of calculating an Average of Averages
I've seen questions around issues calculating averages come up a few times in various forums and it came up again last week and I feel that there is some benefit in walking through the details of this issue. For many of you the following will be nothing new, but I'm hoping that this may serve as a reference that you can point to when you get requests for this sort of calculation. The core issue here is really a fundamental mathematical one. Personally I see it surfacing most often in DAX and MDX ......

Posted On Monday, July 28, 2014 7:18 AM | Comments (19)

MDX equivalent of a filtered GROUP BY in SQL
Does that title make sense? I don't know if it does, but I can't think of another description for this problem. If anyone can think of a better title I would love to hear it. It's hard to explain in words so let's jump into some code examples. Consider the following SQL statement against the AdventureWorksDW relational database. The requirement is to select a list of 4 cities and then want to see the order quantity grouped at the country level. SELECT g.EnglishCountryRegionName Country ,sum(OrderQuantity) ......

Posted On Wednesday, January 28, 2009 6:54 AM | Comments (21)

SSAS: Are my Aggregations processed?
You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ......

Posted On Tuesday, December 2, 2008 10:17 PM | Comments (9)

The case of the vanishing KPIs
I was contacted today with an interesting issue, we had a tabular model that had some KPIs which were not showing up in Power View. The first thing I checked was the version setting on the model. KPI support was not added to tabular models in SP1. If your model is set to a compatibility version of RTM (1100) Power View will detect this and will effectively not ask for metadata about the KPIs. However in this case when we checked the database properties from SSMS the compatibility setting appeared ......

Posted On Thursday, July 3, 2014 7:33 AM | Comments (1)

Implementing Column Security with #SSAS Tabular and #DAX
Out of the box Analysis Services (both Tabular and Multi-dimensional) has great support for horizontal or row based security. An example of this is where you would give User1 access to all data where the Country is “Australia” and give User2 access to all data where the country = “United States”. This covers a large percentage of the security requirements that most people have. But neither technology has great support for vertical or column based security. This sort of requirement is most common ......

Posted On Tuesday, April 22, 2014 11:20 PM | Comments (22)

An Analysis Services 2008 nugget
I was experimenting with some of the PerformancePoint APIs today, but I was doing something wrong as I kept getting ERROR! back. I assumed that some of the parameters that I was using was resulting in invalid MDX being generated. I was running against a test database on SSAS 2005 and as I suspected there was a syntax error in the MDX. Unfortunately Profiler against SSAS 2005 showed me the error, but not the offending MDX, which was not much help. On a whim I decided to move my test database to SSAS ......

Posted On Tuesday, May 12, 2009 11:18 PM | Comments (0)

#DAX – Joining to a Slowly Changing Dimension
The following is one of the scenarios that I showed during my “Drop your DAX” talk at SQL Saturday #296 in Melbourne. Currently SSAS Tabular and PowerPivot models can only have a relationship based on a single column. So what do you do when you need to join based on multiple columns? Ideally you would solve this during your ETL. With a type 2 slowly changing dimension you typically want to insert the surrogate key for the dimension into the fact table. As you may know, “type 2” dimensions can have ......

Posted On Wednesday, April 9, 2014 7:18 AM | Comments (4)

How to build your own SSAS Resource Governor with PowerShell
A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell. You might ask - why create a Powershell version? Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in ......

Posted On Monday, March 31, 2014 6:04 AM | Comments (5)

I agree - It really is all about MDX (baby)
Nick Barclay did an interesting post recently, advocating learning MDX if you are going to be working with Performance Point Server. This is due to the fact that Performance Point builds SSAS cubes on the back end and SSAS which lives and breathes MDX. So the same advice really also extends to anyone using SSAS. You really need at least one MDX guru on your team - someone that can write MDX select statements, if you want to get the most out of SSAS. A couple of resources that Nick did not mention ......

Posted On Friday, July 27, 2007 10:24 AM | Comments (0)

SSAS – Listing Active Queries with PowerShell
Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in ......

Posted On Monday, March 3, 2014 7:20 AM | Comments (2)

SSAS: Clearing the MDX Script for a session in SSMS
Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you may even have design changes ......

Posted On Saturday, February 2, 2013 8:52 AM | Comments (0)

New Book - Microsoft SQL Server Analysis Services
Amazon has kindly let me know that, based on some of my previous purchases (notice the classic use of data mining here?), I might be interested in Edward Melomed's soon to be released book Microsoft SQL Server Analysis Services. Edward is a program manager on the development team at Microsoft and he and a few of the other co-authors joined Microsoft as part of Microsoft's aquisition of OLAP Services from Panorama back in the SQL Server 7.0 timeframe, so hopefully this book should have a few insights ......

Posted On Friday, December 15, 2006 2:32 PM | Comments (2)

SSAS: Executing MDX Scope statements using SSMS
So usually when I want to test an MDX expression in SSMS I'll write a query with a "WITH MEMBER…" clause to create a query scope calculated measure. But sometimes you may want to test a scoped assignment before putting it in your cube script. The following steps show you how to do this. 1. Click on the button to open an new MDX window, enter your server name and then click on the "Options >>" button 2. You must then specify the database that you are using 3. Then under the "Additional Connection ......

Posted On Wednesday, December 19, 2012 6:52 PM | Comments (5)

SQL PASS Summit & MVP Deepdives Volume 2
I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/dela... ......

Posted On Tuesday, October 11, 2011 9:24 AM | Comments (1)

Book Review - MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook
Full Disclosure: I was one of the technical reviewers on this book. I think my friend Tomislav did a great job on this book and it would make a valuable addition to the bookshelf of anyone that is working with MDX. I really enjoyed reading this and there were even a couple of interesting techniques that I have added to my toolkit. As far as I know there are not any other MDX books on the market quite like this one. It's more aimed at the intermediate level of MDX user and assumes that you have some ......

Posted On Monday, October 10, 2011 9:36 AM | Comments (0)

I'm an Analysis Services Maestro
A number of people have spotted this announcement on the SQLCAT blog already: http://sqlcat.com/sqlcat/b/... but if you have not seen it yet, I’m very proud to say that I’ve been made an Analysis Services Maestro! If you have not heard about the Maestro program you can find out more details about it here; as Chris has said, it’s basically something like an MCM for Analysis Services. The course itself was a fair bit of ......

Posted On Friday, September 23, 2011 12:05 PM | Comments (6)

MDX needs a function or macro syntax
I was having an interesting discussion with a few people about the impact of named sets on performance (the same discussion noted by Chris Webb here: http://cwebbbi.wordpress.co... And apparently the core of the performance issue comes down to the way named sets are materialized within the SSAS engine. Which lead me to the thought that what we really need is a syntax for declaring a non-materialized set or to take this even further a way of declaring ......

Posted On Thursday, March 17, 2011 2:33 PM | Comments (0)

A new blogger on the SSAS team

I just noticed that Jeffrey Wang from the Analysis Services team has started blogging. He has put up a great first post on “Execution Plans and Plan Hints for MDX IIF Function and CASE Statement”. Check it out here http://mdxdax.blogspot.com. If you want to subscribe you can get an RSS feed at  http://mdxdax.blogspot.com/rss.xml (I don't know why Blogspot does not make the rss easier to find)

Posted On Wednesday, January 12, 2011 7:50 AM | Comments (1)

Do DAX and MDX need a safe divide operator?
I’ve been teaching an MDX course for the last few days as well as reading Marco and Alberto’s excellent PowerPivot book on the train and it struck me that every time I do a division in both languages I seem to be using the following pattern in order to avoid returning an error to the user MDX: IIF( <denominator> = 0, NULL, <numerator> / <denominator> ) DAX: IF( <denominator> = 0, BLANK(), <numerator> / <denominator> ) I know that languages like C++ and C# don’t ......

Posted On Friday, November 19, 2010 8:05 AM | Comments (3)

Holy Cow Bat Man! –PASS Summit Keynote Day 1
Wow. There were a stack of announcements made at the key note. So I thought I would try to jot then down while I'm just waiting for my first technical session a Deep Dive on PowerPivot to start. The Parallel Data Warehouse Edition of SQL Server 2008 R2 has been released to manufacturing, there was a demo with a query over an 800 Billion record table that was returning in 19 seconds on a 40 node parallel cluster. Yahoo came up and talked about an SSAS cube of theirs, it's 12TB in size and they load ......

Posted On Wednesday, November 10, 2010 5:27 AM | Comments (0)

Documentation on Analysis Services Server Properties
[via Jamie Thomson] http://download.microsoft.c... As Jamie points out, what would be really nice now is a white paper on performance tuning and how best to tune these settings. Mosha discussed a white paper some time ago, that he was working on regarding AS caching, but I don't know if that touch on server settings. It looked more like it would help in understanding some of the volumes of information that can be ......

Posted On Wednesday, June 28, 2006 8:44 PM | Comments (0)

Generating XMLA - referencing Objects by name
Jamie Thomson has an interesting post here http://blogs.conchango.com/... about some of his recent experiences with Analysis Services. One point that particularly stuck out for me an that was his criticism of having to access objects using their ID instead of their name in XMLA. This affected me recent while I was working on the PowerShell provider for AMO (which is still a work in progress). Most of the collections in AMO can be accessed using the object's ID, ......

Posted On Wednesday, June 21, 2006 8:52 PM | Comments (9)

SQL 2005 - BOL Feedback
Chris Webb and a couple of other people mentioned in the last couple of months that if you see an error in a Microsoft help file, not to whine about it, but to click on the feedback link and let them know about the issue. I spotted a small error (back in November last year) where count measures in Analysis Services 2005 were listed as being semi-additive, when in fact they are additive. So I decided to give it a go and sent some feedback in. Well, I received feedback from a Programming Writer at ......

Posted On Wednesday, January 18, 2006 5:34 PM | Comments (0)

MDX: Implicit Recursion
A few months ago Richard Lees did a blog post showing how you can calculate a LastNonEmpty measure using recursion. In Richard's example he used what I call "explicit recursion" in that he explicitly referenced the measure in it's own expression. An example of this sort of calculation against Adventure Works would look like the following. WITH MEMBER Measures.LastNonEmptyExplicit AS IIF(IsEmpty(Measures.[Internet Sales Amount]) ,([Date].[Calendar].PrevMem... Measures.[LastNonEmptyExpli... ,Measures.[Internet ......

Posted On Saturday, November 6, 2010 6:21 AM | Comments (1)

Visio 12 - a BI Client?
Nick Barclay, another BI blogger from downunder, has just blogged about using Visio 12 as a BI client. It sounds interesting, things such as being able to associate areas of a map with KPIs or Measure from a cube sounds like it might have potential. It will be interesting to see how this comes off in practice. Chris Webb has also been blogging about Office 12 with some details about the BI features in Excel 12 ......

Posted On Monday, December 19, 2005 7:09 PM | Comments (0)

Where to get the OLE DB for OLAP v9.0 Provider
[via Chris Webb] The other day I was looking for the OLE DB for OLAP v9.0 Provider to install on a client machine but couldn't find it on the AS2005 install CD (which is where it was in the June CTP, and where you got PTS for AS2000); I then found a newsgroup post stating that it was only going to be available as a separate download from now on. Here's where you can now get it and other useful stuff like ADOMD.Net, the latest PTS for AS2000, various other OLE DB providers etc: http://www.microsoft.com/do... ......

Posted On Friday, November 11, 2005 9:18 AM | Comments (0)

Does SSAS support MSDTC Transactions?
I got asked a question a while ago on whether SSAS supports the transaction settings in SSIS. To answer that we really need to understand what happens when we set the TransactionOption property to Required on an SSIS container. When you switch on transactions in SSIS it uses the Distributed Transaction Coordinator (DTC) to coordinate the transactions. The Distributed Transaction Coordinator is a Windows service that coordinates transactions across multiple processes and/or machines. I did not expect ......

Posted On Sunday, September 26, 2010 9:48 PM | Comments (2)

PowerSSAS v0.3.1.0 released
Today I put out a new release for PowerSSAS. This release adds the following new cmdlets: backup-ASDatabase clear-ASCache get-ASConnection get-ASRole restore-ASDatabase It also includes a Powershell v2 .psd1 module file and includes the option to just download a zip file which can be copied into a folder your modules folder. So you can now install PowerSSAS without needing to have admin privileges. There are instructions on installing PowerSSAS as a v2 module here. I have also done some preliminary ......

Posted On Sunday, July 18, 2010 10:34 PM | Comments (0)

Poll Results: Foreign Key Constraints
A few weeks ago I did the following post asking people – if they used foreign key constraints in their star schemas. The poll is still open if you are interested in adding to it, but here is what the chart looks like as of today. (at the bottom of the poll itself there is a link to the live results, unfortunately I cannot link the live results in here as the blogging platform blocks the required javascript) Interestingly the results are fairly even. Of the 78 respondents, fractionally over half at ......

Posted On Sunday, May 23, 2010 9:44 PM | Comments (0)

Poll: Foreign Key Constraints
Do you create foreign key constraints between dimensions and facts in your relational star schemas? I don't want to bias the results in any way, so I won't post my opinion just yet. But a recent discussion got me thinking about the following question and I'm interested to hear what other peoples approaches are. Follow this link to get to the online poll Feel free to post comments if you want to explain the reasons for your answer ......

Posted On Wednesday, March 24, 2010 4:05 PM | Comments (3)

SSAS: Automating the Scripting of an SSAS database
I've been meaning to post this for a little while, and a recent post on the SSAS forum at ssas-info.com prompted me to finally get around to it. Basically the small Powershell script below will attach to the specified SSAS server and script all of the databases out to an XMLA file. In this example I also add a timestamp in the form of YYYYMMDD to the end of the file. $serverName = "localhost\sql08" $outputFolder = "C:\data\" ## load the AMO and XML assemblies into the current runspace [System.Reflection.Assembly... ......

Posted On Monday, February 22, 2010 11:43 AM | Comments (2)

SSAS 2008 R2 – Improvements for slow Metadata
There was a thread last year on the SSAS MSDN forum SSAS 2008 -- Why is metadata so slow??? in relation to slow metadata queries on a cube with 250+ measure groups. The good news is that despite the fact that the SSAS team has largely been focussed on PowerPivot for the SQL Server 2008 R2 release, they have also tried to address a few of the customer pain points in SSAS. Below is part of a recent response to this thread from Akshai Mirchandani from the SSAS development team: "As an update to this ......

Posted On Monday, February 8, 2010 11:15 PM | Comments (0)

SSAS: Can I make my Distinct Count totals subtotal correctly?
I've seen this question come up a couple of times. What is usually being asked is "can we make the subtotals additive for a distinct count measure?" – and the short answer is a resounding No! But the short answer does not really make for a compelling blog post, so let's dig a little deeper into this issue. The more complete answer is that while there are some techniques that can make a distinct count "appear" to be additive in some circumstances, they are fundamentally flawed and will produce inconsistent ......

Posted On Wednesday, January 13, 2010 5:29 AM | Comments (8)

Analysis Services 2008 Performance Counters showing zero
I had an issue recently with my SSAS 2008 performance counters - they were all showing up as 0. I could see them listed in Performance Monitor, but none of them worked. The counters for my SSAS 2005 instance worked fine, but the 2008 ones did not. I had observed this behaviour on my old Vista x86 machine and now it was happening on a fresh install of Windows 7 x64. I tried numerous steps to troubleshoot this issue including going through this detailed blog post from the CSS SQL Server Engineers blog, ......

Posted On Sunday, November 22, 2009 11:22 PM | Comments (3)

Gemini/PowerPivot MOSSIG Presentation
I did a presentation earlier this month on PowerPivot (a.k.a Project Gemini) at the MOSSIG meeting and as promised here is the slide deck. If you've seen other "Gemini" presentations there will be nothing new in this presentation, I've really only posted it here so that those who attended the meeting can refer to it. You can download the "short" (1Mb) version with just the content slides: Or you can get the full (8.3Mb) slide deck including the "fairy tale" images from the link below: ......

Posted On Wednesday, November 4, 2009 6:58 PM | Comments (4)

SSAS: Beware of measure data types
There was a thread today on the Analysis Services forum where someone appeared to be having an issue with a data type overflow. Multiple large, positive, integer values were aggregating up to a negative amount. If you are interested the full thread is here: Re- negative values on a measure Basically the database and the DSV were increased to a bigint data type, but the issue was still occurring. What I believe is happening here is that the measure in the cube was set with an int data type. To fix ......

Posted On Monday, November 27, 2006 1:26 PM | Comments (4)

SSAS: Office 2007 - breaks the calculation tab in BIDS
This probably does not apply to many people yet, but If you have installed the RTM of Office 2007 and don't yet have SP2 of SQL 2005 installed, then your calculation tab in the cube designer is probably broken. This issue may manifest itself in other ways too, its just that I knew this had been an issue during the beta cycle of office thanks to Reed at the Hitachi Consulting blog. There are details here http://sqlug.be/forums/339/... on how to fix this issue. Basically it looks like ......

Posted On Friday, November 17, 2006 2:35 PM | Comments (5)

Which Edition is my Analysis Services Server?
There was a question the other day on the MSDN SSAS forum asking how to find out the Edition of SSAS which is running on a given server. The answer to this question could be either Standard, Enterprise or Developer. The Object Explorer in Management Studio displays the current version next to the server name, but not the Edition. Now at a basic level I know that this is stored as a property of the server, so you could run a DISCOVER_XML_METADATA command like the following. <Discover xmlns="urn:schemas-microsof... ......

Posted On Wednesday, October 14, 2009 8:20 PM | Comments (0)

SQL Downunder 2009 Presentation: SSAS Metadata – Knowledge is Power
I just finished my talk at the 2009 SQL Downunder Code Camp on Analysis Services data. As promised, attached below are the scripts and Powerpoint slides that I used in the presentation. There is not that much information in the slides, they were really just introduction and conclusion - all of the content is in the scripts. And the scripts probably only make sense if you at the session. In addition to the scripts, below are the links to all of the free tools that I was using: •MDX Studio http://ssas-info.com/forum/... ......

Posted On Sunday, October 11, 2009 2:18 PM | Comments (2)

Book Review - Analysis Services 2008 Unleashed
Full Disclosure: I was lucky enough to be sent a free review copy by the publisher But... if I had not been sent a review copy I would have gone out and bought a copy of this book anyway. Why is that? Because I had purchased the previous edition and I knew that it would be the most in depth book on SSAS 2008 available. It has been written by members of the product team and contains a wealth of information that just could not come from any other source. I'm sure most of you have heard that Attribute ......

Posted On Sunday, October 4, 2009 9:36 PM | Comments (1)

SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure
A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular ......

Posted On Thursday, July 2, 2009 7:42 AM | Comments (15)

SSAS: Executing Arbitrary SQL queries
I had a question a little while ago via my blog about possibly using a rowset action to execute a SQL query against a specified table. Although a rowset action will allow you to enter a SQL query, such a query is still executed against the current cube and only the subset of SQL supported by SSAS can be used. Basically the rowset action just returns a flattened result set. However what would be possible would be to write a .Net stored procedure and use that to execute your SQL query. The code itself ......

Posted On Thursday, June 18, 2009 11:20 PM | Comments (8)

SSAS: Powershell to replace a group member in a role
There was a question in the SSAS forum recently on how to replace one group name with another within the membership of a number of SSAS roles in a number of databases. While you could possibly do this with XMLA it would be tricky as you have to re-submit the whole membership list, you can't just add/remove single members. The easiest way to do this is to write something using the AMO library and in my opinion the easiest way to write a script for AMO is using Powershell. Below is my short script ......

Posted On Thursday, June 11, 2009 8:41 AM | Comments (2)

SSAS: T-SQL Equivalent for a Many-to-Many relationship
This question came a while ago now in this SSAS forum thread: What is wrong in my query and I thought it was something that may interest other people. Basically it boiled down to trying to find a T-SQL equivalent to the following MDX which is querying a dimension with a many-to-many relationship to the measure. So given the following simple MDX query, what would be the equivalent in SQL? select measures.[Internet Sales Amount] on 0 , [Sales Reason].[Sales Reasons].[Reason Type].Members on 1 FROM ......

Posted On Sunday, May 3, 2009 9:47 PM | Comments (2)

SSAS: There is no such thing as an Attribute in MDX!
In MDX in SSAS the term "attribute" occasionally gets used interchangeably with "hierarchy" and "level", but it's not technically accurate. In SSAS, MDX has concepts of Dimensions, Hierarchies and Levels, but not Attributes. Attributes and Attribute Relationships are design time concepts that are understood by the storage engine, but they are not directly exposed in the MDX language. Attributes actually map to levels (or properties) in MDX. I think the confusion comes about because, by default, an ......

Posted On Sunday, March 15, 2009 11:04 PM | Comments (3)

How to build your own Super Model - Melbourne SQL User Group
Next week I am giving my talk talk on "How to build your own Super Model" to the Melbourne SQL User Group. This is the same one that I presented to the Adelaide User Group last month. It is an introductory look at dimensional modeling for Analysis Services. Where we will talk about what it is, how it’s done and look at the features that Analysis Services provides to support some of the different modeling techniques. The focus of this session will be around the various types of dimension usage, looking ......

Posted On Monday, February 16, 2009 11:47 PM | Comments (9)

SSAS: Listing Attribute Relationships
Occasionally questions come up about how to extract certain pieces of metadata from Analysis Services. In general all the metadata that you would need on a day to day basis is pretty well covered by the standard schema rowsets. And in SSAS 2008 you can use the system DMVs to get at most of this data. For example, if you want to get a list of the current user sessions on the server you can do the following... SELECT * FROM $System.DISCOVER_SESSIONS ...and in SSAS 2005 you can use the same syntax with ......

Posted On Wednesday, November 26, 2008 10:58 PM | Comments (5)

Teaser: SSAS Cache Warming with PowerShell
You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data. What I ended ......

Posted On Sunday, November 16, 2008 11:41 PM | Comments (5)

Podcasts from the 2008 Microsoft BI Conference
The B-eye-network website is hosting a series of podcasts from the Microsoft BI Conference. I have not had a chance to listen to any of them yet, but there are quite a number of senior Microsoft people on the list so they should be interesting. http://www.b-eye-network.co... ......

Posted On Friday, October 10, 2008 10:35 AM | Comments (0)

SQL Down Under Code Camp 2008
The SQL Down Under Code Camp for 2008 is on in a couple of weeks. Over the weekend of the 11th and 12th of October 2008 the cream of the Australian SQL Server community will gather at Charles Sturt University in Wagga Wagga. This is a free community even, if you can get yourself there it's not too late to go to the website, have a look at the list of sessions and sign up. There is a range of sessions covering all things SQL Server, including one from me on SSAS. It's going to be a great weekend. ......

Posted On Tuesday, September 30, 2008 10:27 PM | Comments (0)

Off to SQL Down Under Code Camp this weekend
As Rob has just said, this weekend is going to be a big one for the Australian SQL Server community as we are having our second ever SQL Down Under code camp at Wagga Wagga (which is curiously pronounced "Wogga Wogga"). It looks like we will have all 7 Australian SQL Server MVP's at this event. Last year's event was great fun and it's shaping up to be a good one again this year. I'm heading up there tomorrow and looking forward to seeing some great presentations and catching up with friends that ......

Posted On Wednesday, October 10, 2007 5:46 PM | Comments (0)

SSAS: MDX Studio - 0.2.0.0 Released
For those of you who are interested, but may not have checked the MDX Studio forum in the last few days, Mosha has release a new update. You can read about the details and download the 0.2.0.0 release from the links on this posting http://www.ssas-info.com/fo... enjoy! Update: A new 0.2.1.0 release has just been made available that fixes a few issues that were found in the previous release (keep in mind that MDX Studio is still in the alpha phase) ......

Posted On Friday, October 5, 2007 11:02 AM | Comments (0)

SSAS: Deploying to renamed databases
Last year I logged an issue on the connect site around deploying from BIDS and renamed databases. https://connect.microsoft.c... I got a couple of messages relating to this issue, one saying that it was being closed as it was a duplicate issue and then another one recently where it was updated to indicate that the issue has been fixed in SQL Server 2008. I think it is really great to get this sort of feedback that something is being done, unfortunately ......

Posted On Sunday, September 28, 2008 9:50 PM | Comments (12)

Analysis Service Performance Tuning Whitepaper has been released
I'm guessing most of you that read my blog also read Mosha's or Elizabeth Vitt's so you may have seen this already, but if you don't read either of these blogs (and you are into Analysis Services) then you have two things to do after reading this. Download the SSAS 2005 Performance Tuning Whitepaper that they both just blogged about here and here Go and subscribe to their blogs :) ......

Posted On Wednesday, January 31, 2007 4:07 PM | Comments (0)

SSAS: Verifying Backups
There was a question on the SSAS forum recently asking if it was possible to verify a backup file. Now if you have checked in Books Online to check the XMLA backup and restore commands, checked the UI in SSMS and even used reflector against the AMO library you would think that there was no way of doing this. I certainly did. However try the following... Navigate to: <Program Files>\Microsoft SQL Server\MSAS10.SQL08\OLAP\bin then type: msmdsrv /? And you will get the following information: Usage: ......

Posted On Saturday, September 13, 2008 3:42 PM | Comments (2)

MVP Theatre Agenda at Tech Ed Australia
If you are registered for Tech Ed Australia this year, come along to MVP Theatre to watch MVPs delivering sessions ranging from 20 minutes to the full 75 minutes. There are only 20 seats in MVP Theatre, so you need to be quick getting to the Expo Hall if you’re serious about catching an MVP Session. The Agenda for MVP Theatre is here (please watch it for last minute updates). To attend, you must be a registered delegate, so if you haven’t bought your Tech Ed ticket yet, you can register here. I’m ......

Posted On Sunday, August 24, 2008 10:20 PM | Comments (0)

Error 2738 while trying to install Adventure Works BI sample on Vista
When trying to install the Adventure Works 2008 sample database on my new SQL Server 2008 instance I very quickly got a message box about the installation failing with an error of 2738. For some reason this sounded familiar and sure enough a quick search turned up this post from Mitch (see: The error code is 2738) where he had the same error (with a different installer). It appears to be the same issue as the same fix worked for me and now I am up and running with the new sample database. Technorati ......

Posted On Wednesday, August 20, 2008 4:08 PM | Comments (1)

BIDS Helper 1.3 Released (now with support for SQL 2008)
I am pleased to announce that the BIDS Helper team have just released v1.3 which is our first release with support for SQL Server 2008. With these release we have also improved our international support. All of the features in BIDS Helper still only display in English in their user interfaces. But in previous versions, some of the features of would not work at all in non-English versions of BIDS because of the way we were integrating into BIDS. This has now been fixed and all of the features of BIDS ......

Posted On Saturday, August 16, 2008 9:51 PM | Comments (1)

VS.NET 2008 SP1 is available
The download for VS.NET 2008 SP1 is available now: http://msdn.microsoft.com/e... This should fix the issue with running BIDS for SQL Server 2008 and VS.NET 2008. I'm downloading just downloaded it - it's tiny at about 500Kb, I hope it's not one of those "web install" stubs as I am going to be disconnected for most of the day and It's going to be frustrating if the SP stops installing part way through. Update: As I suspected, the tiny file was just a web download, there is ......

Posted On Tuesday, August 12, 2008 8:03 AM | Comments (1)

SQL Server 2008 has been released, but some BI developers will need to wait...
For those of use with MSDN or Technet subscriptions, SQL Server 2008 is available for download now and you have probably heard this from multiple sources already. (is there an echo in here?) But for anyone that wants to work with the BI tools and who already has VS.NET 2008 installed you will need to wait until VS.NET 2008 SP1 is released (see http://support.microsoft.co... which will hopefully only be a few days. This is because BIDS in 2008 is based on the VS.NET 2008 SP1 build. If you ......

Posted On Friday, August 8, 2008 7:08 AM | Comments (0)

SSAS: Scripting out a Process command
I had a comment recently on one of my older posts (which was about processing a cube with XMLA) asking how I scripted out the process command from SQL Server Management Studio. It is much easier to explain this with pictures than with words alone, hence this post. If you right click on an object in the object browser in management studio and click on the process option you will get a dialog box like the one below. This dialog has a "Script" button at the top, like many of the dialogs in Management ......

Posted On Sunday, July 27, 2008 9:49 PM | Comments (4)

MDX ratio of "current parent" issue
Every now and then on the SSAS MSDN forum, the issue of doing a generic "ratio to parent" calculation comes up. Unfortunately there are a number of problems with the premise of giving users a generic "ratio to parent" measure. The first is that the concept of "parent", by definition, requires a hierarchy and SSAS 2005 supports multiple hierarchies. Consider this query: SELECT Non Empty [Product].[Product].[Produc... on Rows, {Measures.[Sales Ratio]} ON Columns FROM [Adventure Works] I am using ......

Posted On Saturday, July 26, 2008 10:18 AM | Comments (6)

SSAS: Reporting on Metadata
I had a comment recently on one of my older posts that I did about XMLA which asked about listing cubes in a database and whether the cube is processed and it's size. I have posted about a number of different ways of doing this over the last few years, but I thought I would use this opportunity to consolidate a list all the various ways of doing this that I could think of. Under the covers all these techniques are ultimately issuing some sort of XMLA discover against the SSAS database and returning ......

Posted On Tuesday, May 6, 2008 10:08 AM | Comments (4)

powerSSAS: Listing Role information
After my last post on how to list the aggregations for a given attribute using Powershell and PowerSSAS, miltruenos commented, asked if it was possible to do a similar script to show roles and allowed/denied sets, so here is just such a script. add-PSSnapin powerSSASnew-PSDrive ssas powerssas localhost $roles = gi "\Databases\Adventure Works DW\" ` | % {$_.Dimensions} ` | % {$_.DimensionPermissions} ` | % {$_.AttributePermissions} ` | add-Member ScriptProperty DimensionName {$this.Parent.Parent.Name}-... ......

Posted On Thursday, May 1, 2008 10:29 PM | Comments (3)

powerSSAS: Getting Aggregations for a particular Attribute
If you have ever manually edited aggregation designs, you may have wondered what aggregation currently exist for a particular attribute. Well the following powerSSAS script will give you just that. (you could do this with straight PowerShell, but powerSSAS just makes it slightly easier to address a given measure group) Technically this script is just one single statement, with a couple of parameters to make it more re-usable. I have broken it up in order to make more readable by using the PowerShell ......

Posted On Sunday, April 27, 2008 10:46 PM | Comments (1)

Using PowerShell to update the case of Attribute names
I had a question recently about how to quickly convert all the attributes in a given database from UPPER CASE to Title Case. Now you could certainly write a C# or VB.Net based program, but I feel that a script is the way to go for a once off requirement like this and what better scripting environment could we ask for than PowerShell. So here is a short PowerShell script that does the job. [System.Reflection.Assembly... > $null [Microsoft.AnalysisServices... ......

Posted On Monday, March 17, 2008 12:01 AM | Comments (3)

Analysis Services Team Update posting on the MSDN forum
I just spotted an interesting post on the Analysis Services forum. It was titled "Analysis Services Team Update" and was posted by Ariel Netz, Group Program Manager for Analysis Services. He starts off with .. "I could never understand people’s fascination with blogs." ...which just begged to be blogged about. :) In it he talks about how Analysis Services is looking for the SQL Server 2008 "Katmai" release "In all honesty, things are looking good. In fact, looking very good (relative to where we ......

Posted On Wednesday, March 12, 2008 9:03 PM | Comments (0)

SSAS 2008 - small DMV improvement
I was just checking the DMVs in SSAS 2008 CTP6 to see if they had changed between CPT5 & 6. Particularly in regard to the list of limitations that Vidas posted. As far as I can tell the only thing that appears to have changed is that "SELECT DISTINCT" now appears to work. Technorati Tags: Analysis Services, DMV ......

Posted On Friday, February 22, 2008 9:21 AM | Comments (0)

Building a better DMV
Following along on my recent theme of exploring the metadata rowsets and the new Dynamic Management Views (DMV) in SSAS 2008. I have added a new DMV function to the Analysis Services Stored Procedure project (www.codeplex.com/ASStoredP... which is included in the v1.2 release that I put out just before Christmas. This all started off when I decided to look at extending the existing Discover() function to provide for sorting and filtering. And I originally started off by building a wrapper ......

Posted On Wednesday, January 30, 2008 10:45 PM | Comments (3)

SSAS: Acquiring Locks using XML/A from SSMS - Part 2
Mosha commented on my last post on this topic that there was another simpler way of doing the same thing from SSMS. And that is to execute the following commands from an MDX window. First run ...<BeginTransaction xmlns="http://schemas.micro... /> and then...<Lock xmlns="http://schemas.micro... <ID>496CEC1F-D66A-4C8... <Object> <DatabaseID>Adventure Works DW</DatabaseID> ......

Posted On Thursday, January 24, 2008 4:07 AM | Comments (0)

SSAS: Acquiring Locks using XML/A from SSMS
I put the following code sample together in response to this question on the Analysis Services forum. If you read Books Online, you might think that running the following statement in SSMS would work : <Lock xmlns="http://schemas.micro... <ID>496CEC1F-D66A-4C8... <Object> <DatabaseID>Adventure Works DW</DatabaseID> </Object> <Mode>CommitShared<... But it will throw the following ......

Posted On Sunday, January 13, 2008 11:28 PM | Comments (4)

powerSSAS: DMV Equivalents
I was reading Vidas Matelis' recent post on the metadata rowsets in SSAS 2008 and this got me to thinking about a discussion that Chris Webb and I had recently about the new "DMV" functionality is SSAS 2008. And that it basically that they are simply gives you an easier way to access information that is available through XMLA discover commands. And that you could already get to this information relatively easily through the Discover() function in the Analysis Services Stored Procedure (ASSP) project. ......

Posted On Monday, December 10, 2007 10:37 PM | Comments (2)

SSAS 2008: New Cube Aggregations tab
The November CTP of SQL Server 2008 was released on the connect MSDN Downloads site at the end of last week and when you open up a cube in BIDS you will see a new tab for aggregations. I figured I might give you a bit a walk through what you can expect from this new tab. What this tab does is to let you see all the aggregation designs that relate to a given measure group. It also lets you manage which partitions are using a particular aggregation design. As with the other Analysis Services designers, ......

Posted On Sunday, November 18, 2007 7:18 PM | Comments (3)

Australian PerformancePoint 2007 launch event / Microsoft Business Intelligence Summit 2007
Nick Barclay posted last week about the Australian launch events for Performance Point. These are free events so follow the links and register if you are in Melbourne (Nov 28) or Sydney (Nov 29). However these events are a bit broader (see the agenda) than just a PerformancePoint launch, if you go to the registration site, you will notice that the event is actually titled as "Microsoft Business Intelligence Summit 2007" - it's interesting to have to hear about something like this from Nick's blog ......

Posted On Wednesday, November 14, 2007 9:02 AM | Comments (0)

SSAS: Adding XMLA templates to SSMS
I use the Templates feature in SSMS a fair bit when I am working with XML/A, but I sometimes wonder how many people are aware that it exists. On my copy of SSMS, it is docked on the right hand side. If you cannot see the Template explorer at all, you can access it through the View menu. Have a look at the following list of templates that are available for MDX and XMLA for Analysis Services. This feature is really great for XMLA queries. I don't really know of anyone that sits down and writes XMLA ......

Posted On Monday, September 10, 2007 12:25 AM | Comments (1)

SSAS: Query Performance Tuning Whitepaper
I suppose by now most of you have seen all the other posts about the Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services whitepaper that was just released. I saw them all too and I was not planning to do another yet another "read this cool whitepaper" post. But, even thoufh I have only just started reading through it, I do have a couple of points I want to make. 1) The first is a shameless plug: BIDSHelper www.codeplex.com/bidshelper gets a mention on ......

Posted On Thursday, August 30, 2007 1:06 PM | Comments (0)

SSAS 2008: MDX changes in Katmai
Wow things have been moving fast this morning. First Mosha posts about some new features in SSAS 2008, that have been available for 3 months, but no one (to the best of my knowledge) has known about. No sooner do I complain to Mosha that none of this stuff was listed in any of the "What's New" pages in the CTP, that he comes out with this post pointing to the documentation for new features that are in the current CTP. (The old versions of the "What's New" pages do not seem to be available anymore, ......

Posted On Monday, August 27, 2007 1:26 PM | Comments (0)

SSAS: AMO Powershell Provider
Following up from my recent post on Powershell, I have updated the Powershell provider for Analysis Services that I wrote back when Powershell was still known by its code name of "Monad", so that it works with the released version of Powershell. I have a few more features that I would like to add to it, but it is quite functional in its current state and I am thinking of putting it up on codeplex in its current form. My main issue now is what to call the thing. The project is currently called AmoPsProvider ......

Posted On Sunday, August 26, 2007 10:20 PM | Comments (8)

SSAS: Manipulating Cube Partitions with PowerShell
Jesse Orosz posted some interesting sample code recently on how to remove partitions with a certain prefix using AMO. I thought it would be an interesting comparison to see what similar code would look like in PowerShell. There is definitely not anything wrong with Jesse's code, it's obviously part of a much larger framework. It's more that I think PowerShell is a really good tool for doing these sort of admin tasks and it's an interesting exercise to take someone else's code from .net and re-implement ......

Posted On Sunday, August 26, 2007 10:13 PM | Comments (2)

Explaining: BottomCount, Filter and Null in SSAS2005 and ProClarity
Thomas was recently explaining some issues he had with the BottomCount() function in MDX. It was difficult to answer this in a comment on the post, so I thought I would see if I could address some of his issues here. One common business analytical problem is to find customers with low sales. In MDX we have the BottomCount function for that but compared to the TopCount function, that return the best customers, BottomCount can return less clear results. If you run this MDX Select in management studio, ......

Posted On Wednesday, August 15, 2007 11:57 PM | Comments (1)

SSAS 2008: Data Management Views
[Via Vidas Matelis] Vidas recently did a post about a couple of new features that will be available in SSAS 2008 (but that are not in the July CTP). One of which was DMVs (Data Management Views. These allow you to do things like the following: SELECT * FROM $system.discover_connections SELECT * FROM $system.discover_sessions SELECT connection_id , connection_user_name , connection_host_application , connection_start_timeFROM $system.discover_connections It appears that these DMV's might be providing ......

Posted On Monday, August 13, 2007 10:54 AM | Comments (3)

1 of 71
[via Rob Farley] I just found out that from this post, as of 27 June 2007, Rob and I were 2 of only 71 MCITP:BI certified people in the world. It's a bit of a tough exam as it covers a lot of technologies, SSRS, SSIS and SSAS (OLAP and Data Mining) which could almost be 4 exams in themselves.This means that I know more that 7% of the current MCITP:BI certified people in the world. Now I know I'm not that special - I don't think anyone really knows all these 4 areas equally, If you think you really ......

Posted On Friday, August 10, 2007 11:57 AM | Comments (4)

SSAS: AMO gems from the MSDN Forum - Measures.Remove()
The second gem from the Analysis Services MSDN forum is also by Adrian Dumitrascu and deals with what the second "CleanUp" argument of the Measures.Remove() method does. MeasuresCollection.Remove() Method The 'cleanUp' boolean argument specifies if the dependent objects will be removed. For a Measure, the dependents include the PerspectiveMeasure objects. For a DimensionAttribute, the dependents include the CubeAttribute objects. If you do not specify the 'cleanUp' parameter, the dependents are removed. ......

Posted On Wednesday, July 18, 2007 11:32 PM | Comments (0)

SSAS: AMO gems from the MSDN forum - MajorObject.Update()
Here is another gem from the Analysis Services MSDN forum by Adrian Dumitrascu from the product team. The one from this thread deals with what exactly the .Update() method does. And was in the context of talking about creating new roles in a database. MajorObject.Update() Method Even if you create the role as new, it's still not needed to call database.Update(). Calling role.Update() is enough. The .Update() method only saves the minor properties and collections (thus not the major children) of an ......

Posted On Wednesday, July 18, 2007 7:55 AM | Comments (0)

SSAS: Detecting the version of SSAS on a given server
To detect the version of AS, use ADOMD and connect using a connection string like "Provider=MSOLAP;Data Source=<server>;" where <server> is the name of your server. If you have the 2000 and 2005 providers on your system, the 2005 provider will attempt to connect to first and will then fall through to using the 2000 (v8) provider if the server is an AS2000 server. Once you have an open ADOMD connection you can check the value of the ServerVersion property of the connection. A value with ......

Posted On Saturday, July 14, 2007 8:08 PM | Comments (1)

SQL Server 2008 - release date announced
[via SQL Server Central] Apparently at it's recent Worldwide Partner Conference, Microsoft announced that SQL Server 2008, Visual Studio 2008 and Windows Server 2008, will all be released on Feb 27, 2008. Cool :) In the current CTP, the BI Development Studio is still using the VS.NET 2005 shell,given the release dates, it will be interesting to see if it moves to the VS.NET 2008 shell ......

Posted On Saturday, July 14, 2007 6:20 PM | Comments (1)

SSAS: Exporting MDX from Profiler part 2
Greg Galloway commented on the previous post I made about exporting captured MDX queries from SQL Profiler, indicating that there was an issue with using the MDX cap Greg has posted an issue on connect which you can vote on here, but I can't see this behaviour changing in the near future. Maybe in Katmai they could make the extract smart enough to do the parameter replacement as it creates the .mdx file. As far as I am aware the Analysis Services provider in SQL Server Reporting Services (SSRS) 2005 ......

Posted On Wednesday, July 4, 2007 7:57 AM | Comments (5)

Re-Awarded for 2007!
I'm back! I just got an email from the Microsoft MVP program this morning letting me know that I have been re-awarded as an MVP for 2007. For those of you that may not be aware, the MVP award is given for a 12 month period, based on your contributions to the community for the last 12 months. I've been doing a few different things this year compared to the year before and the MVP program itself has been consuming some of my time, so I have to admit to being a little anxious coming up to my first renewal. ......

Posted On Monday, July 2, 2007 7:49 AM | Comments (4)

SSAS: Exporting MDX and XMLA from SQL Server Profiler
Hey, why didn't anyone tell me about this one? :) I was not looking for this, I was looking for someway to easily extract event durations without having to get my hands dirty with angle brackets (XML) and I stumbled upon the menu below. It appears that you can easily extract "MDX Events" or "All Queries" to an .mdx file. Notice that there is also one for Extracting DDL events, so presumably you could extract the XMLA for a set of operations on the server or database so that you could wrap them up ......

Posted On Monday, July 2, 2007 7:17 AM | Comments (1)

SSAS: Analysis Services Stored Procedure Project (ASSP) 1.1 release is out!
It's been a little while since we did the initial release of ASSP, but things have been bubbling along quietly in the background and today we pushed out another release which in addition to a few minor fixes includes the following new features. ListFunctions - This is cool on a couple of fronts (and not just because it is one of mine :) ) Firstly it is practical because it will list all the available functions in all the .Net assemblies that have been registered either at the server or, if you pass ......

Posted On Sunday, June 24, 2007 10:32 PM | Comments (1)

Presentation done, and it seemed to go OK
I just finished my presentation to the user group. I think it went OK, I have trouble with my brain getting ahead of my mouth. I tried to demo one concept too early and tried one things which I had not practiced and it really did not demonstrate what I thought it would at the time. So the lesson here is - practice your demonstrations and stick to them, don't do ad-hoc changes to your demo lightly. As an experienced presenter told me - "you are best to keep your demo's simple and explain the heck ......

Posted On Tuesday, June 19, 2007 11:47 PM | Comments (0)

I'm an MVP!
Woo-Hoo! I got an email from Microsoft yesterday afternoon telling me that I am “a recipient of this year’s Most Valuable Professional (MVP) award” A special thanks goes to Edward and Greg and Marco, but there are may others that have helped along the way. I also want to thank all the little people (the smurfs, leprechauns and Oompa-loompas) :) ......

Posted On Monday, July 3, 2006 9:04 PM | Comments (6)

Melbourne SQL Server User Group - Many-to-Many Revolution
I am speaking tomorrow night at the Melbourne SQL Server User Group. The presentation will be based on the excellent white paper that Marco Russo produced about Many-to-Many relationships in SSAS 2005 called the Many-to-Many Revolution. Here is the session abstract: The Many-to-Many Revolution ---------------------------... Do you have a situation with something like categories that have one or more customers and customers that fit into more than one of these categories? ......

Posted On Monday, June 18, 2007 10:28 AM | Comments (0)

BIDSHelper: DeployMDXScript feature enhanced
Have you ever renamed a deployed database in SSMS and then tried to deploy changes using BIDS? I don't imagine that it is too common an occurrence as it does not work! I renamed the "Adventure Works DW" sample to "Adventure Works DW1" in SSMS and then told BIDS to deploy to the "Adventure Works DW1" database and got an error that the "Adventure Works DW1" database already exists - of course it does. If you capture the XML/A that BIDS is sending you will notice something strange. The ID and the Name ......

Posted On Wednesday, June 6, 2007 11:24 PM | Comments (1)

Analysis Services 2000 DSO Scripts
I have a number of DSO scripts that I developed a couple of years ago when I was working on a very large Analysis Services 2000 project. I was recently catching up on the OLAP newsgroup and noticed that there was someone asking about DSO scripts for copying aggregations. I had one such beast in my tool box and decided to post it on my blog. As I get time I will go through and pull out some of the other more generic scripts and put them up here too. Copying Aggregations Script List Source Tables (added ......

Posted On Thursday, September 8, 2005 7:30 AM | Comments (5)

MDX Tuning Webcast
I just can across Chris Webb's BI Blog and it has some good stuff on it in relation to Analysis Services. One of the posts that caught my eye was this Microsoft Webcast on MDX Tuning, I have just downloaded the offline version, this is definitely something I am going to watch ......

Posted On Friday, June 24, 2005 2:43 PM | Comments (0)

SSAS: Sending XMLA commands using AdomdClient
There was a question on the forums a little while ago about what were the minimal libraries that would need to be installed in order to write a program that would allow someone to process a cube. Well it can be done with just the ADOMD.NET client libraries, which the user will need on their machine anyway if they are going to browse the cubes. Below is about the shortest VB.NET "application" that you would need to process a cube. It's a console application and all you need to do is to add a reference ......

Posted On Tuesday, June 5, 2007 7:06 AM | Comments (5)

SSAS: Some new BI Bloggers
Greg Galloway who I have known for a while now from his excellent contributions to BIDSHelper and the AS Stored Procedure project has recently started blogging. And he has a couple of really good posts, including one on doing ProcessAdd with a large dimension. Adrian Downes started blogging earlier this year with his blog: Establish. Execute. Evolve. In his initial post, Adrian stated that his blog would cover more of the "meta-stuff" which is great and he already has a number of good posts like ......

Posted On Monday, May 7, 2007 7:36 AM | Comments (0)

SSAS: ProcessUpdate - Too much of a good thing
I came across something interesting on the Analysis Services MSDN Forum a little while ago - this post has been on my todo list for a while :). When processing a large dimension (10 million+ members) the following error was encountered. File system error: A FileStore error from WriteFile occurred. Physical file: .... At first it sounded like some sort of data corruption which was what someone else suggested and the proposed resolution of re-deploying and fully re-processing would have corrected the ......

Posted On Sunday, April 29, 2007 9:59 PM | Comments (2)

SQL Server Katmai public CTP released!
I got the following email from fellow MVP Greg Linwood on behalf of the Melbourne SQL Server User Group this morning which was taken from a posting by Ed Lehman from Microsoft in the private MVP newsgroup. I have not read any other feeds yet, but I imagine this news will be all over the web today. There will probably be a flood of information on Katmai, now that a release is publicly available. Enjoy! E D L E H M A N'S P O S T I N G This coming Monday, June 4, we will release the June CTP of Katmai. ......

Posted On Tuesday, June 5, 2007 7:00 AM | Comments (0)

What "lights up" in Excel 2007 with SSAS SP2?
I'm sure most of you that read my blog have probably heard that there are some features in Excel 2007 pivot tables that only 'light up" when connected to an SSAS server with SP2 installed. However actually finding a list of those features has not been as easy as you would have thought. My friend Deepak asked a number of people about this at the MVP Summit back in March and while everyone was sure that there was a list somewhere, no one seemed to be able to put there hands on it at the time. Well, ......

Posted On Thursday, May 10, 2007 10:25 AM | Comments (0)

SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"
Here is something interesting that came up a newsgroup thread a little while ago, that I though merited some more explanation. Have you ever seen "the operation has been cancelled" errors from a long running query (one that takes more than 30 seconds) while a cube or partition is being processed? What you may be seeing is a "by design" behaviour, where a pending commit lock will cancel long running queries that are blocking it from completing. There is a property that can be set at both the server ......

Posted On Tuesday, April 24, 2007 8:07 AM | Comments (11)

SSAS: .Net Stored Procedure - Best Practices
If you are into writing stored procedures for SSAS, then you owe it to yourself to checkout Mosha's latest post on Best Practices for ADOMD.NET stored procedures, it covers some really interesting stuff including things like preferring enumerators over iterators which can have a significant impact on performance ......

Posted On Friday, April 20, 2007 3:38 PM | Comments (2)

Interview with ZD Net
The interview with ZD Net that I mentioned a couple of weeks ago was published today. You can see the full article here http://www.zdnet.com.au/mic... It will be interesting to see what questions I get as the interview was based around the topic of "Business Intelligence for the CIO" - so I don't expect to get any trick MDX questions :) ......

Posted On Monday, March 26, 2007 10:22 PM | Comments (0)

SSAS: Parsing Calculated Measures from MDX Script
There was a question recently on the Analysis Services forum on how to enumerate calculated measures from AMO here. Unfortunately you can't do this from AMO. The best you can do is to get the MDX script and then parse it. If you need to do this the following Regular Expression should come in handy. (\bCREATE\s*MEMBER\s*.*\[?m... |(?:\bCREATE\s*(\[(?<Cal... I have wrapped up this regex ......

Posted On Thursday, August 10, 2006 9:10 PM | Comments (1)

SSAS: Getting the xmlaWarningCollection from a Process operation
There was a question recently on the Analysis Services forum asking how to get a list of the Warnings from a Process() method in AMO. There is an overload to the Process methods which includes an xmlaWarning collection, so this should be a pretty easy thing to do - right? Wrong! It's not as easy as it first appears. After inserting an invalid record into the fact table of a test cube, I was able to process the cube using the Business Intelligence Development Studio (BIDS) and see the relevant warnings, ......

Posted On Sunday, March 4, 2007 7:39 PM | Comments (0)

SQL 2005 SP2 - Make sure to read the readme!
Before you install SP2 you really should read the readme file. I have already seen one post on the MSDN forum who ran into the issue documented in section 5.3.2 of the readme. You can find the ReadMe file here http://download.microsoft.c... And the What's new file here http://download.microsoft.c... There are a lot of good things in SP2, ......

Posted On Thursday, February 22, 2007 8:51 PM | Comments (10)

The OLAP Report has updated it's Market Share Analysis
Nigel Pendse from The OLAP REport www.olapreport.com updated his Market share analysis recently http://www.olapreport.com/m... There was an impressive "surge" in market share for MS OLAP (from 27.9% to 31.6%), especially when you consider that Applix (+0.3%) and Microstrategy (+0.1%) were the only other vendors to record increases in market share. Highlights The market grew by 16.4 percent, faster than the 12 percent we had predicted Microsoft’s growth accelerated again, thanks to the full-year ......

Posted On Thursday, February 22, 2007 6:12 AM | Comments (0)

Happy 10th Birthday to the SSAS team
I was over in Redmond last week and took the opportunity to catch up with Edward and Mosha. It turns out that on Thursday last week the SSAS team were having a bit of a lunch to celebrate their 10 year anniversary. According to Mosha the real date was a couple of months ago, which could lead to all sorts of jokes about software companies and slipped release dates, but I'm not going there :) Congratulations to the team - I for one am looking forward to more great things from you in future ......

Posted On Tuesday, February 6, 2007 1:35 PM | Comments (0)

SSAS IA64 Server Fun: Using VBA functions in MDX
Occasionally I have the pleasure of working with Analysis Services 2005 on an IA64 (64 bit Intel Itanium) servers and on the whole, most things work the same. But there is the occasional small difference that can cause some frustration. Background: In Analysis Services 2000, you could not use VBA functions in MDX calculations as the VBA library was COM based and had not been ported to the 64 bit platform. In SSAS 2005, the core VBA functions have been implemented in a .Net library which will run ......

Posted On Sunday, October 29, 2006 12:09 AM | Comments (5)

XAML pivot table control
[via Mosha] Mosha posted today about a sample XAML Pivot table control. http://sqljunkies.com/WebLo... I think I'll have to download a copy and have a play. :) I have not really done any hands on work with XAML yet and this sounds like something that could be quite useful. According to Mosha the MDX generation needs to be cleaned up. I would even like to see something that could round trip the MDX (or at least spit out the MDX after the pivot table has been setup the way you ......

Posted On Tuesday, October 10, 2006 8:48 PM | Comments (0)

SSAS: New white paper on Many-to-Many relationships
Marco over at sqlbi.eu has just announced that he has released a great new white paper on some of the many possible uses for Many to Many relationships within SSAS cubes. Marco has put a great deal of effort into this paper and it is well worth reading. The paper (plus demo code) is downloadable from SQLBI.EU starting from this page: http://www.sqlbi.eu/manytom... ......

Posted On Monday, September 25, 2006 10:53 PM | Comments (2)

SSAS: Running mulitple MDX Queries
This tip came up during a discussion amongst the Analysis Services Stored Procedure team and Chris Webb just posted about it too. Apparently you can use the GO keyword to separate MDX batches in SSMS, in much the same way that you can with T-SQL batches eg. SELECT MEASURES.MEMBERS ON 0 FROM[Adventure Works] GO SELECT [Measures].[Internet Sales Amount] ON 0,[Date].[Day of Week].MEMBERS ON 1FROM [Adventure Works] This ability is built in to SSMS, “GO” is not a valid MDX keyword. It is SSMS ......

Posted On Monday, August 14, 2006 8:20 PM | Comments (2)

Announcing the Analysis Services Stored Procedure Project
Back in April, Chris Webb posted an entry on Analysis Services stored procedures asking for volunteers to joining him in creating a few useful examples that people could download. I was one of those volunteers and after several months of work behind the scenes, we are at a point where we are happy to release beta 1 on CodePlex here: http://www.codeplex.com/Wik... Have a read of the CodePlex site - it's reasonably well documented, and you can download the source ......

Posted On Monday, August 14, 2006 7:54 PM | Comments (0)

SSAS: Using XMLA to get a list of Databases and Cubes.
There are not a lot of example XMLA queries available, so I thought I might work through some examples on my blog. This first example was prompted be a question in the Olap newsgroup and demonstrates how to get a list of databases and cubes. Getting a list of the databases from Analysis Services is fairly easy with a simple XMLA query like the following: <Discover xmlns="urn:schemas-microsof... <RequestType>DBSCHEMA... <Restrictions /> ......

Posted On Sunday, August 6, 2006 10:16 PM | Comments (38)

Product of values in MDX
There was an interesting question on the Analysis Services newsgroup tonight which involved a requirement to produce a product of a set of values. ie. value1 * value2 * value3 * .... valueN You could do something like this with a unary operator, but that would change the aggregation for all measures. In order to do this for just a single measure I proposed exploiting a technique I saw Itzik Ben-Gan use in T-SQL. The basic mathematical proof for this technique was the following: logN (val1*val2*...*valn) ......

Posted On Tuesday, July 18, 2006 9:30 PM | Comments (3)

Some new blogs
I have come across a couple of interesting new blogs in the last few days. Chris Baldwin - Program Manager for Reporting ServicesHas an interesting first post on reducing the amount of merged columns when exporting a SSRS report to Excel Elizabeith Vitt - from SQL SkillHas 2 good posts up, one about how attribute relationships affect aggregation designs. This is well worth reading, I think this is an area that is going to trip up a lot of people. I don't think there is a lot of understanding out ......

Posted On Monday, July 17, 2006 8:57 PM | Comments (0)

Getting a Cell from Analysis Services 2005 using PowerShell
I noticed that the scripts library at http://scripts.readify.net/ had an example on returning results from a SQL table. Not to be out done by the relational guys, I thought I would post an example of how to do the same thing against an Analysis Services 2005 cube. I can see that this could have some value in a production environment in that it could be used to automate the validation that a given tuples in the cube matched the results from a source system.function get-cell { param([string] $server ......

Posted On Wednesday, July 12, 2006 8:37 PM | Comments (0)

Running XMLA Queries the Powershell way
I have been experimenting a bit more recently to see what I can do with Powershell and Analysis Services. The following small script executes an MDX query using an XMLA connection. I have borrowed the xsl files from one of Chris Harrington's excellent ThinOlap samples. 1 [System.Reflection.Assembly... 2 [Microsoft.AnalysisServices... = new-Object Microsoft.AnalysisServices.... 3 $xmlac.Connect("localhost\s... ......

Posted On Wednesday, June 14, 2006 8:25 PM | Comments (8)

DSO Script: Adding a Role to a Cube
I had a question over the weekend about how to add a role to a cube using DSO. As more and more people start using AS2k5 I think the interest is going to shift more towards AMO scripting, but someone else may find the following helpful. The VBScript below shows the basic technique. The key thing to remember is that the Role object is set up at the database level. The cube's role collection basically holds references back to the database role collection (not that you can really tell this from the ......

Posted On Tuesday, June 13, 2006 8:26 PM | Comments (1)

Edward Melomed is Blogging
[via Mosha] Edward is Program Manager for Analysis Services and is very active on the Analysis Services forum and he has just started a blog at http://www.sqljunkies.com/W... He has a great first post on connectivity problems which is a recurring theme in the forum and newsgroup. Welcome to blogging Edward ......

Posted On Monday, May 29, 2006 9:10 PM | Comments (2)

Sending XMLA to Analysis Services the easy way
I love Chris Harrington's Thin Olap site http://www.activeinterface.... which has a stack of examples on sending XMLA using VBScript. However I recently came across an easier way to send XMLA to Analysis Services using .Net. There is an assembly called Microsoft.AnalysisServices.... that is distributed with Analysis Services 2005, it is installed in GAC on the server or can be found in “C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies... by default. If you add ......

Posted On Thursday, May 25, 2006 9:03 PM | Comments (8)

MDX Browser/DOM sample application
I just noticed that Eugene Asahara has posted some sample code at the end of a relatively long post here: http://blogs.msdn.com/eugenea/archive/2006/05/21/603418.aspx. It sounds pretty interesting, I have already downloaded it, I now just need to find a few more hours in the day to have a look at it. :) 

Posted On Tuesday, May 23, 2006 9:35 PM | Comments (1)

Don't use ROUND() in a Reporting Services Model against a UDM
I recently had an interchange with someone on the Olap newsgroup who was having trouble with a Report Builder model based on their Analysis Services UDM. Just as a bit of background, Marco Russo has an excellent post here http://sqljunkies.com/WebLo... on how to build a report model based on an Analysis Services UDM. This is not exactly what I would call an intuative operation, but I am assuming that the function was put in SSMS rather than BIDS as there is no real ......

Posted On Sunday, May 14, 2006 9:55 PM | Comments (0)

Microsoft buys Proclarity
Mosha has done it, as has Chris and Marco, so even though I should join in too. There is an official press release here http://www.microsoft.com/pr... Proclarity has always been highly regarded and I think it will round out Microsoft's BI stable very nicely. It was interesting to read Chris's post and some of the comments on people have posted under Mosha's entry. Ok, so I'm a little slow this time, but I don't want to be the only one not to blog about ......

Posted On Wednesday, April 5, 2006 9:28 PM | Comments (1)

Upgraded Foodmart 2000 sample database for Analysis Services 2005
Soon after installing Analysis Services 2005 I upgraded the Foodmart 2000 sample database from Analysis Services 2000. Foodmart is kind of the equivalent of the pubs database in the SQL Server world. Sample queries and questions on newsgroups are often posted using Foodmart. Someone else in the newsgroups asked if anyone had an upgraded copy of this database as they had Mosha's “Fast track to MDX” book and all the samples are against Foodmart 2000. I did a backup of it both with and without data ......

Posted On Sunday, April 2, 2006 4:38 PM | Comments (17)

A PowerShell provider for Analysis Management Objects
The more I play with PowerShell the more I like it. And now that I have a provider working that allows me to link in an Analysis Services server and navigate through the object hierarchy. Here is an early picture of the Shell (developed while PowerShell was still know by its code name of Monad) The screenshot above shows a PowerShell console that is connected to a drive called “amo“ which is linked to the SQL05 instance of Analysis Services 2005 on my localhost. One of the biggest differences ......

Posted On Sunday, May 7, 2006 8:14 PM | Comments (1)

When is an Empty cell Non Empty and a Non Empty Cell Empty?
I came across an interesting isssue on the microsoft.public.sqlserver.... newsgroup the other day. A calculated member, that calculates with an amount was being excluded from the results when the NON EMPTY clause was added to the axis. At first I thought this could have been related to the solve order issue that Chris Webb discussed at http://spaces.msn.com/cwebb... but the only calculated member involved here was the one created in the query scope, so that could ......

Posted On Monday, April 17, 2006 8:50 PM | Comments (2)

Meet Monad - your new best friend
I have been playing with Monad (also known as MSH) for a couple of weeks now... and I like it! It is the new scripting engine from Microsoft that is being developed as part of WinFx. I started hearing whispers about Monad a few weeks ago, just before Scott Hanselman did his podcast on the subject. This stuff looks really cool, you can seamlessly get access WMI, COM and .Net components. There appear to be Monad resources popping up all over the web. The guys at Readify have started a script library ......

Posted On Wednesday, April 12, 2006 9:40 PM | Comments (0)

Processing an AS2005 cube from the command line with XMLA
There was a question recently on the Analysis Services newsgroup asking how to process an Analysis Services 2005 cube using XMLA from the command line. Below is a script based on Chris Harrington's excellent Thin Olap blog. This is the absolute minimum script required to get an Analysis Services database to process (ie. No error handling <g>). On my machine I processed my “Sandpit“ database (the sandpit is where I play) with the following command cscript xmlaProcess.vbs Sandpit ......

Posted On Tuesday, April 11, 2006 9:07 PM | Comments (3)

Proactive Caching - SQL Server Notification Permissions
I did a presentation at the Melbourne SQL Server User Group this week on the proactive caching feature in Analysis Services 2005. One of the topics that I covered was the different notification methods that Analysis Services 2005 can make use of in order to discover that something has changed in the underlying relational source. The first notification method on the list is SQL Server notifications, but you will notice that as soon as you select this option a warning message appears at the bottom ......

Posted On Sunday, March 26, 2006 7:18 PM | Comments (22)

Chris Webb launches Crossjoin Consulting

[via Chris Webb]

Chris's blog is a must read for anyone in the Microsoft BI space and last week he announced that he is starting up his own consulting company - Crossjoin Consulting (www.crossjoin.co.uk). What is better is that he is planning to build time into his schedule to do blogging. So good luck Chris and keep the posts coming!

Posted On Monday, March 20, 2006 7:18 PM | Comments (1)

SQL Server 2005 SP1 CTP released
This announcement has been doing the rounds of the blogs today, so why not put a note up on mine. :) Microsoft has released a Community Technology Preview of SP1 for SQL Server 2005. There is a list of some of the issues that have been addressed in the service pack here: http://support.microsoft.co... . Although this list really only covers issues for which hotfixes were released. There is no mention of the OPENQUERY bug that I blogged about here, but there is a standard cover all statement ......

Posted On Monday, March 20, 2006 7:12 PM | Comments (2)

Speaking at Melbourne SQL Server User Group

I'm speaking this Tuesday at the Melbourne SQL Server User Group on the new real-time BI features in Analysis Services 2005.

Details of the event can be found at http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=154

I'm currently going over (& over) my presentation and triple checking my demos. :)

Posted On Sunday, March 19, 2006 7:36 PM | Comments (1)

XMLTools on GotDotNet
[via Scott Hanselman] I really like Scott's blog and am a keen listener of his podcast. He had an interesting link recently to a tool from the XMLTools page on gotdotnet. It was to a tool called Microsoft XML Diff and Patch. What really interested me about this tool was the potential application to the SQL Server 2005 BI applications. Now, the connection here may not be immediately obvious, but XML is used all through the MS BI stack, and not just in .config files. Below is a short list of the main ......

Posted On Monday, February 27, 2006 8:09 PM | Comments (0)

What is the KPI Browser doing?
Have you ever run the KPI Browser and wondered how it produces its summary of KPI's. I had someone ask me about this today so I fired up SQL Profiler and had a look at the queries that were fired off while the KPI Browser tab was populating. What I found was that a number of XML/A Discover commands were executed, the Key one appears to be the one against MDSCHEMA_KPIS Here is the query I captured against the Adventure Works Database: <Discover xmlns="urn:schemas-microsof... ......

Posted On Tuesday, February 21, 2006 6:44 PM | Comments (2)

Palo - More open source OLAP goodness
I got another head's up, this time from Marco at Groeneveld Management Services, about another open source OLAP application at www.palo.net, below is the Overview from their website: Palo Overview Are you working with big Excel workbooks that are difficult to maintain because of their size? Then Palo is for you. Palo is an advanced data store for Microsoft Excel that allows you to handle large amounts of Excel data on a small number of worksheets. In addition, it also allows you to share Excel data ......

Posted On Monday, February 20, 2006 6:22 PM | Comments (13)

Connecting to an Open Source OLAP engine from a command-line XMLA client
I got an interesting email from Chris Harringtion today which I though I would share: Hey, I had a really interesting impromtu collaboration yesterday with Nickolas Goodman. The results of our experiment are posted to his blog at http://www.bayontechnologie... I ran my command-line XMLA client on my machine and made requests to his Mondrian XMLA provider. Before talking with Nick I had no idea that there was an open source ROLAP PTS and XMLA provider. ......

Posted On Sunday, February 19, 2006 7:38 PM | Comments (1)

Creating an Analysis Services 2005 Solution from an existing Database
If you migrate an Analysis Services 2000 database to Analysis Services 2005 or if you have a database that has been edited directly you may find yourself without an upto date solution file. This is only really an issue when you want to make changes to the database and test them in a separate environment. Even then it is not much of an issue as you can easily create an XMLA script of the database and deploy it onto another server or instance. But BI Development Studio (BIDS) solutions do have a couple ......

Posted On Monday, February 13, 2006 6:48 PM | Comments (0)

Linked Servers and Stand alone Analysis Services 2000 Servers
I had an interesting experience this afternoon which I thought I would share in the hopes that it may save someone else the pain I went through. Today I moved a production Analysis Services server off a machine which was running both SQL Server and Analysis Services onto it's own machine for one of my clients. The repository was in SQL Server, so after installing AS we simply copied the data files off the old server, changed the repository connection string and we had our new server up and running ......

Posted On Tuesday, January 31, 2006 7:44 PM | Comments (0)

ascmd tool beta
[via Chris Webb] I've just noticed Dave Wickert's post on the following thread on the Analysis Services MSDN forum, and thought I'd flag it up: http://forums.microsoft.com... If you've ever wanted to be able to execute MDX or XMLA from the command line then you might want to sign up to be a beta tester for the tool he's co-developing. Here's a summary from the original post: With it you can execute either an XMLA script or an MDX query. Input and output can ......

Posted On Wednesday, January 25, 2006 6:59 PM | Comments (0)

SQL Server 2005 OPENQUERY bug with linked Analysis Services server
It appears that there is a bug in the RTM release of SQL Server when dealing with a linked server to an Analysis Services server. When you issue a query that has a “WITH MEMBER“ or “WITH SET“ clause like the following simple query: USE master GO /* Add new linked server */EXEC sp_addlinkedserver @server='LINKED_OLAP', -- local SQL name given to the linked server@srvproduct='', -- not used @provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version) @datasrc='localhost', ......

Posted On Saturday, January 14, 2006 6:39 PM | Comments (4)

New Articles from Mosha
[via Mosha Pasumansky] Mosha has recently released a couple of excellent posts on hist blog: Inside OLAP Engine: Cache Prefetching This is an extract from a whitepaper that Mosha is working on and he is asking for feedback on the technical level of the content. I thought it was great, I can't wait for the whole whitepaper to come out. Default members, MDX Scripts, Security, KPIs and Perspectives Another great post which gives some great insight on why some things work the way they do. It is knowing ......

Posted On Tuesday, January 10, 2006 6:45 PM | Comments (0)

SQL2005 BI Metadata Samples Toolkit
[via Chris Webb] I've just found the 'SQL2005 BI Metadata Samples Toolkit', downloadable from here:http://www.microsoft.c... I have added this to my collection of downloaded tools :) It sounds pretty interesting and as Chris says, could be really helpful on a large project that has a team of developers working on it. I will be interested to see if it will applications for consultants like me when we get called ......

Posted On Tuesday, December 20, 2005 6:18 PM | Comments (3)

An Excel Workbook that keeps the filters for all pivot tables synchronised
I have been answering a couple of questions on the microsoft.public.sqlserver.... newsgroup about code for keeping the page filters for multiple pivot tables in synch in the one Excel workbook. One person had 2 pivot tables on the one sheet and another had 2 sheets with a pivot table on each one. I was starting to get mixed up between the two threads so I thought I would just build a solution that would work across all pivot tables across all sheets in the workbook. The workbook I created contains ......

Posted On Wednesday, December 14, 2005 4:43 PM | Comments (32)

Writing multiselect friendly MDX calculations
[via Mosha] Writing multiselect friendly MDX calculations Only a couple of weeks ago I was telling someone on the newsgroup that you cannot write multiselect friendly calculations in AS 2000. Well Mosha just published the post above to show how it can be done in AS 2005 using some of the new MDX features. Well worth the read if you are into writing MDX ......

Posted On Tuesday, November 22, 2005 1:53 PM | Comments (0)

Feature Pack for Microsoft SQL Server 2005 - November 2005
Today the goodness just keeps on coming. I just read a post from Dave Wickert on the Olap newsgroup about the newly released feature pack for SQL Server 2005. It contains all the following goodies. Microsoft ADOMD.NET (x86, x64, ia64) Microsoft Core XML Services (MSXML) 6.0 Microsoft OLEDB Provider for DB2 (x86) Microsoft Operations Manager 2005 Management Pack for Microsoft SQL Server 2005 (coming soon) Microsoft SQL Server 2000 PivotTable Services (x86) Microsoft SQL Server 2000 DTS Designer Components ......

Posted On Friday, November 11, 2005 1:06 PM | Comments (0)

Recent SQL 2005 Events in Melbourne
I have been to a couple of events in the last few weeks and have been a bit slack in getting information up here about them. I went to the SQL Server User Group meeting in Melbourne and heard a talk from Itzik Ben-Gan from Solid Quality Learning about uses for row numbers and specifically how the new Row Number function in SQL 2005 makes these techniques a whole lot easier to implement. The one standout demo for me was where he showed how you can use row numbers to calculate median values, with just ......

Posted On Friday, November 11, 2005 7:07 AM | Comments (1)

Online Training for SQL 2005

Some of us on the SQLDownUnder mailing list are booked in to do some of the Beta certification exams for SQL 2005. There was a post today from one of the guys about some free online training courses at http://www.microsoft.com/technet/prodtechnol/sql/2005/learning/default.mspx which looks interesting.

Now I just need to find an extra couple of hours in my day :)

Posted On Thursday, November 3, 2005 7:42 AM | Comments (2)

SQL 2005 has gone RTM
I just heard on the SQLDownUnder mailing list that SQL 2005 has been released to manufacturing (RTM) as of 9:45am Thursay Oct 27 (Seatle time)! I have not seen this on any other blogs (yet) so I thought I would get in early! Woo Hoo!

Posted On Friday, October 28, 2005 7:40 AM | Comments (0)

New MS BI Certification
via Chris Webb Euan Garden notes that the new SQL 2005 certifications have been announced, one of which is the MCITP: Business Intelligence Developer. See here for more details: http://www.microsoft.com/le... I think I'm going to have to get myself certified in this too. I have the MCP for Datawarehousing, but that was for SQL Server 7.0 and I have not seen anything for SQL Server 2000. I notice that most of the books they have listed as resources are not due out until 2006 so it ......

Posted On Friday, October 28, 2005 7:19 AM | Comments (1)

Migrating DSO code to AMO
I had a question today about how I was going to migrate my DSO code to AMO. (I would have replied directly, but you did not leave a return email address) I know it will be painful, but I think we are up for a re-write. The general rule I have been hearing about migrating from AS2k to AS2k5 is that, while the migration wizard can take most things over to an equivalent database in AS2k, you miss out on most of the benefits of upgrading. You are better off rebuilding your OLAP database in AS2k5 to take ......

Posted On Thursday, October 27, 2005 1:05 PM | Comments (0)

Partial Alternate Hierarchies
There have been a couple of questions on the Microsoft OLAP newsgroup (microsoft.public.sqlserver... recently about making dimension members appear in more than one place in the dimension. Have a look at this article for one method of implementing this. [More] [Updated 27 Oct 2005] I have add more detail on the Analysis Services 2005 solution as it was a little bit light on regarding exactly how the dimension usage was set up ......

Posted On Monday, October 24, 2005 9:08 AM | Comments (1)

Backing up Analysis Services 2000 Databases
There are a number of different methods available for backing up your Analysis Services 2000 database. I recently pulled together this vbscript for automating the calling of msmdarch.exe and thought I would post an article on the full range of backup/restore options. Analysis Manager This is the method that most people are aware of. It is fine for migrating databases, or for restoring databases. But for archiving/backing up databases you really want something that can be automated. If your database ......

Posted On Sunday, October 2, 2005 6:44 PM | Comments (3)

MDX for Months across and years down
There was a question recently in the Olap newsgroup at microsoft.public.sqlserver.... about how to create an MDX query with months across and years going down. Typically the answer to such a question would be to break up the time dimension and put years and months in their own dimensions. By default, and in tools such as Excel this is typically an impossible query. The rules of MDX state that a given dimension cannot participate in more than one axis in a query. However... You can create calculated ......

Posted On Wednesday, September 28, 2005 11:48 AM | Comments (0)