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!

15. Treffen der Regionalgruppe Ruhrgebiet am 10. April 2007 ab 18.00 Uhr.
Veranstaltungsort: ELBA Buerosysteme GmbH & Co. KG in Gelsenkirchen - Anfahrtsplan & Adresse

• Abstimmung weiterer Aktivitäten, Veranstaltungen und Termine der Regionalgruppe (alle)
  
• Themen 2007 (alle)
  
• Einführung in MDX (Ahmad Fani)
  
• MDX relational nutzen (Markus Fischer)
  
• [...]
  
• weitere Themen ...

You will find a PPT (PASS_RG_Ruhrgebiet_20070410_MDX.ppt (823 KB)) and Examples for MDX-Statements (Tutorial.mdx (9,21 KB) Tutorial2.mdx (1,46 KB)) attached.

The Slidedeck is a merger of Slides from:

• intellinet called "Atlanta.MDF - MDX Overview"
• Microsoft TechEd 2006 "SQL Server 2005 Analysis Services: Deep dive" from Thierry D’hers
• "MDX Basics" from Katherine Splett

The link for the usage of MDX in a relational scenario is this one - "Extracting Olap Data from SSAS using SSIS - Darren Gosbell"

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.

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!

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 

/* 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', -- 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 * 
FROM OpenQuery(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...

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.

Interested in online ressources for SQL Server Training?
Please do not forget that these ressources can not replace a complete Training and therefor only should be considered as a additional point of information...

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

SQL Server 2005

After offering free delta courses Microsoft now offers a whole bunch of free E-Learning courses, which will enable you to pass the Exam 70-445 for the Microsoft Certified Technology Specialist (MCTS) SQL Server 2005 Business Intelligence.

You will find the complete Course Catalog at www.microsoftelearning.com

There are also some other resources for free at the moment at a subsite from Microsoft SQL Server 2005 – Learning Portal they called Dig Deep. Especially the free e-book Microsoft SQL Server 2005 Administrator's Pocket Consultant is a very useful resource and worth reading.

Sharepoint Technology

There is another bunch of free E-Learning courses for the Sharepoint Technology, which can be found here.

Visual Studio 2005 and .Net 3.0

And as expected developers are not left alone and there are also free E-Learning courses and a Dig Deep available for Visual Studio 2005 and .Net 3.0.

Chris Webb has posted an detailed article called Designing Effective Aggregations in AS2005 which - in my personal opninion - is a must-read because of the details mentioned and the further links Chris provides.

Number 9 of his listing also points out a pitfall if you use Usage based optimization via the wizard - also read this carefully.

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)

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

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

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)

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 video highlights two mission-critical applications within Microsoft that take advantage of SQL Server 2005: MS Sales, a worldwide revenue reporting system, and SAP R/3, an enterprise resource planning system.

http://www.microsoft.com/downloads/details.aspx?FamilyID=2ef438a4-4ae3-4d7b-bfe5-037c954f72c0&DisplayLang=en

Discussion on how Microsoft IT used SQL Server 2005 to deliver Microsoft Report Analytics, a robust, ad hoc reporting application and MS Sales Business Intelligence, a comprehensive business intelligence solution.

http://www.microsoft.com/downloads/details.aspx?FamilyID=92fbac74-0ca3-4b47-98ca-85014ec5df26&DisplayLang=en

You are using SQL Server 2005 and are missing the SQL Server Service Manager which is included in the version 2000?

Jasper Smith helps you out and has published the "SQL 2005 Service Manager" for download.

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!

Without Office 2007 (Office 12) having hit the market you could use this for BI...
But please regard that this download is available at a Microsoft website but is not a supported product, it is just a demonstration - but it is a really good one!

Business Intelligence Portal Sample Application for Microsoft Office 2003

Extract from the website:
"The business intelligence portal sample application for Microsoft Office 2003 is an integrated, customizable, multilingual, Web-based OLAP solution that enables employees in an organization to create and share structured views (OLAP and Relational) and unstructured views (Documents and URLs), based on various data sources, such as SQL Server 2000 Analysis Services, offline cube files, relational databases, SQL Server Reporting Services reports, spreadsheet data files, XML Web services, XML files, and any requested URL.
The business intelligence portal is entirely built on top of Microsoft Windows® SharePoint® Services. It extends Office Web components capabilities, and uses SQL Server Reporting Services. The BIP Viewer is a smart Web Part that can show multiple views from multiple sources and filter data based on Web Part connections with other Web Parts such as the Business Scorecard accelerator.
The business intelligence portal also contains an add-in for Office 2003, that enables end users to consume reports and OLAP-based views (charts and PivotTable reports) directly into Microsoft Outlook®, Microsoft PowerPoint®, Microsoft Excel, and Microsoft Word documents. This can streamline their daily work, and enhance their business productivity."

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"

Microsoft has published the documentation about MARS - there internal financial reporting solution - as an "Microsoft IT Showcase".

Extract from the website:
"Overview of how Microsoft implemented a more robust and flexible financial reporting solution by using SQL Server 2005 technologies. By using SQL Server 2005 Analysis Services and SQL Server 2005 Reporting Services, the standard reporting of the financial results was upgraded to provide end users with an effective business intelligence platform that was more flexible and more secure."

Mosha Pasumansky is writing a whitepaper about Analysis Services 2005 Cache Manager and has posted an extract to gain some fedback about the technical level.

Guys, fasten your seatbelts, clear your brains - this paper will be good stuff when released.

And please do not forget to give Mosha feedback!

I just came across this post from Bayer White who is a co-blogger of Brian Knight - they are sharing this Community Server.

There is a website available called msbicentral.com, regarding to the post, this should be a "pilot community and the "the "GOTO", place for Business Intelligence knowledge".

Extraxt from the website:
"Wayne, Brian and I spoke about what the future holds for MSBICentral and came to the conclusion that there were not enough BI Communities. We decided we should do more and make sure that are content is up to date and give examples of BI techniques for 2005. Please hold me accountable to this!"

I think we should do this...

Extract from msbicentral.com:
"MSBICentral.com is a community site that helps users like yourself learn to develop, use and administer Business Intelligence (BI) solutions using Microsoft products like SQL Server. On this site, you'll find hundreds of articles, scripts and tutorials to help you learn or perfect your BI skills. If you have a question or if you're a business intelligence newbie, look on the left bar for some quick links to get you started.
Please note: You'll need to be logged in with your free account to see articles and use much of the site."

And be ashured, registering is really worth investing the two minutes to fill out the form!

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

Download an updated version of the documentation and tutorials for Microsoft SQL Server 2005.

Extract from the website:
"Download an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The December 2005 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes.

Books Online includes the following types of information:

• Setup and upgrade instructions.
• Information about new features and backward compatibility.
• Conceptual descriptions of the technologies and features in SQL Server 2005.
• Procedural topics describing how to use the various features in SQL Server 2005.
• Tutorials that guide you through common tasks.
• Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2005.
• Descriptions of the sample databases and applications included with SQL Server 2005."

Download over 100 samples and three sample databases for SQL Server 2005.

Extract from the website:
"The samples download provides over 100 samples for SQL Server 2005, demonstrating the following components:

• Database Engine, including administration, data access, Full-Text Search, Common Language Runtime (CLR) integration, Server Management Objects (SMO), Service Broker, and XML
• Analysis Services
• Integration Services
• Notification Services
• Reporting Services
• Replication

The samples databases downloads include the AdventureWorks sample online transaction processing (OLTP) database, the AdventureWorksDW sample data warehouse, and the AdventureWorksAS sample Analysis Services database. These databases are used in the samples and in the code examples in the SQL Server 2005 Books Online."

Mosha Pasumansky has posted his experiances with a presentation about "Analysis Services 2000 vs. 2005".

Extract from the website:
"I have run across a presentation called “Analysis Services 2000 vs. 2005” prepared by Jaimie Basilico and Mark Frawley (Jamie works in Microsoft as Senior Technology Specialist in the East Cost, and he is one of the best field people in Analysis Services that we have). This presentation is targeted towards people who are familiar with Analysis Services 2000 and want to come on speed with Analysis Services 2005. I have found the presentation very useful, but not all subjects are covered in the same depth. Below are my comments [...]"

Full presentation can be found at http://www.nysia.org/events/past/2004/2004129dbprof.pdf

Absolutely worth reading and also worth reading Mosha´s comments!