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!

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!

You surely know "Project REAL", the SQL Server 2005 reference implementation at Barnes & Noble. It contains information on all upcoming topics in a BI project.

And now...
your are not only able to read about it - you are able to download a whole subset of the project REAL data and the various parts of the implementation

Extract from the website:
"The kit contains:

1. A set of instructions for setting up the environment
2. Guidance on how to explore the implementation
3. A sample relational data warehouse database (a subset of the Project REAL data warehouse)
4. A sample source database (from which we pull incremental updates)
5. SSIS packages that implement the ETL operations
6. An SSAS cube definition and scripts for processing the cube from the sample warehouse
7. Sample SSRS reports
8. Sample data mining models for predicting out-of-stock conditions in stores
9. Sample client views in briefing books for the Proclarity and Panorama BI front-end tools

This kit will guide you through the key points to observe in the Project REAL implementation. Use it to learn and to get ideas for your own implementation. (While we believe it represents a very good design and generally follows best practices, it should not be regarded as the solution for every BI situation.) It will be helpful to see the overview presentations about the BI tools in SQL Server, or read documentation, before exploring the kit. One good source of information is to go through the tutorials that ship with SQL Server (the tutorials are installed when you select “Workstation components, Books Online and development tools” at installation time)."

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!

Yet another version 2.0 of a three-letter-buzzword you may think...

But the article "Information Management: DW 2.0 - Architecture for the Next Generation of Data Warehousing" is provided by Bill Inmon, the father of data warehouse.

The article acommodates the encreased needs, the gathered work experience and also the developed status of hard- and software.

Read carefully - in my opinion it is more than another "version 2.0" hype...

If you want to know more than written in the article, you could register here.

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

With SP1 SQL Server Reporting Services 2005 will now also accept SAP BW as a data source. The connection will be established without any custom code in SAP BW (and therefor no need of installation assistance by SAP Administration) using XML/A (XML for Analysis, XML standard for Online Analytical Processing [OLAP] using standard Internet protocols), which is an established standard as described at the website of the organization (http://www.xmla.org/, http://www.xmlforanalysis.com/).

Microsoft provides a really good white paper, which includes a step-by-step guidance and also describes some prerequisites and basics. There is also a video, which visualizes the described steps of the white paper.

The whole documentation and the connect to SAP BW is real good work, but some things regarding "real life" is not included in the white paper or is not mentioned as clearly as needed - in my personal opinion.

Basic Information

The SDN at http://www.sdn.sap.com/ (SAP Developers Network - do not hesitate to surf there, you will not get infected... :-) ) provides a real good basic information as pdf-file for XML for Analysis from the SAP point of view.

SAP System Prerequisites

If you are authorized to connect to the SAP system and would like to test if the XMLA SOAP Services is up and running please use transaction SICF (Service Maintenance) to check this - as described in the above mentioned pdf-file on page 14. And if SAP Administration does not know how to verify this hand them over the pdf-file...

You will also be able to discover the service url using the transaction SE37 and the function module RSBB_URL_PREFIX_GET as desribed on page 13.

Patch Level

As described in the white paper, there is a certain patch level required to assure a working XMLA-Connect.

• Support Package 30 for SAP BW 3.0B
• Support Package 24 for SAP BW 3.1
• Support Package 16 for SAP BW 3.5
• Support Package 6 for SAP NetWeaver 2004s (BW 7.0)

Other patch levels will also work BUT... will not support real interesting things like selections via variables for example. But I would suggest that you COULD use Reporting Services to do a real smart master data reporting for example - the best way to convince the stakeholder of the SAP System that doing the patching will be really worth the work.

You will not be able to convince the SAP Administration to do the patching required for Reporting Services, because implementing a Patch is not as "easy" as in the SQL Server World, doing a patch in SAP BW definitely means a own and time intense project, because all implementations in the system have to be checked, if they would work as expected and as designed at the current patch level.

So just think about this point and do not hassle with SAP Administration and please do not start moaning, if they are not willing to do the patch update - remember, it is not as in other worlds...

Language / Communication with SAP BW Developers

Do not wonder about the terms that SAP Developers are using while talking about the objects in SAP BW - this will be different from the terms you will encounter using XMLA or you certainly will know from the SQL Server world. So communication sometimes will be difficult but an accurate translation will be helpful and is also provided in the pdf-file on page 19 and 20. 

Security & Single Sign on (SSO)

To be shure, that we think and talk about the same thing while having SSO in the mind first a definition:

"What’s the Difference Between Authentication and Single Sign-On?

Authentication, or initial authentication, occurs when users first identify themselves to a system, and in turn this identification is verified. Initial authentication in SAP environments can take a number of different forms, ranging from anonymous or guest access to a Web site through the familiar user ID and password procedure, to using X.509 digital certificates.

Where single sign-on is in place, the user is issued credentials in one form or another following initial authentication. This allows the user to forego subsequent authentication steps when accessing further systems, offering not only convenience, but also increased security by limiting the number of times users enter sensitive information. This reduces the temptation for users to choose an easy-to-guess password. Single sign-on authenticates the user to access all the applications they have been given rights to in the SSO landscape, and eliminates future authentication prompts when the user switches applications during that particular session."
[from the SAP Developers Network]

But what does this mean regarding RS on SAP BW?
While SAP BW is a data storage and reporting system whith an own authorization system you definitely would like to make the usage of the reports as comfortable as possible. Though a user should not need to athenticate against the SAP system as backend while running the different reports and provide his credentials again and again. Therefor a automatic authentification should be possible and established by the system without contacting the user.

Normally in enterprise environments you will not face a homogeneous landscape whith SAP BW residing on a Windows Server but will discover, that SAP BW is running on a UNIX OS.

If you are a lucky one with SAP in a windows environment, you probably will be able to use kerberos and the SAP CUA (central user administration) and this will be the right documentation:

• http://www.microsoft-sap.com/doc/SAP_AD_Final.doc
• https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/8302a929-0501-0010-05b5-d48f544bc572

But what to do, if you are facing the "standard", SAP BW on UNIX OS??
There are alos certain ways to implement a SSO solution between Windows and UNIX, including Kerberos, or 3rd-Party-Tools like the ticketing with the Tivoli Access Manager or the solution from secude. You will find appropriate documents on the SDN or other sources like:

• UNIX and Kerberos
• IBM Tivoli Access Manager
• secude

If you do not like to establish the SSO via a 3rd-Party-Tool and are using the SAP ITS (Internet Transaction Server) than you should read this article provided by Thomas Jung very intendly.

If you can not solve the above mentioned topics, you probably could use a technical user to connect RS via XML/A, but please consider, that the user authorization in SAP BW normally is based on so called authorization objects (for example key identifiers like company code) and you will not be able to use this build-in security concept for the user while using a technical one and though you will have a loose of security. If you are just planning to deploy master data reports, than the use of a technical user could be without security concerns.

I would highly recommend that you avoid a scenario where you implement your own security structure in Reporting Services or in SQL Server. Then you are only building up certain places for administration and could not be sure that your "home-made" security provides the same security settings for the users as the settings in SAP BW. And because of SAP BW normally being the leading system and the main reporting system you should prevent this situation.

Please do not forget to ONLY use the https-SOAP-Connection, whether you are using SSO nor provide the credentials via the reporting manager application during the report call.

Conclusion

Doing Reporting Services on SAP BW is a good option to enhance enterprise reporting towards the microsoft plattform regardless, which data source is connected. Doing so also will bring up some points for discussion or some pain points - if you face a hardliner SAP Administration for example... :-)
But to be honest, you also would not be really glad if your SQL Server data would be assimilated by a SAP BW system, would you?

If someone has other arguements, opinions and especially hints on the SSO-topic please feel free to use the comments

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

You shurely know, that Microsoft provides a control to display reports in your own applications without just linking to the SQL Server Reporting Services by http or SOAP. It is a little bit difficult to understand how this control works and especially the difference between the remote mode and the local mode needs to be clarified, I think...

It´s a pity that the the ReportViewer control cannot be extended by adding custom renderers or custom report items - that is the only disadvantage.

Here are some links, that should bring up light into the dark:

Microsoft Report Viewer Redistributable 2005
[As a german developer you will need the Microsoft Report Viewer 2005 Language Pack Deutsch to bring up the right screen messages]

http://www.gotreportviewer.com/, which provides informations and sample codes to give a quick start. Especially the difference between local mode and remote mode is described clearly.

MSDN article about ReportViewer Controls (Visual Studio)

MSDN Forum: Visual Studio Report Controls

Teo Lachev, Building Report-enabled Applications with the New ReportViewer Controls (Part 1 of 2)

Teo Lachev, Building Report-enabled Applications with the New ReportViewer Controls (Part 2 of 2)

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!

Jedox - normally known for the Worksheet-Server has released a Open-Source OLAP-Server called PALO a while ago.
You will find more Informations at the Jedox-website or on the official PALO-pages.
The free download is placed here.

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!

What Makes an Enterprise Data Warehouse?
http://www.eweek.com/article2/0%2C1895%2C1834993%2C00.asp

Budgeting for a Data Warehouse
http://www.b-eye-network.com/print/1146

Database Change Management Tips and Techniques
http://www.dmreview.com/editorial/dmreview/print_action.cfm?articleId=1031664

Top 20 Tips for OLAP Success
http://www.dmreview.com/editorial/dmreview/print_action.cfm?articleId=1031668

Microsoft BI tutorials by William E. Pearson, III
http://www.databasejournal.com/article.php/1459531

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

IT Toolbox

Intelligent Enterprise

Data Monitor

The Data Warehouse Institute

Business Intelligence Pipeline

DM Review

Business Intelligence Network

The Business Intelligence Website

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

MS did it!

A real MCP for Business Intelligence, called Microsoft Certified IT Professional: Business Intelligence Developer (MCITP: Business Intelligence Developer) will be available soon in English. We will have to wait and see when the German one is ready...

Seems, that they have put very useful stuff together for these exams.

Extract from the website:
"Microsoft Certified IT Professional: Business Intelligence Developer (MCITP: Business Intelligence Developer) is the premier certification for business intelligence system designers and developers. This certification demonstrates that you can design solutions, data transformations, and reports.

Business intelligence developers design and implement multi-dimensional database models (logical and physical), data marts, data warehousing, data transforms, data analytics, and reporting solutions. This includes programming and customizing servers that use MDX, customer transforms, and custom reporting solutions. Business intelligence developers are typically employed by mid-size to large organizations. "

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

Mauro Cardarelli tries to bring up some clarification regarding Dashboards and Scorecards in his post Dashboards versus Scorecards.

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

As posted before, there are all Office12-Teams blogging about there products.

You will find the Business Scorecard Manager Team Blog here. They haven´t posted many articles but you will find many usefull links there.

If you would like to read a bunch of articles you should visit the blog of Ian Tien called "Ian Tien's Unofficial Business Scorecard Manager Blog - Scorecards and Dashboards for the masses".

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

to be continued...
just to store the links - text will be added...

http://www.msftdwtoolkit.com/
Chapter 13 - Metadata Plan Reporting package:
http://www.msftdwtoolkit.com/ToolsandUtilities/Ch13/DW%20Metadata%20Browser%20Master.zip

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!

With meta data getting more and more in the focus of BI-Projects you should look at http://www.metaintegration.net/

Here is a little self-explaning extract from there website "Meta Integration is the leading "Metadata Component Provider" to major database, data integration, business intelligence, repository, and modeling tool vendors. [...]"

They have also some publications online, especially the PDF-File Meta-Data Version And Configuration Management In Multi-Vendor Environments seems to be worth reading.

Because of having not tested the mentioned products any comment is appreciated!

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