SQL

There are 113 entries for the tag SQL
BIDS Helper 1.6 Beta Release (now with SQL 2012 support!)
The beta for BIDS Helper 1.6 was just released. We have not updated the version notification just yet as we would like to get some feedback on people's experiences with the SQL 2012 version. So if you are using SQL 2012, go grab it and let us know how you go (you can post a comment on this blog post or on the BIDS Helper site itself). This is the first release that supports SQL 2012 and consequently also the first release that runs in Visual Studio 2010. A big thanks to Greg Galloway for doing the ......

Posted On Wednesday, March 21, 2012 6:47 AM | Comments (3)

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)

BIDS Helper version 1.5 Released
Today the BIDS Helper team released a new version the details below are taken from the download page for the new release. Those of you with BIDS Helper already installed should get a new version notification when you next launch BIDS. The major feature in this release is the Biml Package Generator, John has been doing some great work documenting the capabilities of this new feature here (make sure you also check out the samples and tutorials of this powerful feature). Enjoy! ====== New Features Duplicate ......

Posted On Wednesday, June 8, 2011 7:20 AM | Comments (0)

New Technical Article on the Columnstore (Vertipaq) indexes in Denali
You may have already heard some information about the fact that Vertipaq indexes that are coming in the next version of SQL Server code named "Denali" following the announcements last week at PASS. If you want some more detailed information there is a new technical article available for download: Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0 which goes into more depth about how this will all work ......

Posted On Tuesday, November 16, 2010 1:55 PM | Comments (0)

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)

The PASS 2010 conference is approaching fast
Next week is the 2010 PASS conference. This will be my second PASS conference and I'm really looking forward to it. The content is great and I love the chance to meet up in person with all the really great people in the SQL community that come from all different parts of the world just to talk about SQL Server. On Tuesday 9th for Lunch I am hosting a "Birds of a Feather" table on "SSAS Triple A – Administration, Automation and APIs". Between PowerSSAS, BIDSHelper and the ASSP projects on codeplex ......

Posted On Thursday, November 4, 2010 10:12 AM | Comments (0)

Speaking at TechEd Australia 2010
TechEd 2010 Australia is approaching at a rapid pace and this year I have a speaking position. I will be talking on the topic of "PowerPivot: How, Why & When" doing some contrasting between PowerPivot and SSAS. And giving my opinions on where PowerPivot should be positioned in the Microsoft BI landscape. This will be happening at 3.30pm next Thursday. Even if you don't manage to catch my talk you should be able to find me hanging around the SQL Server area in the exhibition hall ......

Posted On Wednesday, August 18, 2010 10:25 PM | Comments (0)

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)

Speaking at the Sydney SQL User Group
Next Tuesday, July 13 I will be speaking at the Sydney SQL User Group on "BI Power Tools". I am going to run through some of my favourite free BI tools including (but not limited to) some of the projects that I contribute to like BIDS Helper, the AS Stored Procedure project and PowerSSAS. See here http://www.sqlserver.org.au... for more details and to register ......

Posted On Friday, July 9, 2010 8:10 PM | Comments (0)

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)

Re-inventing the Wheel
Interestingly I had two instances last week when I thought different parts of Microsoft were re-inventing the wheel. The first was while listening to Episode 496 of Dot Net Rocks, where they were talking about project Trident from Microsoft Research. My first thought as they were describing the "visual workflows" was that MS Research had re-invented SSIS. Then as they described the versioned catalogue of workflows it sounded more like the way DTS used to store it's packages. But when they started ......

Posted On Monday, November 23, 2009 10:40 PM | Comments (0)

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)

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)

BIDS Helper release 1.4.1
I am happy to announce that we recently put out a new release of BIDS Helper. I have copied the release notes out below, but one important "feature" that is not listed is that we now have a build script which is a modified version of the psake Powershell build script written by James Kovacs. It had gotten to the point where it was a bit of an effort to do a build. You had to start by making sure you had the latest version of the source code, then version number had to be updated in a number of spots, ......

Posted On Thursday, May 14, 2009 6:40 AM | Comments (0)

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)

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

SQL Down Under Code Camp 2008 - Attribute Relationships Presentation
At the recent SQL Down Under Code Camp my presentation was on Attribute Relationships and was sub titled - "You can choose your relatives, but you can't choose your friends". It covered Attribute Relationships, how they work, how to set them up and why you should bother. I put a bit of effort into making it what I think is an attractive looking presentation, but if you were not there it probably does not make much sense without the narrative and demos that went with it. So I am mainly posting this ......

Posted On Friday, October 24, 2008 6:24 AM | Comments (2)

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)

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)

Invalid namespace - Running SSRS against a named instance
I recently went to check the configuration of my SQL Server 2008 Reporting Services instance (which is a named instance called "SQL08") and it came up with a "connect" dialog and when I clicked on the "find" button I got an "invalid namespace" error. I found a few threads on the MSDN forums about this and eventually a Microsoft guy came up with a solution. The post at the end of this thread http://forums.microsoft.com... had the solution which worked for ......

Posted On Thursday, September 4, 2008 8:21 AM | Comments (6)

Minor Update to BIDS Helper for SQL 2008
If you have installed BIDS Helper 1.3 for SQL 2008 in the last few days, you might want to head back to the site and pick up the new 1.3.0.7 build for 2008. There is an issue with one of the assembly references picking up a 9.0 assembly instead of a 10.0 assembly which causes the Variable Window Extensions to throw various error messages. If you are using the SQL 2005 build you will not be affected by this issue ......

Posted On Wednesday, August 20, 2008 11:46 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)

PowerShell and SSIS
Up until now, most of my use of PowerShell has been against SSAS, but I recently had a need to setup a number of SSIS packages for some relatively large CSV files. My problem was that the CSV files had about 125 columns and 120 of them (10 years of monthly figures) need to be inserted into Numeric(18,5) columns in SQL Server. However the connection manager auto-detected these columns on my flat file connection manager as DT_FLOAT. Changing these columns to DT_NUMERIC and setting the scale to 5 quickly ......

Posted On Monday, July 14, 2008 12:21 AM | Comments (1)

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)

A new Logo for SQL Server
[via EXEC dbo.LongTermMemory__Archive] Apparently SQL Server now has a new logo. It's interesting... I think they felt left out at the launch events with Visual Studio 2008 and Windows Server 2008 when they were the only product without a logo. I'm not quite sure what it's meant to be, but to my eye it seems to have been influenced a bit by the Kookaburra :) What do you think this new logo is ......

Posted On Saturday, June 7, 2008 1:08 PM | Comments (3)

We are hiring
My employer, James & Monroe are currently looking for people with strong skills in the Microsoft BI technologies. We are specifically looking for people to work at the Senior Consultant level that have experience with the SQL Server 2005 BI technologies in addition to experience with .Net programming. There is the likelihood of working with PerformancePoint so any experience in that would be advantageous. The role would be based in the Melbourne area so you would either need to live in Melbourne ......

Posted On Friday, May 30, 2008 4:37 PM | Comments (2)

What's New in SQL Server 2008 BI - Melbourne SQL Server User Group - May 20
If you are on the mailing list for the Melbourne SQL Server User Group you will already be aware of this, but I am doing a talk on Tuesday night on the new BI features in SQL Server 2008. You can find the details here: http://www.sqlserver.org.au... If you are going to be in town feel free to drop by and say hello ......

Posted On Saturday, May 17, 2008 2:11 PM | Comments (1)

More BI MVP goodness
I just learned that we have some new BI MVPs that were awarded recently On the SSAS front Vidas Matelis (of www.ssas-info.com fame) has received a SQL Server MVP award. And two of my fellow countrymen and PerformancePoint specialists Adrian Downes and Nick Barclay have both been awarded too ......

Posted On Wednesday, April 2, 2008 9:36 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: What's new in the Schema Rowsets?
I was asking myself this question recently and while I could not find anything in Books Online, it is not too hard to figure this out for ourselves. The following comparison was run between SSAS 2005 SP2 and the February CTP (CTP6) of SSAS 2008. I really love the way that you can discover the metadata in SSAS, all I had to do is to run a DISCOVER_SCHEMA_ROWSETS XML/A command against each server and then use XMLNotepad 2007 to compare the resulting XML. In the SQL Server 2008 you should find that ......

Posted On Thursday, February 21, 2008 2:56 PM | Comments (2)

CTP6 and CU6 are now available - Follow-up
Just a quick follow-up on Chris Webb's recent post about the release of CTP6 for SQL 2008 and CU6 for SQL 2005. Chris mentions that there is still no suggestion that SP3 for SQL Server 2005 is in the works despite having released 6 cumulative update packs. If you would like to see a Service Pack 3 you can add your voice to the push for SP3 by voting for it on connect here: https://connect.microsoft.c... The other interesting thing about the release ......

Posted On Thursday, February 21, 2008 1:51 PM | 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
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)

Connect Issue Updated
I had a notification recently that one of the issues that I have reported to the SQL Server connect site (http://connect.microsoft.c... ) had been fixed. Deploying a Renamed Database from BIDShttp://connect.microsof... This issue was closed a little while ago as being a duplicate of a bug in the internal bug database, which I was a bit disappointed in as this meant that I would no longer get automatic feedback on this issue. So I was pleasantly ......

Posted On Saturday, December 22, 2007 1:53 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)

Supporting Add-ins for Management Studio
I was searching the connect site for before posting an issue the other day and came across this SSMS : Allow support for AddIns as in Visual Studiohttps://connect.micro... Which I have thought would be a good idea since I first started work on BIDS Helper. So if you think it would be a good idea too, please follow the link and add your vote. I don't think there is any chance of this feature making it into the release of SQL Server 2008, ......

Posted On Wednesday, October 24, 2007 8:22 PM | Comments (2)

SSIS Best Practices - SQL Down Under Code Camp 2007
This post is mainly for those people that were at my presentation at this years SQL Down Under Code Camp. I said that I would post the slides and the sample project (there are links at the end of this post) and here they are. a Powerpoint 2007 version of the presentation [link 2] a Powerpoint 97-2003 version of the presentation [link 2] and the sample project I was using for all the demos [link 2] Note: Some of the demos are using the trash destination and the data generation data source from www.sqlis.com ......

Posted On Sunday, October 21, 2007 5:37 PM | Comments (1)

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)

You go away for a few days and look what happens...
I have been on holidays recently and have just finished catching up with things that happened while I was away. For anyone else who might be interested, here's a quick re-cap... Mosha was forced to cancel his PASS pre-conference session, but then release an alpha version of his MDX Studio tool and then an update to it Adrian and Nick both announced they they have been working on not 1 but 2 Rational Guides to PeformancePoint Server books. One on the Planning module and the other on the Analyze and ......

Posted On Tuesday, September 25, 2007 5:51 PM | Comments (1)

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

SQL Server 2008 - launch date announced
Sorry - I got one important word wrong when I posted previously that the release launch date had been announced for SQL Server 2008. It turns out that Feb 27th 2008 is the launch date and not the release date. I should have known better, this is the same scheme that the mighty Microsoft marketing machine came up with for the 2005 launch. Oh well - it should be a good party anyway and with any luck the actually release date will be about the same time ......

Posted On Monday, July 16, 2007 7:54 PM | Comments (2)

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

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)

SSAS: BIDSHelper Released!
I am pleased to announce that we have just launched the first public release of BIDSHelper. What is it? It is an Add-in for the BI Development Studio (BIDS) for SQL Server 2005 that adds a number of useful features. This is an open source project, hosted on codeplex under a Shared Source Permissive License. Currently the features are all centred around SSAS, but we are planning to work on a few for SSIS and SSRS in future versions. Where do I get it? It can be downloaded from www.codeplex.com/bidshelper ......

Posted On Tuesday, May 15, 2007 10:42 PM | Comments (1)

Another Aussie SQL MVP

Congratulations to Peter Ward who has just been awarded as an MVP in SQL Server, that takes us to 6 SQL Server MVP's in Australia.

Posted On Monday, April 2, 2007 9:07 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)

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)

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

SSAS: Why can't I pass a Dimension as a parameter to a stored procedure?
The group of us that worked on the Analysis Services Stored Procedure project were having a discussion via email recently and one of the guys lamented the fact that you cannot pass a dimension object as a strongly typed parameter into an SSAS stored procedure. After pondering on this for a little while it occurred to me that, in the change to the attribute based model in AS2k5, dimensions now appear to have become little more than logical containers for attributes and hierarchies. This is in real ......

Posted On Wednesday, November 8, 2006 9:20 PM | Comments (1)

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)

Congratulations to Rob Farley - a new SQL Server MVP
Congratulations to Rob Farley, who runs the SQL Server User Group in Adelaide on recently being awarded as an MVP in SQL Server http://robfarley.blogspot.c... That takes the total number of SQL Server MVPs in Australia up to 5. Rob is a fellow speaker at the SQLDownUnder Code Camp this week end, so if you are going, make sure to congratulate him. And if you look really close, you might still be able to see the scars where the implants were inserted :) ......

Posted On Wednesday, October 4, 2006 9:19 AM | Comments (0)

SSAS: Named Queries or Database Views?
I was recently asked about the issue of whether it is better to use views in the database or named queries in the data source view (dsv). I don't think there is any one correct answer. I think it comes down to the following 2 issues: Consistency: If you already have logic in database views, I would continue to use them. As long as you know that you go to one spot to view/change the logic. Putting the logic in 2 different spots could lead to confusion. Security Permissions: often you may not have ......

Posted On Tuesday, September 5, 2006 8:28 PM | Comments (6)

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)

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)

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)

SSIS alternative to the Slowly Changing Dimension transformation
[via Jamie Thomson] If you are transforming large dimensions this will be of interest to you. Alberto Ferrari has come up with an interesting solution to the performance of the SCD transform task. The built-in SCD transform issues separate SQL statements for each incoming dimension row, Alberto has a custom SSIS component here (http://www.sqljunkies.com/... that compares two tables in one operation. It is definitely something I am going to look ......

Posted On Wednesday, June 14, 2006 7:44 PM | Comments (6)

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)

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)

Documentation Tools
I got these interesting links sent to me by Marco. http://www.dtsdoc.com http://www.dbdesc.com And while they are interesting and the sample output looks quite good, I can't help wondering when we will see tools like these appear for SQL 2005. Especially with the way xml is so intrinsic to SSAS and SSIS. I have started doing some of my own investigation into documenting SSAS and it looks quite promising ......

Posted On Tuesday, April 18, 2006 9:24 PM | Comments (5)

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)

MSDN Nuggets
I have watched a few full length web casts from Microsoft and a couple of episodes of dnrTV. And while these are a great learning tool, it is not always easy to find an hour to set aside to watch many of these (well it's not easy for me <g> ) I came across the following after someone posted a reference to them on the aus-dotnet mailling list. MSDN Nuggets: http://www.microsoft.com/uk... The concept looks really interesting, these are little 10-15 minute videos on specific ......

Posted On Sunday, April 2, 2006 9:17 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)

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)

When is a variable not a variable?
When it is a SQL Server table variable! This topic came up a little while ago on the SQLDownUnder mailing list and surprised me because it went against most things I had heard about table variables. In fact a few days later I got a newsletter from SQLServerCentral.com with the following tip in it: If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory ......

Posted On Sunday, March 26, 2006 5:54 PM | Comments (3)

Not going to Redmond :(
Last week I got an email from Microsoft inviting me to a meeting in Redmond (same as Rob), apparently due to the fact that I was 'high performing beta examinee'. Rob make the cut, but unfortunately I missed out :(. I thought I responded fairly quickly, but I would not have been suprised if there were only a limited number of overseas people that MS were prepared to fly in. I held off on blogging on this until now, as I did not want to “jinx” myself - I was half expecting to get a “sorry, ......

Posted On Tuesday, March 21, 2006 7:20 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)

Squeeze Every Last Drop of Performance Out of Your Virtual PCs
[via Andrew Connell] HOWTO: Squeeze Every Last Drop of Performance Out of Your Virtual PCs I'm not spending nearly as much time working in VPC images as I used to while SQL Server 2005 was in beta. But I still use them for playing with Sharepoint and Business Scorecard Manager and they are a great technology for experimenting with things. This blog post from Andrew provides some great tips on how to get the very most from your VPC's ......

Posted On Monday, March 13, 2006 5:35 PM | Comments (0)

Key Value Pairs in Database design
There was an interesting thread on the SQLDownUnder mailing list last week about the use of Key Value Pairs in database design. One of the Guru's on the list Jeremy Huppatz made a few posts which covered off pretty much all the issues I was thinking about. One of the key points that Jeremy made was this one: Remember that relational databases are designed with 3 key goals - reliable data entry, stable consistent storage and rapid retrieval. By using KVP's you throw the rapid retrieval capability ......

Posted On Sunday, March 12, 2006 7:02 PM | Comments (8)

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)

I passed my first SQL 2005 exam!
I sat for the beta of the certification exam for the 71-441 PRO: Designing Database Solutions by Using Microsoft® SQL Server™ 2005 certification back in November last year. And I was just checking my MCP transcript online and noticed a new entry! Now, I am not normally in the habit of checking my online MCP transcript, but someone mentioned recently on the aus-dotnet mailing list, that they noticed a new entry on their transcript (for a dfferent exam) - so I have been checking my details ......

Posted On Sunday, February 19, 2006 6:27 PM | Comments (8)

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)

SQL Server User Group December 2005 - SQL CLR Integration
This month's Melbourne SQL Server User Group meeting was all about the CLR integration in SQL 2005. There has been a lot of hype about this feature and you can find various views ranging from “use it for everything” to “don't even enable it”. The talk we given by one of our local members, Greg Obleshchuk from A & G Software and I think he did a great job of presenting the good points of the CLR integration and highlighting when to use it and when not to use it. As a general ......

Posted On Wednesday, December 21, 2005 7:03 PM | Comments (0)

Oracle 10g vs SQL Server 2005
I had a request recently from someone for information on performance of Oracle 10g vs SQL Server 2005. I don't think there is too much around on SQL 2005 yet as it has not been officially released for that long and the Beta and CTP releases were not really meant to be used for benchmarking (in fact I think the license agreements explicitly states that this is not allowed). I can't actually claim credit for this as the topic came up on the SQLDownUnder mailing list a couple of weeks ago and two of ......

Posted On Monday, November 21, 2005 1:34 PM | Comments (13)

Trying to register for a Microsoft e-Learning course.
Well, I have been trying to register for one of Microsoft's free e-Learning courses for SQL Server 2005 for the last 24 hours. But each time I try to access I get told I need to register and each time I try to register I get the following: There was an error while registering you as a Learner. Please try after some time. I have registered before for other similar courses without any problems, so “after some time” I come back, but still no luck. Darn it! I had set aside some time this ......

Posted On Saturday, November 19, 2005 4:57 PM | Comments (5)

Resources for Exam 70-441: Designing Database Solutions by Using Microsoft SQL Server 2005
This page is a work in progress, I am using it as a repository to store resources as I come across them. Preparation Guide for Exam 70-441 (lists all the skills being tested)http://www.microsoft... Skills AssessmentIntroducing Microsoft SQL Server 2005 for Database Developers BooksIntroducing Microsoft SQL Server 2005 for Classroom TrainingCourse 2734: Updating Your Database Development Skills to SQL Server 2005 Free* Microsoft E-LearningThe following e-learning ......

Posted On Tuesday, November 15, 2005 6:39 PM | Comments (8)

SQL Server Management Studio Express November CTP is available
This was another announcement up on the website for my local SQL Server user group - cool :) [via Australian SQL Server User Group] Microsoft SQL Server Management Studio Express (SSMSE) provides a graphical management tool for SQL Server 2005 Express Edition (SQL Server Express) instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, ......

Posted On Tuesday, November 15, 2005 11:56 AM | Comments (1)

Microsoft have dropped a late-breaking but exciting new feature into SQL Server 2005 - "Scalable Shared Databases"!
This was an announcement up on the website for my local SQL Server user group [via Australian SQL Server User Group] From Lubor Kollar in the SQL MVP forum this morning: Scalable Shared Database (SSD) is ability of SQL Server Enterprise edition to access the same database hosted on SAN from several server instances. It enables scaling read-only workloads by adding additional servers accessing the same database. This feature is available in the currently shipped RTM version of SQL Server 2005. The ......

Posted On Tuesday, November 15, 2005 11:54 AM | 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)

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)

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)

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)

New Features in Reporting Services in SQL 2005
I have been asked a couple of times now, “what new features are coming in SQL 2005?” and even by people that have access to, or have seen the CTP. It's true that there have not been a lot of changes in SRS, let's face it, the version released for SQL 2000 was really just a pre-release of the SQL 2005 version. But in the 18 months or so there have been a number of small changes that really to add to the products maturity. The following list summarising the major changes was taken from the Microsoft ......

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

SQL Server 2005 - Service Broker
Service Broker is a new feature of SQL Server 2005. Even though I have been playing on and off with SQL 2005 since it was “Yukon Beta 1”, I had not heard much about this feature before going to the Melbourne SQL Server SIG session last night. I was not sure if it would have many applications in a Data Warehousing application, which is the area I normally work in. But due to the fact that it allows you to asynchronously kick off a long running process, using standard T-SQL code I can see ......

Posted On Wednesday, June 22, 2005 5:21 PM | Comments (5)