Analysis Services

There are 77 entries for the tag Analysis Services
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)

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)

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)

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 (0)

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 (6)

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)

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

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)

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 (8)

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)

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)

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)

A Rose by any other name - MDX Formatting
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].... ......

Posted On Sunday, June 15, 2008 10:52 PM | Comments (3)

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)

SSAS: ASSP 1.2 Released
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 ......

Posted On Saturday, December 22, 2007 11:25 PM | Comments (0)

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 (0)

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)

SSAS: Connecting via HTTP on Windows Vista
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 ......

Posted On Sunday, November 4, 2007 11:17 PM | Comments (15)

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: 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: 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 (4)

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)

Debugging SSAS .Net Stored Procedures
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 ......

Posted On Tuesday, May 22, 2007 3:51 PM | Comments (1)

SSAS: OLAP Design Best Practices for Analysis Services 2005 - Technet Article
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 ......

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

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)

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: Overriding Built-in functions
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 ......

Posted On Wednesday, December 13, 2006 2:42 PM | Comments (3)

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)

Building Connection Strings the easy way
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 ......

Posted On Sunday, November 5, 2006 9:39 PM | Comments (6)

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)

Office 2007 Video - Business Intelligence
[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 ......

Posted On Saturday, October 28, 2006 10:27 PM | Comments (0)

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: 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: 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 (34)

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)

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)

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)

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)

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)

Monad/MSH/Powershell What's in a name
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 ......

Posted On Wednesday, April 26, 2006 9:38 PM | Comments (0)

Extracting Olap Data from SSAS using SSIS
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 ......

Posted On Wednesday, April 26, 2006 8:17 PM | Comments (12)

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)

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)

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)

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)

DSO Script to list Cubes Sizes
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 ......

Posted On Friday, February 24, 2006 5:54 PM | Comments (0)

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

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)

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)

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)

Partial Alternate Hierarchies
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 ......

Posted On Monday, October 24, 2005 8:58 AM | Comments (9)

DSO Script to list the number of aggregations in a cube (or all cubes in a database)
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 ......

Posted On Monday, October 17, 2005 3:06 PM | Comments (0)

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)

VBScript to backup and timestamp an Analysis Services Database
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 ......

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

DSO Script: Listing all source tables for an Analysis Services 2000 Database
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 ......

Posted On Saturday, October 1, 2005 6:50 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)

Copying OLAP Partition Aggregations using DSO in Analysis Services 2000
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 ......

Posted On Thursday, September 8, 2005 7:22 AM | Comments (1)