Data Reflections Discussions of data warehousing, transformation and analysis by the Business Intelligence Team at Tallan.

Yesterday, I attended a seminar covering the Oracle BI landscape. My aim was to come out of this session with a clearer idea of how Oracle products correspond to the Microsoft BI stack. My impression going into this seminar was that Oracle had many, many applications bundled under the BI umbrella. Nevertheless, I was surprised by the sheer number of options available. Practically every piece of the MS BI Stack has at least two parallel products on the Oracle side, in some cases many more. My second impression was that Oracle has done some very credible work to integrate the vast number of applications they’ve developed alongside their Siebel and Hyperion purchases.

Here’s how Oracle visualizes their BI offering:

(more…)

This post is intended to introduce Analysis Services 2005/2008 foundational concepts.

SQL Server Analysis Services is a component included in the Microsoft SQL Server product, and its use is fully covered under the same license (which applies to Integration Services and Reporting Services as well). Like the database engine, SSAS has a range of features stratified by Express, Workgroup, Standard Edition and Enterprise Editions.

SSAS is an OLAP, multi-dimensional database. While a relational data warehouse can also be described as OLAP, products such SSAS, Cognos and Essbase have fundamental differences. These OLAP servers:

· Aggregate data from a variety of sources into a compressed format optimized for query response
· Emphasize end-user navigation with modeling capabilities such as hierarchies, or drill-paths
· Employ MDX as the standard query language for retrieving result sets
· Use XMLA as the standard communication mechanism between clients and servers
· Automate the aggregation of granular data

OLAP browsing tools, such as Excel Pivot tables, enable users to drag in measures (facts in DW terminology) and constrain and slice them by dimensional attributes (columns in a dimensional DW table). This is a typical browsing session of the AdventureWorks cube in Management Studio:

(more…)

Reporting Services Installation

Reporting Services 2008 is included as part of the installation for SQL Server 2008. To install Reporting Services, select the checkbox that says “Reporting Services” under the “Instance Features” section.

As part of the installation, a Service Account will be configured for the Report Server.

The Report Server runs under a single Windows Service using either a Built-in account or Domain Windows user account.

The report server service contains:

• Report Server Web Service
• Report Manager
• Background processing application for scheduled report processing and subscription delivery

(more…)

Two big pieces of news on the Microsoft BI front:

1. Performance Point Server is being dismantled. After the next service pack, MS will cease develop of the product as its own entity. The Monitoring & Analytics capabilities will be bundled into the enterprise version of SharePoint. The Planning application looks to be dead in the water, however.

   Although PPS was complicated and unwieldly, it had potential and an interesting vision. I don’t see the failure of any part of the MS BI stack as a positive thing. The official press release and blog reactions are here.

2. While Microsoft had previously described a major-minor release schedule as the model for SQL Server going forward, the next version sounds like an interesting departure from that. Code-named “Kilimanjaro”, the next SQL Server release has been described as minor and BI-focused.

   Two components described thus far are “Madison” - which incorporates DATAllegro technology enabling 100+ terabyte warehouses, and “Gemini”. If we can believe the hype, and in this case I think there may be something to it, Gemini will allow relatively large cubes to be built by Excel users in memory, queried and moved into SharePoint. Some details on this are here.