Business Intelligence on SQL Server

Microsoft BI Conference

2007-05-06T22:53:00.001+12:00

Ok so I have not blogged in a long while. So time for an update!

I have been very busy working on a few data warehouse projects and have not had the time to blog. So I am glad to get sometime out to go to Microsoft's first BI conference in Seattle next week.

It's a Conference of many firsts, first time Microsoft has created a forum for BI using Microsoft products. Sure there has been many a 'TechEd Session' on SSRS, SSAS and SSIS. But this is the real deal best practices, case studies and the like. And I am not talking about project real either. The big difference with this conference is there is not mix bag of sessions.

As a part of the trip I am going to blog every day of the conference to let you know what went to.

It's very exciting.

Microsoft and Teradata Collaborate

2007-01-19T19:46:00.000+13:00

Microsoft and Teradata have formally announced a partnership. It looks like SSIS, AS2005 and RS2005 will be able to access Teradata enterprise warehouse. This will help Tetra data access Performance Point.
check out:

Microsoft and Teradata Collaborate to Offer Business Intelligence Solutions
http://www.teradata.com/t/

Business Intelligence with SQL Server 2005: Transform Data into Decisions

2007-01-18T20:46:00.000+13:00

Just came across a set of PerformancePoint webcasts on http://thesource.ofallevil.com/events/series/sqlserverbi.mspx. Check out:

TechNet Webcast: Performance Management 101 with PerformancePoint Server 2007 (Level 200)
Thursday, January 18, 2007
1:00 P.M.–2:00 P.M. Pacific Time
Join us and learn how Office PerformancePoint Server 2007 enables businesses to deliver performance management capabilities broadly by providing an application that is easy to use and less costly to deploy than traditional solutions.

TechNet Webcast: Microsoft Business Intelligence: Introduction to PerformancePoint Server 2007 (Level 200)
Friday, January 19, 2007
8:00 A.M.–9:00 A.M. Pacific Time
Discover how Office PerformancePoint Server 2007 provides you with critical visibility into the factors that affect your business, allowing you to make decisions and take actions that drive better business outcomes.

Round up for 2006

2007-01-16T00:08:00.000+13:00

Happy New Year! I am sorry I have not blogged in awhile. It’s largely due to a house we are building in Auckland. Building a house is very similar to building a data warehouse, best left to the experts.

What a year 2006 it has been. I have to say Business Intelligence on SQL server has definitely seen some big changes this year. After the release of SQL Server 2005, big things had to happen to top the release and what a year it has been.

Service Pack 1 Was not long in coming after the November release of SQL, helped a lot of companies to get of over the ‘not until SP1’ issue. The release was not without controversy. The removal of the ‘Select All Parameter’ caused some developers concern. Microsoft removed it due to performance issues. At the same time MS released the feature pack with a series of great free add-ons. Such as a SAP .Net OLE DB driver and data mining viewer controls. Check out:

Rather Ripped (Select All in Service Pack 1)

Feature Pack for Microsoft SQL Server 2005

The acquisition of Proclarity has shown Microsoft continued commitment to build its BI platform. Seeing the direction and integration of Proclarity, Balance Scorecard Manager, etc into Performance Point is very exciting and will start to give some of the Analytical BI vendors a run for their money. Check Out:

Performance-Point-Screenshots

Performance Point Home

Office 2007 was shown off at TechEd and is very exciting from a BI point of view. The new excel features are going to continue Excels front-end dominance. Although the new Excel front-end may take some getting use to for some users the new BI features are well worth the upgrade. Check out:
Business Intelligence in Excel 2007

Service pack 2 CTP is going to continue to enhance the BI features of SQL 2005. Report builder will now support Oracle as a data source. The ‘Select All’ parameter is now back in en-vogue. Of course there will be the standard bug fixes. So best get to testing and seeing the new fixes/enhancements. Check out:

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2 Community Technology Preview (CTP)

How to obtain the latest service pack for SQL Server 2005

2006-11-09T22:59:00.000+13:00

Microsoft have just release SQL Server 2005 Service Pack 2 CTP. Check out: How to obtain the latest service pack for SQL Server 2005

MCITP: Business Intelligence Developer

2006-09-30T19:04:00.000+12:00

Microsoft has just released an update to the Microsoft Certified IT Professional (MCITP) with the creation of Business Intelligence Developer credential. It looks like you only need to sit two exams to become a Business Intelligence Developer.

This qualification is a step up from the Microsoft Certified Technology Specialists in Microsoft SQL Server 2005 Business Intelligence (MCTS: SQL Server 2005 BI).

The training material is available but the exams are not available until early 2007. It might be a good time to start looking into getting this certification.

Check out:
MCITP: Business Intelligence Developer
MCTS: SQL Server 2005 Business Intelligence

Tips, Tricks, and Advice from the SQL Server Query Optimization Team

2006-09-16T22:56:00.000+12:00

A colleague of mine has just introduced me to one of the best blogs I have seen in along while. The Tips, Tricks, and Advice is brought to you by the SQL Server Query Optimization Team.

Here are some of my favourite posts on the performance features of SQL 2005 Query Optimizer. Check out the following:

SQL Server 2005 Skill Assessments

2006-09-12T20:27:00.000+12:00

Microsoft has released a series of free skill tests that are well worth trying. There are tests covering most Microsoft products. There are two categories that I have given a go.

Introduction to SQL Server 2005 (DBA, Developer, BI): SQL 2005
Introduction to SQL Server 2000 Reporting Services: RS 2000

This Months Book Recommendation: Microsoft Data Warehouse Toolkit

2006-09-04T00:05:00.000+12:00

After TechEd I realised that in my slide deck I did not recommend The Microsoft Data Warehouse ToolKit by Joy Mundy and Warren Thornthwaite as required reading before starting a BI project.

If you are new to BI or new to the SQL Server 2005 BI platform this book is a great starting point. This book covers an end to end BI solution on the SQL server 2005 platform. The book covers a Reference Architecture for implementing a Microsoft BI Solution.

The book also includes a case study using the Adventure works example database as the source for an end to end BI solution. All the sample code can be downloaded from www.msftdwtoolkit.com

My TechEd session Ready for download

2006-08-31T21:06:00.000+12:00

My TechEd session on Delivering an End to End Business Intelligence is now up on Microsoft for download.

Check out: BIN213.ppt

Project REAL Reference Implementation

2006-08-24T22:17:00.000+12:00

I have just come across a new download from project real. It’s a complete business intelligence reference implementation. I am currently downloading the 238.2 mb sample. It looks like it a good example of an end to end solution.

Check out: Reference Implementation

New Zealand TechEd Is Finally Here!

2006-08-09T16:21:00.000+12:00

TechEd 06 has been a sold out event for a number of weeks now. It sold out in record time. For those you that are not aware, Microsoft NZ TechEd is the largest annual IT conference in New Zealand with over 2000 delegates attending.

This year I am presenting again. I am lucky enough to have one very comprehensive session on Business Intelligence.

BIN213 - Delivering an End to End Business Intelligence Solution on Monday 21st Aug 2:00:00 p.m. to 3:15:00 p.m.

My session is going to cover a detailed overview of implementing Microsoft Business Intelligence solutions. I am covering topics such as SQL Server 2005, SSIS, Analysis Services 2005, Reporting Services 2005, Office 2007 and ProClarity.

I am also looking at the business reasons behind BI projects. In the demonstrations I will show an integrated architecture for a Microsoft BI platform.

Ok it’s a lot to cover. So I will also be about on the HP stand this year to go through any questions or demos you would like to discuss in detail.

If you have registered for TechEd you need to login to CommNet and book your session schedule.

For complete schedule information see Nigel Parkers Blog

SQL Server Versions

2006-08-03T00:08:00.000+12:00

I have lost count the number of times I go searching for the patch level or version number in SQL.

This was something I did all the time with SQL 2000. It all started when SP 2 came out. We started to get issues with dts packages that were developed on SP1 and then run SP2. The problem was the package format changed between versions. Hence the question what version I am really running?

Why have I brought this up you may ask? Because now that we have SP1 for SQL Server 2005 and the rollup hotfix. We now have three versions of SQL Server 2005 that are in support by Microsoft.

How Do I get the Version of SQL Server?
The easiest and most reliable is the T-SQL Statement below:

SELECT @@Version

There are of course lots of other methods but I prefer T-SQL. This works for SQL 7 and above. The statement returns the SQL Server edition, version, OS and OS Patch number, and the most important the SQL Build Number for Sqlservr.exe.

The build number will tell you the patch level of your SQL Server.

Build Numbers for SQL Server 2005

2005.90.1399 - RTM first commercial release of SQL Server 2005

2005.90.2047 - Service Pack 1 for SQL Server 2005 see: How to obtain the latest service pack for SQL Server 2005
2005.9.0.2153 - Rollup hotfix for SQL Server 2005 see: Cumulative hotfix package (build 2153) for SQL Server 2005

Other Build versions
There are heaps of sources for this information. Listed below are some helpful links for earlier versions of SQL Server and software update naming conventions.

How to identify your SQL Server version and edition

New naming schema for Microsoft SQL Server software update packages

New Microsoft SQL Server 2005 Business Intelligence Example

2006-07-24T19:49:00.000+12:00

I have just come across a new set of sample databases for SQL Server 2005. Some of the examples are based on Project Real Business Intelligence best practices project. Based on a real world implementation of SQL Server 2005 by Microsoft.

Check out: SQL Server 2005 Samples and Sample Databases (July 2006)

Installation of SQL Server Service Pack 1 by Windows Update???

2006-07-20T20:35:00.000+12:00

The other day something happened to my laptop that took me completely by surprise. Windows Update decided SQL2005 SP1 was a critical patch. And proceeded to download and install SP1.

Although this isn’t an issue for me at then moment as I was going to install the Service Pack. I was taken a back with Windows Update installing it in the first place.

Like most DB pros I prefer to manage the database patch level on my own. I can see this kind of service pack roll out causing problems for IT Administrators and DBAs a like. Particularly with SQL express instances that are managed by business users.

Kimball Group Webcasts

2006-07-17T19:59:00.000+12:00

I have just come across a series of must see webcasts by the Kimball Group consultants that wrote The Microsoft Data Warehouse Toolkit. I have not viewed them all yet. I am currently viewing the MSDN Architecture Webcast: Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200). Which I highly recomend.

Check out the other Kimball Group Webcasts at:http://www.microsoft.com/sql/solutions/bi/kimballwebcasts.mspx

Free Tools that have made my SQL life a bit better

2006-07-04T19:41:00.000+12:00

I normally don’t recommend tools on this blog. But over last month I have been working with couple of tools that are worth a mention.

The first tool on the list is SQL Prompt by Red-Gate. I have been a fan of red gate bloggers for a while now at http://www.simple-talk.com/. SQL Prompt is without a doubt one of the most useful ad-ins for SQL Management Studio and Query Analyzer. This little tool is Intellisense for SQL Server. This tool dynamically looks up common functions and syntax as you type. It’s a free download until September 2006.

Check out: SQL Prompt

The second tool was recommended to me by Chris Webb. It’s a replacement for SQL Manager for stoping and starting all of SQL Server 2000/2005 services created by Jasper Smith at SQLDBA.com

Check out: SQL Manager

The last tool on my list is a new migration script by Marco Russo. If you were like me and followed the DTS best practices guide that MS put out and used UDL data sources. You will find that are unable to migrate your DTS packages. Marco has put together a great tool for converting UDL and other DTS objects that do not migrate.

Check out: A tool to improve DTS to SSIS migration

SQL Management Studio Short Cuts

2006-06-01T21:44:00.000+12:00

I have been working with SQL Management Studio (SMS) a fair bit lately. Like most developers I always look for the standard short cuts that I used on Query Analyser. Below are a few of my favourites.

Commenting

I use these short cuts all the time to comment large sections of code. The comment style I prefer the most is dash form which I prefer too the slash style. -- Dash Comment /* Slash Comment */

To comment in SQL management studio you must now do Ctrl+K, Ctrl+C instead of Ctrl+Shift+C in Query Analyzer.
To uncomment in SQL management studio you must now do Ctrl+K, Ctrl+U instead of Ctrl+Shift+R in Query Analyzer.

Formatting

To indent a block of code Select the code you would like to indent and press TAB to indent
Shift+TAB to un-indent your code
Word Warp long strings with Ctrl+E, Ctrl+W
Make Uppercase Ctrl+Shift+U
Make Lowercase Ctrl+Shift+L

Bookmarks

Ok so you are working on a massive script that travels for miles. Well then you better be using book marks to navigate that code. Bookmarks are great for moving through scripts and trouble shooting. Try out the following the next time you are working on a big script.

Toggle Book Mark on/off and Crtl+K, Ctrl+K
Enable all book marks Crtl+K, Ctrl+A
Move to next bookmark Ctrl+K, Ctrl+N
Move to previous bookmark Crtl+K, Ctrl+P
Move to next bookmark folder Ctrl+Shift+K, Ctrl+Shift+N
Move to previous bookmark folder Ctrl+Shift+K, Ctrl+Shift+P
Clear book marks Ctrl+K, Ctrl+L

This Months Book Recommendation: SQL in a Nutshell

2006-05-11T22:49:00.000+12:00

Ok I haven’t recommended a book in a while, and it’s not from the lack of reading them. I thought its time I recommend one of the books I always use on most of the projects I work on. SQL in a Nutshell from O'Reilly Press is my universal translator between database engines.

It’s the one book I take onsite with me, when I am working in multiple database environments. SQL In a Nutshell allows me to look at functions and syntax across database platforms. For instance ever wondered what CASE is in Oracle? Or what date functions are supported in MySQL? SQL In a Nutshell will give an overview of each SQL function and what is supported across database platforms.

This book also gives a great overview of the ANSI standard for SQL. The Second edition is well worth getting as it is updated with the SQL language definitions for Microsoft SQL Server, Oracle, MySQL, and PostgreSQL.

BTW: CASE in Oracle is DECODE go figure.

Blog Neglect and SQL Server 2005 Service Pack 1

2006-04-29T23:38:00.000+12:00

Ok, it’s been a really long time since I posted, well over a month. Work commitments have taken a bit more of my time this month. If you missed it this may have been the biggest month for SQL Server since RTM release.

April saw the release of Service Pack 1 for SQL Server 2005 and Microsoft making another tool acquisition in the BI space.

SP1 was the most interactive service pack I have seen. The CTP access allowed SQL users to work with the beta product and provide feed back to MS on any possible issues. Creating what should be a solid service pack.

If you have not downloaded SP1 yet check out: Download SQL Server 2005 SP1

A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP) March 2006

2006-03-24T15:36:00.000+12:00

I came across an interesting KB post on Microsoft support today. A complete list of bugs fixed in SQL Server 2005 Service Pack 1 CTP. There is a list of 19 fixes at the time of this post. This CTP service pack fixes a fair amount of Analysis services issues. The list will be updated as Microsoft document other fixes.

Check out: A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 CTP March 2006

If you have not downloaded SQL Server 2005 Service Pack 1 CTP check out:

How to obtain the latest service pack for SQL Server 2005

Microsoft SQL Server: SQL Server 2005 SP1 CTP

2006-03-19T14:09:00.000+12:00

Good News,

The service pack 1 for SQL Server 2005 has now gone public. If you have been waiting for Database mirroring then this is the service pack you should be testing.

Also Books online has been updated for this CTP. The services pack has been in private beta for a while now and it’s great to see Microsoft following through with the same level of beta testing as was done for SQL 2005.

Remember the CTP is not a supported release and is for testing purposes only. So the same rules apply to the use of any beta product. Bearing that in mind I would get into testing the CTP soon as possible

Check out: Microsoft SQL Server: SQL Server 2005 SP1 CTP

SQL Server SSIS Sample Component: UnpackDecimal

2006-03-15T23:04:00.000+13:00

Microsoft have just release another SSIS Sample Component for Packed Decimals.

Packed Decimals are generally converted using the OLE DB provider.

SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.

Download details: SQL Server SSIS Sample Component: UnpackDecimal

Beginners Guide to Business Intelligence – What is a Data Warehouse?

2006-03-13T21:24:00.000+13:00

This is the second instalment of my blog feature on Beginners Guide to Business Intelligence. This time I am looking at: What is a Data Warehouse exactly?

Business Intelligence Context
First Business Intelligence in my opinion is not just architecture. It is a strategy of information within an organization. All organizations report on the performance of their business processes, services and sales. How they manage and organize information is maintained by a Business Intelligence Architecture, which is made up of several components. A data warehouse is just one such component. The data warehouse is sometimes seen as the corporate repository.

Before we get into the detail of data warehouse definitions lets have a flash back for a bit of data warehouse history.

Birth of Data warehousing
In a sense, companies have been reporting on information since the dawn of computing systems. Data warehousing is really a collection of technologies that have evolved within information systems. Listed below are some of the legacy terms that you may come across when talking about data warehouses.

Executive Information System (EIS)
One of the first attempts at giving executive managers a high level view of business activities, this kind of system was widely adopted in the late 70s and early 80s. Usually these systems would be summary tables built on top of transactional tables. EIS focused solely on the financial view of the organisation with limited information other than budget vs. actual. The tables would be maintained within the source system and loaded once a month and reports would be generated on top of them, almost always printed on line-flow paper.

Management Information System (MIS)
An extension of EIS, the MIS gave business users a wider view of information in the form of Balance Scorecards and other summarised views of information. The term Key Performance Indicators (KPI) was widely adopted for measures that were just not financial based. For the first time summary tables would be maintained independently of the transactional source system. This kind of system was widely adopted in the late 80’s early 90’s. In fact entire departments were named after MIS systems, hence the term MIS Department.

Decision Support Systems (DSS)
The next evolutionary step was the creation of the DSS. A term widely used when discussing OLAP solutions. In the 90’s you were more than likely building a DSS system to support OLAP reporting. This was the era of creating reporting systems for areas of the business no longer just focusing on Financial and Sales reporting. It has to be noted that if is was not for products like Essbase, Cognos and front-end tools like Lotus 1-2-3 and Microsoft Excel; DSS systems may not have been so successful. It was the first time that information was delivered to operational roles outside of transactional systems.

Data Warehouse
The data warehouse really is a central repository combining all of these earlier systems. The early data warehouses were third normal form 3NF databases, taking incredible amounts of time to develop. Due to the large nature of early data warehouse, data integration was heavily used in the creation of data warehouses, requiring special ETL (Extract, Transform, and Load) tools and skills - even worse to develop and create reports against. At this time two distinct trains of thought appeared when it came to defining what a data warehouse should be.

The first general definition was that the existing third normal form system, was the only solution to the eternal business questions that a drive a company. Bill Inmon was the main proponent of this concept stating that data warehouse architects could never predict all possible business questions just using summary tables alone. A data warehouse by its very definition should store data at the lowest level possible providing an Ad-hoc query environment. This created the need to store large amounts of data in a structure, which could never be fully tuned for all possible queries.

Many organisations had invested heavily in a data warehouse without fully realizing all the potential benefits. The original goals of data warehouse systems were to create an environment with the easy access, and usability too large amounts of data. Early data warehouses were too complex in nature and as a result fell well short of this goal.

Something had to change.

That happened to be Ralph Kimball and the concept of de-normalisation. Ralph proposed an ingenious way of data retrieval using existing RBMS systems. The main idea was to create low level transactional (facts) tables with attribute (Dimensions) tables relating to each transaction using an integer surrogate key system.

This allowed the database optimizers to summarize large amounts of data more efficiently. Due to the reduction in the number of tables required for a similar query in a traditional data warehouse. This definition describes the Star schema concept from a high-level technical view. Besides the query performance improvement there was a huge business benefit, for the first time business users had a view of data from an information context.

Another benefit of Star schemas was the business context or subject area. Business users were presented a view of transactional data from a business context. Instead of having to join multiple tables with complex joins to produce a query that only DBAs understood. Users where able to write queries that answered business questions for example, how many products did the company sell for this year compared to last year?

Third Normal Form vs. Star schemas
Kimball vs. Inmon is the most common discussion between Data Warehouse Architects, next to which database engine is the best for data warehousing, It maybe one of the few arguments that will never truly die down.

Proponents of 3NF push the robust nature of data warehouses as the corporate repository/information factory that will move with the business and insulate against change with in source systems.

On the other hand Star schemas provide a high performance user friendly reporting environment, which is the ultimate goal of any data warehouse. Even within the star schema approach there are differing views on whether the star should be snowflake (Relational dimensions with de-normalized fact table) or just solely the de-normalized.

The Next Step: Data Marts
Data warehouses became victims of their own success. Organisations started to realize the benefits but could not wait necessarily for a large system to be put in place, hence the data mart.

Although data warehouses were and still are a successful approach to the corporate data repository, the length of time, cost and complexity to develop and deliver data warehouse projects reduce some of the benefits. This was due to the fact that most organizations would change during the course of long projects.

A data mart tends not to suffer from such issues. As the data mart is a focused repository on one area of the business. Organisations are able to see a quick turn around and delivery of a high value repository that a data mart has to offer.

The most common form of a data warehouse will more than likely be a collection of data marts. This is sometimes called a federated data warehouse. Often this is the most successful form of data warehousing.

No matter what kind of system you are building the goals always the same: the delivery of information. Ultimately this kind of system undertaking should always support the business decision making process.

References
Want to know more about Data Warehouses? Check out the following references:

Bill Inmon http://www.inmongif.com/

Ralph Kimball http://www.ralphkimball.com/

Great Resources and Articles about Data Warehousing:

http://www.intelligententerprise.com/

http://www.DMReview.com/

Beginners Guide to Business Intelligence: What is Business Intelligence About?
What is Business Intelligence About?

SSIS Sample Components

2006-02-06T00:57:00.000+13:00

Well is seems the flow of add-ins for SSIS is starting. Microsoft has just release a lot of usefuls add-ins for SSIS. I am a big fan of the Regex component as it is often the hardest to code. Check out the links below.

SQL Server SSIS Sample Component: CalendarTransform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes.

SQL Server SSIS Sample Component: UnDoubleOut
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place, or via the creation of a new output column.

SQL Server SSIS Sample Component: Regex
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.

SQL Server SSIS Sample Component: RTrimPlus
RTrimPlus takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese.

SQL Server SSIS Sample Component: SeeBuffer
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

SQL Server SSIS Sample Component: NullDetector
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

SQL Server SSIS Sample Component: CodePageConvert
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SQL Server SSIS Sample Component: ConfigureUnDouble
ConfigureUnDouble takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.

SQL Server SSIS Sample Component: UnDouble
UnDouble takes a text column, and, removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

This Months Book Recomendation

2006-01-28T22:40:00.000+13:00

Every time someone asks me about getting into BI or learning about data warehousing I point them to one book: The Data Warehouse Tool Kit first Edition by Ralph Kimball. When it comes to a starting point for Data Warehousing this is the book.

It’s the only book I know of that starts at the beginning and asks the questions of why you would want to embark on a Data warehouse project. Ralph uses plain language to describe star schemas and data warehouse design. Each chapter takes a real world industry example such as telecoms, retail, banking and Insurance and demonstrates star schema design. Each chapter takes on different design problems that you may face when building a star schemas.

The first Edition contains a CD with sample stars populated based on each chapter. The later editions do not come with the CD.

It’s a great book for learning about design and understanding the concepts of data warehouses and star schemas. Even if you are not a developer this book is still for you. It makes the connection between business problems and data warehouse design. The great thing about this book is its technology independent.

KB Alerts Feed added to my Blog

2006-01-25T22:51:00.000+13:00

One site I keep an eye on is http://www.kbalertz.com/. It’s a great resource for finding Microsoft known bugs and knowledgebase articles.

I have added feeds for SQL Server 2000 and 2005 to my side bar.

Avoiding Microsoft SQL Server 2005 Deployment Pitfalls

2006-01-20T12:15:00.000+13:00

January has not been a good month for blogging. It seems the reality of coming back to work has taken over my time a bit. Plus I am currently training for a Mountain bike race in March. I came across this interesting installation guide on installing SQL Server 2005. It’s worth a read if you have not installed SQL 2005 before.

Check out: Avoiding Microsoft SQL Server 2005 Deployment Pitfalls

It raises an interesting point, what is the minimum spec to locally develop SQL 2005 applications on. Currently I am running a HP nc6000 with 2 Gig of Ram. It’s the minimum I would recommend to anyone starting development on a laptop.

Any one looking at serious development should always have a development server. A basic development server I would start with a 2 way dual core 64x with 4 Gig minimum. Of course you will still need to size your dev server based on project requirements.

SQL Server 2005 Services Manager

2006-01-04T00:24:00.000+13:00

So its 2006, No more CTPs or Launches to look forward too; Instead it's back to the reality of doing projects with SQL Server 2005.

Like most developers I miss SQL Server Services Manager to start and stop SQL. Mainly because I have a laptop and I use it for everything else as well as development.

To get round this I have created two DOS batch files on my desktop for stoping and starting required SQL services. There are three main services that I have in my batch files:

MSSQL - The services name for SQL Server 2005,
MSOLAP -The services name for Analysis Services 2005,
ReportServer - The services name for Reporting Services.

The Syntax for starting services is NET START command. To start a default instance of SQL from the command line use the following:

NET START MSSQL

For a Named Instance use the following syntax.

NET START MSSQL$InstanceName

Strangely enough to stop Services you use the NET STOP Comand. Listed below are the two batch scripts I use to stop and start SQL Server , Analysis Services and Reporting Services on my Laptop. Feel free to save them in notepad with the .bat extention.

Start Serivces down batch script.

CDREM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services START Script
REM -----------------------------------------------------------------
NET START MSSQL$SQL2005
NET START MSOLAP$SQL2005
NET START "IIS Admin"
NET START w3svc
NET START ReportServer$SQL2005

Stop Serivces down batch script.

CD\
REM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services STOP script
REM -----------------------------------------------------------------
NET STOP MSSQL$SQL2005
NET STOP MSOLAP$SQL2005
NET STOP w3svc
NET STOP "IIS Admin"
NET STOP ReportServer$SQL2005

Microsoft SQL Server 2005 Goodies

2005-12-29T13:06:00.000+13:00

In case you did not realise I am on Holiday, well from work anyway. It’s been great I have spent a whole five days away from my laptop.

Today I have managed to catch up on a few things, checked out what’s new for SQL 2005. I came across a couple of things that are worth a look.

Get Ready for Microsoft SQL Server 2005

Microsoft has put out a series of free training courses that can be downloaded and done in your own time. The great thing about the nine courses is they are free until November 2006. Plenty of time to get your head round Administration, Programming and Business Intelligence features checkout:

Get Ready for Microsoft SQL Server 2005
Feature Pack for Microsoft SQL Server 2005 - November 2005

This is a major release of add ons for SQL server 2005 from Microsoft. It includes handy free database drivers for SAP (preview) and IBM DB2. The Microsoft SQL Server 2000 DTS Designer Components for SSIS, and Datamining Viewer Controls are also included, the list goes on. Checkout:

Feature Pack for Microsoft SQL Server 2005
SQL Server 2005 Business Intelligence Add ons

This is an excellent start on a for SSIS metadata reporting framework. There are two downloads containing SSRS reports and report model that reports against the SSIS log table. There is a dependency analyser for SSAS and SSIS. Checkout:

SQL Server Integration Services Log provider reports using SQL Server Reporting Services

SQL Server 2005 Business Intelligence Metadata Samples Toolkit

If you have not heard there is an Updated Books Online

2005-12-11T22:23:00.000+13:00

Microsoft has released updated Books Online and samples. There are a few cool things in this updated that makes it a must for downloading besides being the most up to date documentation. Rather than repeating Brian Welcker's Weblog on the new updates go and check out it at:
Antics (SQL 2005 Documentation Updates)

To download the latest copy of BOL:
SQL Server 2005 Books Online (December 2005)

Big Plug for Dave Duvarney

2005-12-08T18:36:00.000+13:00

Well I just came across Dave's Blog. He took SQL 2005 ascend course last year in Sydney. He has also written a couple of books on Reporting Services. Well worth keeping an eye on what he is up too.

Check out:
Dave Duvarney Blog
http://bimvp.com/blogs/dave_duvarney/default.aspx

Report Builder and the UDM

2005-12-06T21:57:00.000+13:00

This month is definitely Report Front end development month. After presenting at our cabana session at SQL Server 2005 launch I have been getting busy with Report Builder and Reporting Services 2005. The project I am working on is a small scale reporting solution.

The UDM

At first I had a good look at using Analysis Services as my main repository for storing all my business logic and handling querying against the source system. This has its advantages; it reduces the load against the source system and offers high performance.

The main disadvantage was the complexity of getting data out of an OLAP solution. Of course it is easy with RS 2005 now, but the navigation across the OLAP cube can be a bit daunting to a novice business user.

Report Builder

Then there is Report Builder. Now there is something, an easy to use interface to create reports against any SQL Server/Analysis Services data source. The report writing is done via a report model. The model removes the need to create queries that require knowledge of the data source. This is great for users that don’t want/know how to join tables.

There is a gap in Report builder when it comes to complex reports. Report builder is a limited report designer allowing users to create reports in a controlled interface. For complex reports I always go for Report Designer. There is a not so well document feature that allows you to create Visual Studio Report Designer reports against a report model.

Russell Christopher wrote a neat little piece on using a report model as a data source. Check out: Using a Report Builder Model as a Report Designer data source in SQL 2005

How about both Report Builder and Analysis services?

The best solution is using a combination of Analysis Service cubes and Report Builder to provide an easy access way for users to reports. Using this hybrid approach will enable high performance query and reporting response times with user interface that’s not a pivot table.

It is a challenge to create a Report Model based on Analysis Services. The quickest way is creating a shared data source in reporting services. Follow these steps:

Select the shared data source in report manager

Click on Generate model button

Enter a Model Name, Description and Location Click ‘Ok’

Open Report Builder and select your new model.

Mark Russo has also blogged on creating a report model against Analysis services in BI Studio. Check out: Report Builder Model with UDM

This Months Book Recomendation

2005-12-02T20:58:00.000+13:00

Microsoft SQL Server 2000 Data Transformation Services DTS
by Timothy Peterson

With SQL Server 2005 launched and ready to go it is easy to forget that there will be a lot of DTS 2000 systems ticking over for a long time. This means you may need a good reference book with great descriptions of everything DTS wise.

Timothy did an amazing job describing every DTS Task, workflow and practical solution possible. It is the only DTS 2000 book I have in my bookshelf.

I hope Timothy writes a SSIS book I would definitely buy a copy.

Get down with the feed!

2005-12-01T21:41:00.000+13:00

Ok so it’s taken an email from someone in Sydney to make me finally add a feed to my blog. I am using feed burner to provide a site feed. Now I need to select a viewer for all the other blogs I am reading. Got any suggestions?

For my feed:

Which SQL Server 2005 BI features are you going to use the most?

2005-12-01T21:02:00.000+13:00

Well November has not been a good month for me when it comes to Blogging. I have been flat out doing the Nationwide SQL Server 2005 Launch in New Zealand. It was amazing to meet so many keen SQL Server Developers.

Microsoft was very impressive running three events in Wellington, Christchurch and Auckland. Great support for us Kiwis.

This month I ran a quick poll on ‘What BI features are Developers most interested in’. The results are well inline with what I am doing.

It is interesting to note that only 5% are looking at Data mining, and the shift from ETL(SSIS) to Reporting Services.

This Months Book Recommendation - Impossible Data Warehouse Situations: Solutions from the Experts by Sid Adelman

2005-11-05T17:00:00.000+13:00

Ok so it is time I really recommend a book instead of linking to one in Amazon.

Starting a data warehouse project or in one? Want a worst case scenario guide. Well this book is for you. The book is based on newsgroup posts and real world projects. This book gives you an insight into everything from running a Data warehouse project to managing Tool vendors. I have read this book and found it funny and insightful. It’s the only book I have read that touches on the corporate and political situations that occur on a data warehouse project. It’s most definitely not a book for reading on Client site or at project meetings.

All Things Reporting Services Blog

2005-11-03T18:54:00.000+13:00

Just came across a great Reporting Services Blog By Brian Welcker. Its well worth a look if you are a RS nut. Brian even has a post on moving from CTP to RTM for Reporting Services reports.

Check Out: Direct Reports (Brian Welcker's Weblog)

How to install SQL Server 2005 RTM over September CTP

2005-10-31T19:45:00.000+13:00

I have just installed SQL Server RTM from MSDN. Here are the steps that worked for me when installing RTM successfully over CTP releases.

Note: You cannot have a side by side installation of SQL sever 2005 CTP and RTM, but you can have a SQL 2000 and SQL 2005 using name instances. This also works for Analysis Services as well.

Step 1: Remove SQL Server 2005 CTP. Using the remove tool (Build Uninstall Wizard) from the original CTP installation files. \..\SQLDEV\Tools\Setup Tools\Build Uninstall Wizard\sqlbuw.exe

Step 2: Uninstall SQL CTP or Beta Tools such as the Upgrade Advisor

Step 3: Uninstall .net framework 2.0.

Step 4: Install SQL Server 2005 RTM from MSDN

Huge week for SQL Server 2005

2005-10-30T13:20:00.000+13:00

It’s been a huge week for SQL Server 2005. Microsoft have launched several new initiatives in preparation for November

New BI developer Qualification, I am looking forward to doing this qualification. Not since SQL Server 7.0 has Microsoft created a certification dedicated to BI developers. Have a look at MCITP: Business Intelligence Developer

RTM is now available to MSDN subscribers for SQL Server 2005 and Visual Studio. Check out http://msdn.microsoft.com/sql/

My Top 10 SQL 2005 Features for RTM

2005-10-23T18:42:00.000+13:00

Hello All,

This post is all about the top ten features I am looking forward to in SQL Server 2005. You may be surprised it’s not just SSIS nor is it just about reporting services.

1. SQL 2005 RDBMS: There have been some huge improvements in the core database engine. The two major areas of improvement that bring this area as number one are: Query Performance and Disaster Recovery. These two features I will use on every project. Listed below is functionality in detail that made up the number one ranking.

Query Performance: There are two areas that have improved query performance, Covering Indexes and Partition tables. The major change is in covering indexes. They can have more than 16 columns as long as the total number of page bytes (8k) is not exceeded. This is great for fact tables with a large number of dimension keys that need to be indexed. Partition tables are the other feature that makes a difference to query performance on large tables. This feature not only allows the partitioning of data but indexes as well. Check out: SQL Server 2005: Two Little Known Features That Matter Big! and BOL: Index with Included Columns and also have a look at BOL: Designing Partitioned Tables and Indexes

Disaster Recovery: The Backup and Recovery have been improved in this release. The best new feature is the Online restore. Allowing User access to the database once the redo logs have start. This is an awesome feature allowing the restore time to be reduced significantly. For more info check out BOL: Performing Online Restores

2. Analysis Services: This is really a tie with the database engine. The new features in AS2005 are almost boundless. The all the limitations of AS2000 have been addressed. One of the key changes is around deployment. Everything is now fully scriptable using the new Analysis Services Scripting Language (ASSL). A new query standard XMLA has been implemented which is widely accepted by front end tools. There have been huge changes in the engine room of analysis services. We no longer have to create virtual cubes to meet the need to service mixed grain cubes. Performance and large cubes have been updated too with proactive caching. Listed below is functionality in detail that made up the number two ranking.

Deployment of Analysis Services Cubes: The new ASSL scripting allows for creation and maintenance of Analysis Services Databases and Cubes. Check out BOL: Analysis Services Scripting Language (ASSL)
Performance Enhancements: Analysis services is fast becoming the reporting back end for high performance reporting. Features such as Proactive Caching, and the improvement in storage models allows users a fast query response against OLAP and Relational data sources.
Analysis Enhancements: There are several key enhancements in the area of analysis. Key performance indicators (KPI), are now stored within the OLAP cube. Drill through has been enhanced and translations have been created to help developers with international users.
Dimension: One of the limitations that I continue to struggle with in AS2000 is the use of member properties to extend dimension usability. Of course this has been addressed in AS2005. No longer are dimensions based on hierarchies instead they are based on attributes. In line with most reporting requirements. Hierarchies are instead based on attributes.
UDM (AS2005 OLAP Cube): One of my biggest gripes with AS2000 was the inability to handle mixed grain cubes. Generally relating to the multiple fact tables within one cube requirement. Of course we could use virtual cubes to create the big super cube solving this issue. In the case of AS2005 this is no longer an issue. Through the use of measure groups we are able to handle multiple fact tables with different grains. This is further enhanced by dimensions being allowed to handle many to many relationships with fact tables.

3. Reporting Services: RS2005 is jam packed with new features that just makes it better to write reports than the last release. New controls have been enabled for multi parameter selection. Calendars controls for selecting date parameters, and of course direct printing. Listed below is functionality in detail that made up of number three ranking.

Multi Parameters: One of the missing features in RS2000 was the ability to select multiple values in a list. This has been resolved in RS2005 with a check box list option for parameter selection.
Calendar Control: One of my favourite features in this release. This is automatically enabled as long as your parameter is of a data type “Datetime”. The control will automatically appear when viewing reports.
Direct Printing: The printing functionality in reporting services has been greatly enhanced. ActiveX control has been created to enable printing from within the browser.
MDX Query Designer: Ever had to create a report against an OLAP cube in RS2000? It was not pretty as you had to create MDX statements manually. RS2005 provides you with a query builder for MDX.

4. Report Builder: This is the first attempt by Microsoft to enable report users to create reports from relational database sources and not just an OLAP cube. Report builder was a product called ActiveViews that was purchased by Microsoft sometime ago (See: Microsoft Adds ActiveViews to Free SQL Reporting Services Add-On). This is the first version from Microsoft and currently only supports SQL Server and Analysis Services. This was the product that I really enjoyed presenting at Tech05. It is a great product for the report users to create reports without having to understand the underlying database structure. For an overview Check out BOL: Working with Ad Hoc Reports using Report Models

Listed below is functionality in detail that made up of number four ranking.

Report Models: The major component of the report builder is a business model that allows report users to create reports. This is the central repositiry for creating: common business calculations, Drillthrough paths and complex queries. Just like integration services and analysis services, report models are based on a data source views. You are able to hide database complexity by creating made queries for complex joins and data source calculations.
Ad hoc Reporting: Once a report model been created and published. Any user can navigate the underlying RDBMS without having to join any tables. It’s a great tool for reducing the complexity of creating reports. Reports can take three forms cross tab, table, and my favourite charts. All reports are published to the report manager portal. If the report model supports Drillthrough functionality users are able to navigate the data model.
Report Manager: Drillthrough functionality is one of the best things this happened to reports in reporting services. Report builder reports also support floating table headings. With scrollable headings large reports can be displayed in the browser in a greatly improved format.

5. Integration Services: I liked DTS, but I really love integration services. This should really have a top ten feature list of its own. Perhaps I will have to do one after this blog. The key feature I’m really interested in are around reusability of data in the data pipe. Its a great feature and one of the biggest changes. Other features include Fuzzy Lookups, Slowly Changing Dimensions Transformation, Loops, the list goes on. Listed below is functionality in detail that made up of number five ranking.

Control/Data Flow: One of the first features that most DTS developers will notice when using integration services. The control of flow allows developers to seprate workflow from transfromation tasks. Transfroming with DTS 2000 was a read once write once operation. Data flow allows you to read data in and transform multiple times(read once write mutlipule operations). This reduces disk I/O but ultimately requires more memory. This results in a huge performance gain with reduction of disk operations. Check out:BOL: Integration Services Programming

Fuzzy Lookups: Data quality affects every data warehouse project. A new transformation called fuzzy lookups is a tool to help address this problem. The functionality allows you to apply some data mining techniques to profile data that would normally fail on load.

Slowly Changing Dimensions Transformation: OK why did I not rank this features number one. By far this is one of the cooler features in integration services. This transformation allows you to handle type I and type II slowly changing dimensions. This is one of the things I’ve had to create my own architecture in SQL server 2000 to handle. Check out :BOL: Slowly Changing Dimension Transformation

Loops: What can I say all of us have wanted to do loops with DTS 2000, it is now standard feature with an integration services. The best example of this is looping through a set of text files and loading them with one data transformation.

6. Data Mining: Seven Data mining Algorithms! OK hands up who has done data mining with AS2000? I myself have only done it on three projects. The biggest limitation with data mining in 2000 was a lack of a work bench to test your mining hypothesis. Then if you want to display the results you had a limited number of display options. Needless to say data mining has been greatly improved in this release. Microsoft research has continued their quest in the development of new algorithms for us to use. The two algorithms provided in SQL server 2000 have been greatly improved. If the statue with data mining to have this tutorial for books online: BOL: Creating a Forecasting Mining Model Structure (Data Mining Tutorial)

7. T-SQL: New SQL functions - finally I can rank. T-SQL has been approved with inclusion of new features such as CLR support, and new text Data types. Check out: BOL: Transact-SQL Data Types

Rank(): I can honestly say I have need something like this for a long time. With this function I can rank my results with a rank number. I can even rank with a partition of the result set. Check this out if you use Reporting Services and need to rank the results I bet you will use this function. See: BOL: RANK (Transact-SQL)
Replacement text data types: The search limitations of using text and ntext was handled by using the full text search engine. Two new data types have been created varchar(Max) and nvarchar(Max). The new data types can be used to in the where clause is standard conditions.
XML data type: XML is now treated with its own data type. The new data type fully supports XQuery. See: XQuery Against the xml Data Type

8. Database Tunning Advisor: Now we can tune the whole database. No really the whole database. DTA can be used in a similar way to the index tuning wizard only this time it looks at the entire database right down to the file groups and database table structure. It is well worth a look if you are trying to understand some of the new performance enhancements SQL Server 2005 has to offer. To get started with the database tuning adviser, check out:BOL: Database Engine Tuning Advisor Tutorial

9. User Interfaces: Ok it’s a bit lame but the complete refresh of the UI was required. And this time someone listen to us developers and created an integrated workbench for the development of business intelligence projects. The old user interfaces have been replaced by two new applications, the SQL server management studio and business intelligence development studio.

SQL Server Management Studio: a cross between the old enterprise manager and query analyzer. To it as a single point for managing all server instances this includes Reporting Services, Analysis Services and Integration Services. Management studio is also the main query engine for SQL server.
Business Intelligence Development Studio: fully integrated into visual studio 2005, developers are able to create end to end business intelligence solutions. This a single point for developing integrated services, reporting services, and analysis services projects.

10. Upgrade Advisor: Need to upgrade? Check out your current SQL server 2000 instance before installation. The first step should be the upgrade adviser. This can be found on the installation CD and is not installed by default and can be run independently of SQL server 2005 applications. It requires .Net framework version 2.0. You may wish to migrate instead of upgrading; SQL Server 2005 can be run as a side by side installation with 2000.

To download the CTP version of Upgrade Advisor check out: Upgrade Advisor - Community Technology Preview (CTP) September 2005

This list is based on the September CTP so it’s missing DB Mirroring which was in my top ten. I haven’t begun to talk about the new BI products such as the new scorecard accelerator that will have to be in another post.

What are you’re top ten features you’re looking forward too in SQL 2005?
Don’t be shy feel free to leave a comment as I have enabled anonymous user comments.

SQL Server 2005 Books Online

2005-10-11T23:07:00.000+13:00

I have finally found the SQL Server 2005 Online books at MSDN. I have been looking for it for ages. Its a great resource to refer for Newsgroup and Blog posts.

Check out at: http://msdn2.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx

Myles

Getting ready for SQL Server 2005

2005-10-08T20:25:00.000+13:00

Well it’s not long now till RTM, in fact its 5 weeks or so to launch. Just enough time to go through the last CTP before November. I have downloaded the September CTP release from http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

After the download was complete I hurriedly installed the CTP on my laptop. Here are few comments on what I found with the latest CTP.

There are few changes in the User Interfaces here and there. So be prepared to look for the odd thing. Most of the UI changes are great at reducing the complexity of some areas. The main two I have noticed are Report Builder and SQL Server Management Studio.
Apps consolidation has also featured in this CTP. A lot of wizards have been moved to SQL Server Management Studio. So if you are looking for the Analysis Services upgrade wizard like me try the following:

Connect to your AS2005 instance in to SQL Server Management Studio and right mouse click on the AS server icon in the object explorer.
Select the Migrate Analysis Service database

Database mirroring one of my favourite features has been parked for a service pack. It is still available in RTM but will require some low level configuration and will not be supported. For more info see this article http://www.entmag.com/news/article.asp?EditorialsID=6926
This is the only CTP I have installed on my host OS and not a VPC. The only problem I have come across is development disconnected form the network. The problem is around connecting and browsing cubes. This results in an OLE DB error.

Well that’s about it for now. Over the next couple of posts I am going to cover some of the features I am really looking forward to.

Till next post,

Myles

The Status of Database Mirroring in SQL 2005

2005-10-06T11:03:00.000+13:00

I just came across this article about Database mirroring. It appears that it will not be available/hidden in the RTM release in November. The link to article below contains Tom Rizzo comments.

http://www.entmag.com/news/article.asp?EditorialsID=6926

This Blog is dedicated to Jim

2005-09-26T23:50:00.000+12:00

Who rightly points out I have not blogged in weeks, it’s not been from the lack of wanting to put something down, and I have just been a bit busy with work.

So Jim sits next to me at work in a completely unrelated field of mobility. And seeing how Jim does not work with BI systems, I thought it would be good to have a look at the features that SQL Server 2005 has for Mobile applications. While I am at I will put a blog about it.

First off there are no direct BI features for SQL Mobile applications. In fact there are none. Accept for the standard Reporting Services web services that can be customized.

So what are the Mobility Features?

First off a new DB that replaces SQL CE. SQL Management Studio is a single point for maintaining and developing SQL Mobile databases. There is full integration with Visual Studio. It’s a huge improvement over CE.

Product Overview Getting Started
http://www.microsoft.com/sql/ce/productinfo/SQLMobile.mspx

Development Centre for SQL Mobile
http://msdn.microsoft.com/SQL/2005/mobile/default.aspx

Well I know that’s a bit short and sweet, next post I will have a bit more of a BI favour.

\Myles

Introduction to the Unified Dimensional Model (UDM)

2005-09-07T23:15:00.000+12:00

I was talking to a colleague about Analysis Services and how awesome the Unified Dimensional Model (UDM) is. I then realized I have not blogged in ages about anything useful. So I have pulled together some of my thoughts about the UDM.

I was really introduced to the UDM on the Yukon Ascend program last year in Sydney. At the time I was still trying to find out what the UDM was in Analysis Services 2005 (AS2005). The simple answer is “The UDM is a Cube in AS2005”. Ok that may be glossing over the UDM a bit, but you will not find the UDM in AS 2005. There is no CREATE UDM function in AS2005. An AS2005 cube ties all the UDM features together.

The UDM, I mean cubes provide the ability for reports (OLAP, Reporting Services) to be generated against standard OLAP cubes as well as Relational data sources. Queries can be optimized to either run against aggregates from OLAP storage or directly against the relational data sources offering a high performance reporting environment. Reporting services has been extended too with a UI to create MDX queries (something that was sorely missing in SQL 2000).

The feature list for the UDM is huge. Data mining has been extended in AS2005 with seven algorithms. New KPI functionality has also been introduced in this release. The list goes on. I have put together some Useful links to UDM and Analysis Services 2005 resources.

Introduction to the UDM
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/IntrotoUDM.asp

Analysis Services 2005 Processing Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Enabling Drillthrough in Analysis Services 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

Introduction to Data Mining
http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2dm.mspx

SQL Server 2005 Reporting Services (SSRS)
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

I have started my research in earnest into how I am going to use the UDM on Business Intelligence projects. I hope this is a good start for any one looking at the UDM.

\Myles.

TechEd 2005 Auckland NZ Session slides

2005-09-07T23:12:00.000+12:00

Hello,

My session slides have now been posted on to ww.microsoft.com

For my session on Analysis Services 2005 migration tips see:

http://download.microsoft.com/download/0/9/b/09bb6510-535a-4fe6-be02-8af8762b0acc/SQL312.ppt

For Ad-hoc reporting with report builder see:

http://download.microsoft.com/download/e/2/6/e2631eea-9a8d-484c-ba43-ac2e5be86396/BIN316.ppt

\Myles

TechED Photos

2005-09-06T22:51:00.000+12:00

Hello All,

Mark who attended my morning session has sent me some pics of me getting ready for the migration to AS 2005.

Thanks Mark.

\Myles

What I got out of TechED

2005-09-01T11:01:00.000+12:00

Wow TechED is over, three days of sessions and two presentations of my own. It was a full on event for NZ IT scene.

Besides presenting at TechED I did get to a couple of sessions. Looking back at them I took something from each session, listed below are my thoughts.

BIN301- Cube Design in Analysis Services 2005 - Best Practice for Performance and Functionality - Richard Lees.

This was the only session on AS 2005 that I was able to attend this year. Richard covered the UDM in detail and gave us a run of all the client tools for AS 2005. The best was the review of the proactive caching for loading cubes and the use notification services for automation of cube processing.

The UDM really does have a large amount of features around query performance. Something I am going to look into in more detail on this blog at some stage.

BIN310 - SQL Server 2005: End-to-End Part 3 (Analyze and Act) - Thierry D'Hers

It was great to get to meet Thierry. I spent a bit of time with him on is brief say here in NZ. His insight into the direction of the MS BI platform was very informative. Thierry’s session reviewed all the SQL 2005 features with a take on MS direction for the product range.

DBA313 - Architecting a Large Scale Data Warehouse with SQL Server 2005 - Doug Barrett

Doug took us threw some of the key DW features for creating a DW on SQL 2005. The session was more of a traditional approach to DW architecture. He cover table partitions and loading with SSIS packages.

I realy feel that any new BI projects on the MS platfrom will be based around the UDM.

Microsoft is going to post all TechED presentations on their NZ site latter this month. I will post a link to these sessions on blog.

Till next time

Myles

Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities

2005-09-01T08:08:00.000+12:00

Hello All,

I came across this neat MSDN Article on Data Cleaning using SSIS. It was raises some great points on how to use the new Fuzzy transformations.

Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities

http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/05/09/SQLServer2005/default.aspx

\Myles

Beginners Guide to Business Intelligence: What is Business Intelligence About?

2005-08-23T17:30:00.000+12:00

I have decided to do a beginners guide to BI on my blog. I thought this would be a great way to tie all the resources I use and give you a good idea for an approach to BI projects. I am starting off with a question that needs to be answered.

What is Business Intelligence About? Decisions of Course

I often find myself in situations where I have to try and explain Business Intelligence. It’s not that easy to define BI in a universal definition that everyone agrees with. BI is as much a collection of technologies and principles as anything else.

So here it goes,

‘Business Intelligence is about taking the guess work out of the decision making process.’

That’s right it’s not about technology or a wonderful new process. It’s about the management decision making process that occurs in every business. To demonstrate this in more detail lets review what the Management Decision Making Process (MDMP) is about.

The MDMP was introduced to me on my first data warehousing project by Peter Nolan the architect of the project. He took me through the concept of why you want to start a BI project in the first place. He posed a simple question, ‘How is Business?’

There are four primary drivers that make up a cycle that form the MDMP. Consider each factor as deliverables forming a BI platform. The diagram below shows four primary drivers for the MDMP.

Made an Investment – Every organization makes an investment either in a product or a service. The tracking of the investment is the first driver in the MDMP. For example a product stock keeping or a resource schedule report allows a compony to track its resources.

What Happened? – The second driver covers the result of the investment. Did the investment payoff, did we make a sale? For example a sales report allows business to track the success of the investments.

Why did it happen? – The third driver is to analyse the success of the investment. Organizations use OLAP analysis Ad-hoc reporting to drill down to the reasons for the performance.

What if? – The fourth and final driver and the most difficult to implement. Organizations are constantly looking for new opportunities to invest, questions like; what is the impact of further investment? What if we gave 10% further discount? Looking for new opportunities with data mining and forecasting techniques helps answer this question.

So how do you deliver MDMP?

When starting a BI project you must set firm goals around the end deliverables. A great place to start is taking a single business process and mapping to the MDMP. (Selecting a business process is an entirely different subject for a blog.)

Once a process has been selected a solid business case been derived using the MDMP. The four primary drivers of MDMP will allow you to set clear goals for delivery that the business can understand.

SQL Server 2005 vs. MDMP

Reviewing the SQL Server 2005 product against MDMP shows how much BI functionality Microsoft has release in the version. Below is a table showing the reporting functionality SQL Server 2005 has built into support the MDMP.

Conclusion

Basing your BI project on the MDMP you have a common point of view across the business and technology requirements are key to delivering a successful project. For further information on the MDMP check out the resources below.

Resources

Getting Started and Finishing Well By Peter Nolan
http://www.intelligententerprise.com/010507/print/webhouse.htm

Where is Peter?
http://www.peternolan.com/

Beginners Guide to Business Intelligence: What is a Data Warehouse?

Download SQL Server 2005 now!

2005-08-19T13:50:00.000+12:00

Hello All,

Again I am completely snowed under with work so I have not written any blogs since Monday.

I am currently working on feature for this blog and I will have it out soon.

Now is a good time as any to get started with SQL Server 2005. Download the current BETA CTP from http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

Good Luck

Myles

What I am going to see at TechEd 05

2005-08-12T15:41:00.000+12:00

Hello All,

Well its 17 days out to New Zealand TechEd 05 and I can’t wait.

I know I have not blogged for a long while. This is due to work commitments and TechEd prep. I am still in the middle of a large Data warehouse project. It’s been running for the last 8 months and nearing completion.

There are a few TechEd sessions I am really looking forward to seeing. I thought it would be a good idea to share them with you. Below are the key sessions I don’t want to miss.

BIN303 - Exploiting Data Mining in SQL Server 2005 - Richard Lees

BIN304 - SQL Server Integration Services: Deploying, Managing and Loading a Data Warehouse - Tony Bain
BIN310 - SQL Server 2005: End-to-End Part 3 (Analyze and Act) - Thierry D'Hers

DBA313 - Architecting a Large Scale Data Warehouse with SQL Server 2005 - Doug Barrett

I haven’t listed the times here as the schedule may change for the latest TechEd schedule see: http://nigelp.members.winisp.net/schedule.htm

Bye for now.

Myles

TechEd 05 Preparation – Project Real

2005-08-02T04:31:00.000+12:00

Hello All,

While I am doing prep for TechED 05, I came across a couple of really good public links to project Real. For those of us that are going to be converting a lot of SQL 2000 Clients to 2005 it's on the 'must read list'.

Check Out:

Project REAL: Technical Overview
http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Project REAL—Business Intelligence in Practice
http://www.microsoft.com/sql/bi/projectreal/default.mspx

Happy Reading

Myles

News Group Post: Slowly Changing Dimensions Type 2

2005-08-01T12:17:00.000+12:00

Note: This is a response to a news group posting. Due to formatting problems I have reposted the response on my Blog. Don't worry I don’t intend this again.

Rico asks how I can implement type 2 changes in a dimension.

Hello Rico,

Type 2 is relatively easy to implement, but it can be a pain for users to understand and use.

Implementing Type 2

Generally I use a set of standard flags to track the changes in the dimension. Each flag is used to determine the state of the record. I am assuming that you are using Surrogate keys in your dimensions and Fact tables.

Date From – The date the record arrived in the dimension
Date To – The date the record is deemed to be changed
Current Flag – The State of the record Y or N

With these flags you can track changes of the dimension at the lowest level such as employee changes.
e.g.

Employee Dimension

You will have to come up with at change capture process. If you have type 1 deployed already it would be the same logic for identifying the changes, with the exception of updating old records and a creating new records in the dimension. Remember you will have to update your surrogate key lookup in you fact table build to load with the current dimension record. I.E. “WHERE Current Flag =’Y’ ”

Implementing Type 2 without Flags

To be honest I have not tried this method but it worth considering if your Business users have problems reporting using the Type 2 Flags. Rather than having one Employee Dimension create a series of mini dimensions against the fact table. This would allow you to track changes against the fact record instead of the Dimension.

E.G.

Employee Dim is broken into three new dimensions (Dim Manager, Dim Employee, and Dim Job Title) as shown in the diagrams below

There is a HUGE draw back to this you end up a large amount of dimensions and a really wide fact table if you have to include a lot of them.

Hope this gets you started.

Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

TechEd 2005 Sessions Schedule

2005-07-30T23:59:00.000+12:00

Hello All,

My Sessions have now been confirmed. Both sessions are on the last day of TechEd (Day 4: Wednesday 31st August)

I am presenting the following sessions,

9.00am - 10.15am Session 12

Project REAL and Tips for Migrating from SQL Server 2000 Analysis Services to SQL Server 2005

3.45pm - 5.00pm Session 16

Ad Hoc Reporting with Report Builder: Extending the Capabilities of SQL Server 2005 Reporting Services

Check out the link below for the TechEd sessions schedule

http://www.microsoft.com/nz/events/teched/sessions/default.aspx

See you all there!

Myles

TechEd 2005 – Auckland New Zealand

2005-06-11T05:30:00.000+12:00

Just a quick post to let you all know, that I am presenting two sessions on the next version of SQL Server 2005.

I will be covering the advanced features of Report builder New in SQL Server 2005. Also I will be covering migration tips from Project REAL for migration of Analysis Services 2000 OLAP cubes to AS 2005.

Once I have a confirmed schedule I will publish my session times.

Myles

SQL Server 2005 - Formally Known as Yukon

2005-06-10T05:57:00.000+12:00

Ok it’s been along wait; in fact it’s been a very long wait. To be truthful when I joined the Yukon Beta 1 program in the UK, I knew Yukon was special. SQL Server 2005 is jammed packed with new features with a large number around Business Intelligence.

SQL Server 2005 is the first major release of the SQL server product line since SQL server 2000. Microsoft has reviewed the editions of SQL Server to provide cost effective solutions. Have a look at the following new editions for SQL Server 2005.

SQL Server 2005 Features Comparison
http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx

SQL Server 2005 is the only platform to offer end to end BI. From Extract, Transform, Load processes with Integration Services through to Information Delivery with Reporting Services and Report Builder. Not only will my development time decrease I will be able to deliver a cost effective solution without having to integrate multiple vendor products.

I have put together are few links to help you get started.

Get Ready for Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/learning/default.mspx

SQL Server 2005 – Business Intelligence
http://www.microsoft.com/technet/prodtechnol/sql/2005/BI/default.mspx

SQL Server 2005 Business Intelligence Web Casts
http://www.microsoft.com/events/series/sqlserverbi.mspx

Getting Started with DTS 2000

2005-06-06T18:30:00.000+12:00

Hello All,

I thought it would be a good idea to list some useful sites for first time users of Microsoft SQL server 2000 DTS.

Check out the following links. These will help you get started using
DTS as an ETL development platform.

DTS Tutorials

The link below is a great overview of DTS designer

http://www.sqldts.com/default.aspx?278

DTS white papers

DTS Programming Techniques Used in Microsoft SQL Server Accelerator for
Business Intelligence

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dts_ssabi.asp

Best Practices for Using DTS for Business Intelligence Solutions
(updated web version)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_busintbpwithdts.asp

Good Luck,

Myles

Hello World

2005-06-05T18:04:00.000+12:00

Hello All

Welcome to my small part of the web. I have started this blog to share my thoughts on Business Intelligence and its applications using Microsoft Technologies primarily SQL server, and of course anything else that interest me.

But first a bit about me.....

I have been working in the field of Business Intelligence for the last seven years. During that time I have worked in New Zealand, Australia and the United Kingdom. My focus has been delivering BI solutions using SQL Server 2000. I have completed numerous projects across multiple industries. I am currently living back in New Zealand leading BI projects in Auckland.

Mainly I hope to tie all the key web resources I use and publish my thoughts on the direction I am taking. Of course I want to hear what you are doing. I guess that’s the great thing about blogging everyone can have a say.

So stay tuned,

Myles