A long time ago - February and March 2006 - Eugene Asahara has posted two very intersting articles about

• KPI Cause and Effect Visio Graph
• KPI Cause and Effect Graph - A View from the UDM into Relationships Between Information Workers

The "KPI Cause and Effect Visio Graph" is a very useful visualization tool to diskuss KPI´s with the prospective users whereas the "KPIRelationships sample application" is a very smart tool to control if all perspectives, KPI´s, etc are developed and have the right definitions.

This two articles are a really must-read and Eugene also provides the tools for download - so check it out!

This weblog is sponsored by FITS.
We support PASS Germany!

Today at the BI Conference in Seattle Jeff Raikes from Microsoft has given a first official impact - just slides - on the next SQL Server version.

I have seen a new feature called Change Data Capture (also mentioned in the datasheet) in the session "PL208 Data Warehousing with Microsoft SQL Server 2005" presented by Gopal Ashok, Program Manager with the SQL Server Product team, which was quite impressive.

Ever dealt with the problem of having mass data in a table and no "real" marker to identify the specific rows for incremental data operations like inserts, updates and deletes?
The Change Data Capture should ease your pain by creating a kind of audit table to watch data modification without the need of programming stored procedures, triggers and so on.
I am extremely curious how this feature proves in real life scenarios. And I am also a little bit surprised if the SSIS team will not bring up some new tasks to enable the use of the audit table of the Change Data Capture - or do we still need to develop the handling concerning load cycles, deleting of successfully loaded records, etc by our own? A kind of automatic delta queue mechanism and handling like in SAP systems would be great!

Tomorrow Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft will have a keynote at the morning - I hope that he will show some more live impressions.

You will find the official press release here.

And there also is a information website online.

Regarding to the press release, "SQL Server “Katmai” is scheduled to be delivered in 2008" - but there is no information about CTP´s available on the website yet.

This weblog is sponsored by FITS.
We support PASS Germany!

Vera Noest provides a really great link collection of ressources which will be very usefull while doing troubleshoting.

Vera also provides troubleshooting tips for Terminal Server and Citrix.

Worth reading - check it out!

This weblog is sponsored by FITS.
We support PASS Germany!

Darren Gosbell has posted a very interesting article about querying SSAS and use the result in the relational part of SQL Server.

I have used it in a project to retransfer data which was calculated due to a parent child dimension containing items for a financial analysis.
Calculating this via the relational data would have been a mess, because of having to rebuild the whole calculation from the bottom to the top of the hierarchy.

All you have to do is to define a linked server in the first step:

          USE master GO /* Addnew 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', --
analysis server name (machine name)  @catalog='Adventure
Works DW'-- default catalog/database

After successfully defining the linked server you will be able to query any SSAS cube based on your security like this way:

          SELECT * FROMOpenQuery(linked_olap,'SELECT
--measures.members {Measures.[Internet Sales Amount]} ON COLUMNS, [Date].[Month].members
ON ROWS FROM [Adventure Works]') 

While writing this blog entry I tested it on my notebook also - and was very astonished that querying the cube does not work, while testing the linked server worked well.
I am getting an error message like:
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.3" for linked server "linked_olap" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSOLAP.3" for linked server "linked_olap"."

Maybe it depends on Windows Vista, even with the security set in the right way and also running SSMS as an administrator...

This weblog is sponsored by FITS.
We support PASS Germany!

Interested in all the new things in SP2? Here is a complete list.

And this is the complete download site with links for the SP2 itself, a new BOL, new Samples, new Feature Pack and especially the links to SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies and SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007.

To avoid a restart after the installation of the SP2 you should stop the neccessary services which could be done with the following commands on the command line - if you have used the standard instances:

net stop MSSQLSERVER
net stop MSSQLServerOLAPService
net stop msftesql
net stop MsDtsServer
net stop ReportServer

After the installation the "SQL Server 2005 User Provisioning Tool is started automatically if you use Windows Vista. As you probably know, Windows Admins are not automatically also SQL or AS Admins  - this tool enables to set the right privileges to the administartor accounts.

But do not forget to start the services before you try to set the privileges...

If you want to add a new account you will have to start the Provisioning Tool again, but unluckily it is not added to the Start Menu, you will find it at:
C:\Program Files\Microsoft SQL Server\90\Shared\SqlProv.exe - assumed that you have a standard installation and an english version of MS Windows Vista.

This weblog is sponsored by FITS.
We support PASS Germany!

I have encountered a system error deploying a SSAS project when I changed the deployment from my deployment server to a new production server. As you can imagine, the common case that really could bring you made is when you successfully deploy a project into a development server but you fail to deploy the same database into a production server and the error message is too generic and it hides the real cause of the problem that is very hard to find:

I looked for the error in the connection strings, I guess network or Active Directory problems...but every change attempt fails with the same error message: "Systemerror: ."

The solution was a problem with a role that references a membership of a local user of my development server, that fails because the role member name can't be resolved by SSAS production server. Thanks to microsoft for the great translation from "Can't deploy role xyz..." to "Systemerror: ."  ;-)

This weblog is sponsored by FITS.
We support PASS Germany!

Definition from the SQL Server 2005 Product Guide:

"Analysis Management Objects (AMO) allows client applications to access the range of administrative commands and capabilities that are available to Analysis Services by using an object library that can provide object-level validation capabilities. This is an alternative to manually generating both the DDL scripts for Analysis commands and the often-lengthy contents of the ASSL ObjectDefinition element. Applications that use AMO can either connect and work directly with objects on an Analysis Services instance, or they can create such objects without an existing connection and persist the metadata for later deployment. AMO also “wraps” Analysis Services Scripting Language (ASSL) commands and elements."

Microsoft SQL Server 2005 Management Objects Collection from the Feature Pack for Microsoft SQL Server 2005 - April 2006

Introducing AMO Concepts

Readme for AMOBrowser Sample

AMO Security Classes

Programming AMO Security Objects

Analysis Services Concepts and Objects

Permissions and Access Rights (SSAS)

This weblog is sponsored by FITS.
We support PASS Germany!

The "Microsoft Architect Insight Conference" was held on 22 - 23 March 2006 and the slides are now available for download.

I would highly recommend that you read the PPT about Service-oriented Business Intelligence (SoBI) by Sean Gordon, Robert Grigg, Michael Horne, and Simon Thurman, which brings up some really good new approaches.
The authors also have published an article, which contains a more detailed descriptions and is available online.

And here are two additional links extracted from the article page:

• Data on the Outside vs. Data on the Inside
• Information as a Service: Service-Oriented Information Integration

You also should have an eye for the slides of Eric Nelson, who tries to explain that there are seven new servers in the SQL world...

This weblog is sponsored by FITS.
We support PASS Germany!

Chris Webb has blogged about the "Business Intelligence Architecture and Design Guide" downloadable at gotdotnet.

I agree with Chris, this is not really new stuff, but I personally think it is good to have informations like this in a kind of "complete package".

Extract from the website:
"The patterns & practices team is please to announce the availability of Architecture and Design Guidelines for Business Intelligence Applications. This exploratory community project provides prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. This guide is intended for software architects and developers who are developing Business Intelligence applications on the Microsoft .NET Framework using SQL Server 2005 - Integration Services and Analysis Services. Being an exploratory community project - anticipate gaps and scope for improvement. For more information see: http://codegallery.gotdotnet.com/biguide."

This weblog is sponsored by FITS.
We support PASS Germany!

PASS Germany is doing a BI BootCamp again!
[Even if it is an event in german language I am writting in english - please spread this news around in your blogs though interested community members have the chance to participate]

Better, bigger, more content, more networking, more speakers than 2005 - if even possible! :-)
And also a suprising night session again - stay tuned!

If you want to register, please use this link.

Here is the eight-slide-presentation for download:
PASS BI-BootCamp2006_8-Slides.pdf (190,66 KB)

This weblog is sponsored by FITS.
We support PASS Germany!

The SQL Server 2005 Service Pack 1 is available now, which updates your SQL Version to Build 9.00.2047.

• download english version
• download german version
• List of bugfixes
• List of new features

Microsoft also now has published the "SQL Server 2005 Express Edition with Advanced Services", which contains Reporting Services now, and the "Microsoft SQL Server 2005 Express Edition Toolkit", which contains the Business Intelligence Development Studio and a Software Development Kit!
SQL Server 2005 Express now really is worth a look and it is for free, you will be able to download it here:
http://msdn.microsoft.com/vstudio/express/sql/download/

This weblog is sponsored by FITS.
We support PASS Germany!

Project REAL is a cooperative effort between Microsoft and a number of technology partners in the business intelligence (BI) industry to build on actual customer scenarios to discover best practices for creating BI applications based on SQL Server 2005. The term REAL in Project REAL is an acronym for Reference implementation, End-to-end, At scale, and Lots of users.

This page gives tons of informations and really is worth reading carefully and staying for hours!

This weblog is sponsored by FITS.
We support PASS Germany!

Microsoft offers free e-learning courses for SSAS, SSIS and SSRS as a kind of delta-courses to enable an update of your knowledge to SQL 2005.

Course 2942: New Features of Microsoft® SQL Server™ 2005 Analysis Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into five modules.

• Exploring SQL Server™ 2005 Analysis Services Core Concepts and Architecture
• Designing and Deploying a Unified Dimensional Model
• Administering SQL Server™ 2005 Analysis Services
• Programming in SQL Server™ Analysis Services 2005
• Working with Data Mining Solutions in SQL Server™ 2005 Analysis Services

Objectives:
At the end of the course, students will be able to:

• Build a Cube in SQL Server 2005 Analysis Services
• Administer SQL Server 2005 Analysis Services
• Program in SQL Server Analysis Services 2005
• Work with Data Mining Solutions"

Course 2943: Updating Your Data ETL Skills to Microsoft® SQL Server™ 2005 Integration Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into four modules.

• Exploring SQL Server™ 2005 Integration Services Architecture and Core Concepts
• Designing and Debugging SQL Server™ 2005 Integration Services Packages
• Rich Functionality in SQL Server™ 2005 Integration Services
• Extensibility

Objectives:
At the end of the course, students will be able to:

• Design and debug SSIS Packages
• Configure and deploy SSIS Packages "

Course 2944: Updating Your Reporting Skills to Microsoft® SQL Server™ 2005 Reporting Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into three modules.

• Exploring the Features and Core Concepts of SQL Server™ 2005 Reporting Services
• Designing Reports Using Report Designer
• Deploying and Managing Reports
• Working with Ad Hoc Reports

Objectives:
At the end of the course, students will be able to:

• Design Reports Using Report Designer
• Deploy and Manage Reports
• Work with Ad Hoc Reports"

This weblog is sponsored by FITS.
We support PASS Germany!

As Chris Webb posted here, there is a "SQL Server 2005 Business Intelligence Metadata Samples Toolkit", which brings up "Tools and Sample reports for Metadata Dependency Analysis of SSIS, SSAS", available for download for free.

Extract from the website:
"Microsoft SQL Server 2005 BI Metadata Samples provides tools and samples for analysing dependencies within and across SSIS and SSAS. The source code for the tools is provided.
The following are the components of this pack:
DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
Analysis Services Samples : sample SSAS packages that can be analyzed
Integration Services Samples: sample SSIS packages that can be analyzed
Report Model Samples (Report Builder): report builder model
Reports: sample reports built from metadata store
SQL: SQL script for creating metadata store"

Extract from the website:
"This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis."

This weblog is sponsored by FITS.
We support PASS Germany!