Darren Gosbell [MVP] - Random Procrastination

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... ......

Share This Post:
Short Url: http://wblo.gs/cIv

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 ......

Share This Post:
Short Url: http://wblo.gs/cIl

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 ......

Share This Post:
Short Url: http://wblo.gs/cF8

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)

Share This Post:
Short Url: http://wblo.gs/bJW

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, ......

Share This Post:
Short Url: http://wblo.gs/ZV3

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/... ......

Share This Post:
Short Url: http://wblo.gs/ZDp

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 ......

Share This Post:
Short Url: http://wblo.gs/ZC4

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 ......

Share This Post:
Short Url: http://wblo.gs/YeO

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 ......

Share This Post:
Short Url: http://wblo.gs/YZX

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 ......

Share This Post:
Short Url: http://wblo.gs/YFz

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 ......

Share This Post:
Short Url: http://wblo.gs/Xfc

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 ......

Share This Post:
Short Url: http://wblo.gs/XAh

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 ......

Share This Post:
Short Url: http://wblo.gs/X4Y

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 ......

Share This Post:
Short Url: http://wblo.gs/WeG

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: ......

Share This Post:
Short Url: http://wblo.gs/WYX

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 ......

Share This Post:
Short Url: http://wblo.gs/WP3

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 ......

Share This Post:
Short Url: http://wblo.gs/WGX

A little while ago Jamie posted about how he formats SQL code which happens to be quite similar to the way I do it. But this then lead me to think about the equivalent formatting for MDX. So if I get MDX like the following:with member measures.ptd as 'sum(periodstodate([Date].[... [Date].[Calendar].currentme... Amount] )',format_string = "currency" select {[Measures].[Measures].[Sales Amount] ,measures.ptd} on 0, {[Date].[Calendar].[Month].... ......

Share This Post:
Short Url: http://wblo.gs/Vxl

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 ......

Share This Post:
Short Url: http://wblo.gs/ViN

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}-... ......

Share This Post:
Short Url: http://wblo.gs/Vgo

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 ......

Share This Post:
Short Url: http://wblo.gs/Ved

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... ......

Share This Post:
Short Url: http://wblo.gs/VMm

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 ......

Share This Post:
Short Url: http://wblo.gs/VLK

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 ......

Share This Post:
Short Url: http://wblo.gs/VBS

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 ......

Share This Post:
Short Url: http://wblo.gs/Uyj

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> ......

Share This Post:
Short Url: http://wblo.gs/UuX

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 ......

Share This Post:
Short Url: http://wblo.gs/UpJ

Just in time for another early Christmas present the Analysis Services Stored Procedure team have just released version 1.2. It incorporates and couple of new functions and a few alterations. Below are the main additions, you can follow the links to the wiki pages for more information about each of the following. StrToSet - is a new class that incorporates a couple of new functions. The functions in this class are designed to allow the StrToSet function to receive shorter strings by passing in just ......

Share This Post:
Short Url: http://wblo.gs/Ug3

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. ......

Share This Post:
Short Url: http://wblo.gs/UZj

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, ......

Share This Post:
Short Url: http://wblo.gs/UPi

Microsoft has a couple of articles on how to set up HTTP connectivity for SSAS, one for Win XP http://www.microsoft.com/te... and another for Win2003 server http://www.microsoft.com/te... But Vista Business/Ultimate includes the new version of IIS (IIS 7) which means some of the steps have changed a little. So let's walk through the process with the help of a few screen shots. Getting binariesCopy the contents of the %Installation ......

Share This Post:
Short Url: http://wblo.gs/UKI

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 ......

Share This Post:
Short Url: http://wblo.gs/Tyc

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 ......

Share This Post:
Short Url: http://wblo.gs/Tej

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 ......

Share This Post:
Short Url: http://wblo.gs/TZF

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? ......

Share This Post:
Short Url: http://wblo.gs/TT3

Having written quite a few .Net Stored Procedures for the Analysis Services Stored Procedure project, I have often needed to debug into these stored procedures and it is pretty much second nature. But I had a question about this recently which made me realise that getting this working is not really well documented. There is a page in the product documentation which probably has enough information to get you going, but to my mind some of the steps are in the wrong order - and there are no pictures ......

Share This Post:
Short Url: http://wblo.gs/TIz

The following announcement was made recently on the Analysis Services forum. A collection of best practices on the design of OLAP objects in Analysis Services 2005 is now available at http://www.microsoft.com/te... These tips were gathered from the product team and our parners in order to help people create better OLAP databases. It looks like a lot of these Best Practices have been incorporated into the SQL Server Best Practices Analyzer tool ......

Share This Post:
Short Url: http://wblo.gs/SZL

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, ......

Share This Post:
Short Url: http://wblo.gs/S40

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, ......

Share This Post:
Short Url: http://wblo.gs/Rpa

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 ......

Share This Post:
Short Url: http://wblo.gs/QKQ

A few of us that worked on the Analysis Services Stored Procedure project http://www.codeplex.com/ASS... were having a discussion on email the other day and the issue of trying to override some of the built-in functions with a different implementation came up. (specifically some of the Excel functions) My first attempt at this was to register an assembly at the server level with a name of "Excel", thinking that when you called "Excel.Round(...)" it might call my assembly instead of the ......

Share This Post:
Short Url: http://wblo.gs/QH0

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 ......

Share This Post:
Short Url: http://wblo.gs/Pnq

Chris Webb recently blogged about a conversation that a group of us were having where the topic of the Timeout connection string property came up. Chris asked how I found out about this property. The technique I used is handy for exploring all types of connection string properties, so I thought I would share it here. Create a new text document on your desktop. Rename it to test.udl (its the extension that matters, the name can be anything you like) double click on this file and it brings up a "Data ......

Share This Post:
Short Url: http://wblo.gs/P14

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 ......

Share This Post:
Short Url: http://wblo.gs/Onc

[via Stefan Gossner] Wow, there is a bit over 1Gb over video content that can be downloaded for your viewing pleasure. I'm off to get my external USB hard drive. Below is a excerpt from the download site: Overview The Microsoft Office System Developers Conference 2006 featured more than 60 breakout sessions organized in eight technical tracks. In this track, you'll learn how the 2007 Microsoft Office system helps developers build smart client and browser-based BI solutions leveraging a wide array ......

Share This Post:
Short Url: http://wblo.gs/OnX

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 ......

Share This Post:
Short Url: http://wblo.gs/Mrv

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 ......

Share This Post:
Short Url: http://wblo.gs/MmH

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 /> ......

Share This Post:
Short Url: http://wblo.gs/Mgw

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) ......

Share This Post:
Short Url: http://wblo.gs/MFf

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 ......

Share This Post:
Short Url: http://wblo.gs/M4J

[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 ......

Share This Post:
Short Url: http://wblo.gs/KnZ

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 ......

Share This Post:
Short Url: http://wblo.gs/Kh3

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 ......

Share This Post:
Short Url: http://wblo.gs/KKf

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 ......

Share This Post:
Short Url: http://wblo.gs/K9j

I think I'm with Mitch on this one. I'm not overly keen on “PowerShell“, the new name for Monad. The product team have already renamed their blog to PowerShell, so it could be pretty much a done deal, but there seems to be a fair bit of criticism on Scobles post about the new name. While there are some people who point out some worse possible names, there are not many people showering the new name in praises. I quite liked Monad and even MSH was acceptable (although I don't know if Microsoft ......

Share This Post:
Short Url: http://wblo.gs/Jsh

There was a question on the newsgroup a few months ago asking how to extract data from an Analysis Services cube and store it in a SQL table using SSIS. I responded saying that in theory you should be able to setup a connection using the OLE DB provider for MSOLAP 9.0 and then put an MDX query in place of the SQL text. And I even when as far as setting up a package and previewing the data to make sure this was a viable approach. To this point everything looked fine and I assumed that the next step ......

Share This Post:
Short Url: http://wblo.gs/JsY

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 ......

Share This Post:
Short Url: http://wblo.gs/JUY

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 ......

Share This Post:
Short Url: http://wblo.gs/JSq

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 ......

Share This Post:
Short Url: http://wblo.gs/JGh

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 ......

Share This Post:
Short Url: http://wblo.gs/J5H

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. :)

Share This Post:
Short Url: http://wblo.gs/Iv3

The following DSO Script lists the size for either all the cubes in a database, or for a single cube. Copy the script out and save it to a file called ListCubeSizes.vbs To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run. cscript ListCubeSizes.vbs <Server> <Database> [CubeName] If your server, database or cube names have spaces in them, surround them in quotes. eg. “Foodmart ......

Share This Post:
Short Url: http://wblo.gs/IMT

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 ......

Share This Post:
Short Url: http://wblo.gs/I1T

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 ......

Share This Post:
Short Url: http://wblo.gs/Hbi

[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 ......

Share This Post:
Short Url: http://wblo.gs/HR3

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 ......

Share This Post:
Short Url: http://wblo.gs/HEW

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', ......

Share This Post:
Short Url: http://wblo.gs/H84

[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 ......

Share This Post:
Short Url: http://wblo.gs/H13

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 ......

Share This Post:
Short Url: http://wblo.gs/FXf

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 ......

Share This Post:
Short Url: http://wblo.gs/F2S

There have recently been a couple of questions in microsoft.public.sqlserver.... on how to implement a dimension where members can have multiple parents. I did this for a client a number of years ago and thought I would share the technique here. Before I start, I need to stress that this technique does have down sides, it will take longer to process your cubes and having the same members in multiple positions in a dimension can be confusing to some users. If you can implement your design using multiple ......

Share This Post:
Short Url: http://wblo.gs/F2R

The DSO script below will list the number of aggregations for either a single cube or all the cubes in the specified database. Copy the script out and save it to a file called ListOlapAggregations.vbs To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run. cscript ListOLAPAggregations.vbs [] If your server, database or cube names have spaces in them, surround them in quotes. eg. “Foodmart ......

Share This Post:
Short Url: http://wblo.gs/Es4

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 ......

Share This Post:
Short Url: http://wblo.gs/EVH

This script will backup and Analysis Services 2000 database and timestamp the .cab file with the date of the backup. Copy and Paste the following script into a file called OlapBackup.vbs and execute it be running the following from a command line. cscript OlapBackup.vbs Before running this script in your environment, be sure to update the assignments section. '--------------------------... Name : OlapBackup.vbs' Author : Darren Gosbell ......

Share This Post:
Short Url: http://wblo.gs/EVD

I created the following script in response to a question on the microsoft.public.sqlserver.... news group. Someone had posted a question about how to list all the source tables for an Analysis Services database that they had inherited. Using a one of my other scripts as a shell it was not too hard to pull this together, hopefully other will find it useful also. To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges ......

Share This Post:
Short Url: http://wblo.gs/EUI

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 ......

Share This Post:
Short Url: http://wblo.gs/DoB

As you can see by the dates in the comments below I have had this script kicking around in my tool box since 2002. It was used in the context of the development of a very large cube with lots of partitions. As we tweaked the cube structure during the development stage it would invalidate the existing aggregations, making it necessary to often have to redesign the aggregations. The wizard in Analysis Manager does this on a partition by partition basis and while you can choose to copy the aggregations ......