Data Reflections The blog of the BI team at Tallan

April 24, 2009

The Oracle Business Intelligence Stack

Filed under: Microsoft,Oracle — achandler @ 9:53 am

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…)

April 10, 2009

Introduction to Analysis Services

Filed under: Analysis Services,Microsoft,SQL Server,Warehousing — achandler @ 1:00 pm

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…)

February 12, 2009

SSRS 2008 Installation and Configuration

Filed under: Microsoft,Reporting Services,SQL Server,Uncategorized — mblake @ 3:52 pm

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…)

January 27, 2009

MS BI News Roundup

Filed under: Analysis Services,Microsoft,SQL Server — achandler @ 3:02 pm

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.

December 4, 2008

Partitioned Fact Tables

Filed under: ETL,Warehousing — achandler @ 1:06 pm

Once tables grow into the millions of records, they become candidates for partitioning. Table partitioning offers many benefits, particularly in warehouse environments. Since data is split into smaller units of storage, backups can target filegroups with a higher rate of change. Systems with multiple CPUs see improved query performance as partitioned data leads to greater parallelism. Perhaps most significant is the ability to swap in huge amounts of data by partition switching, an operation that is practically instantaneous.

Tables can be partitioned horizontally or vertically. With vertical partitioning, columns are split out into separate physical tables. This post focuses on horizontally partitioned tables, which take advantage of new constructs Microsoft added in the 2005 release – partition functions and partition schemes. Table partitioning is an Enterprise Edition only feature in SQL Server 2005 and 2008.
(more…)

November 6, 2008

Introduction to SAP Business Objects Data Integrator ETL tool

Filed under: ETL — sfernandes @ 7:33 am

SAP Business  Objects Data Integrator XI R2/R3

The purpose of this presentation is to familiarize the team with some of the features of Business Objects Data Integrator (DI). The presentation is also aimed at providing some insight into how technology can affect the design and implementation of  the ETL processes’. 

While at a high level it is best that an ETL architecture be technology agnostic, the physical implementation can stand to benefit by being designed to take advantage of the features provided by the technology.

(more…)

October 22, 2008

SSIS data management fundamentals

Filed under: ETL — elizotte @ 10:07 am

Loading data into a warehouse for use in business intelligence often involves loading data from multiple dissimilar sources. Many times the easiest way to consistently manage the data involves doing extracts from the source systems into a simple text format such as comma delimited. Even legacy systems have some form of reporting engines that allow for text file output, and in dealing with more current database types there are mechanisms such as BCP to allow for dumping output to a text file. A few more reason for using flat files – it allows for a snapshot in time that is replicable, it allows for a single output from a source system to easily be imported into multiple different warehouses, and allows for the imported data to easily be broken into manageable chunks by managing the text files.

SSIS has many features to simplify working with data files. It allows you to create Control Flows which sequence tasks involved in importing data, allowing for decisioning based on your criteria, as well as robust options for error management. It also allows you to easily perform common transformations, set checkpoints to define restart points and view intermediate results by using debug windows and visualizers. Microsoft has given easy visual designers for creating this flow within SSIS, making it easy and straightforward.

(more…)

October 20, 2008

ETL Process Control Architecture

Filed under: ETL — mfortuna @ 10:06 am

Data Volumes are growing at incredible rates across many IT departments. Businesses are dealing with new problems every day, and there is much demand on IT to be flexible and to react to many issues facing the business on a daily basis. Many times, processes become obsolete or disfunct because they cannot keep up with the rate of change and increasing demands by the business. In some cases, the users lose confidence in the systems and seek alternative methods for delivering reports and data in a timely fashion.

A centralized, standard Process Control Environment can provide an inexpensive and efficient way to provide an auditing mechanism on new and existing data migration processes which may run occasionally or very frequently.  This will enable an IT department to become more pro-active and aware of issues well before the end users are aware. This will allow for the IT department to react and correct the issue before the specified time in the Service Level agreement, or to send a notification or alert to their users, without the fear of losing their users confidence in the data or the overall performance of the IT department.

(more…)

September 23, 2008

Developing a Dimensional Model w/ conformed dimensions and facts

Filed under: Data Modeling,Warehousing — ocash @ 9:04 am

Conformed Dimensions and Facts

Data marts are often developed to represent important systems within a company.  Over time additional data marts are added, and eventually there is a desire extract data across multiple marts.  Extracting data across data marts can be cumbersome and some times impossible if the data marts were not designed to share common dimensions, also known as conformed dimensions. Ideally since the dimension criteria would be the same for each data mart, selecting data across multiple marts would be as routine as selecting data from a single data mart.

Going hand and hand with conformed dimensions, conformed facts involve standardizing facts across multiple marts.  Adopting conformed facts eliminates the ambiguity of having facts that possess the same name but have different underlying calculations.

In this blog we use simple data marts to demonstrate the use of both conformed dimensions and facts but the same concept applies to EDW’s and AS Cubes as well.

Now, to see how conformed dimensions and facts are applied during development lets start at the beginning with our ER diagram.

(more…)

September 8, 2008

SCD via SQL Stored Procedure

Filed under: ETL,Warehousing — srichards @ 7:37 am

We are going to revisit the issue of dealing with Slowly Changing Dimensions in a data warehouse. We have seen a demonstration of using the SCD transformation that is available in SQL Server Integration Services (SSIS); however, this is not always the best option for processing an SCD. When the volume of rows you’re dealing with is substantial, this creates a significant, and usually unacceptable, performance hit. Another valid reason why you may choose not to implement the SSIS transformation is flexibility. The SSIS transformation requires your begin and end dates to be datetime data types, which could prove to be inconvenient when your SCD needs to tie back to a date and/or time dimension. 

One alternative we are going to exhibit is using a SQL Server stored procedure. This example demonstrates the implementation of a Type 2 SCD, preserving the change history in the dimension table by creating a new row when there are changes. (more…)

Older Posts »

Powered by WordPress