Skip to content

Successful BI project – Step 2 – Requirements

The second post in the series about successful BI projects is the most important. The gathered requirements are the foundation of the whole BI solution. If your gathering and analysis of the requirements is poorly executed the overall project will suffer from this leading to unpleased end-users and bigger cost due to necessary changes in the system.

There are many ways to gather requirements and I’m not saying that one is better than the other but the one I’m using, and have been using for a while, works and also involves the business in the process. The method I use consists of at least two workshops. The reason that there should be at least two is that there will always be unanswered questions at the end of the first session and the time between the first and the second workshops gives the participants some time for reflection and contemplation.

To make the most out of the workshops there has to be a couple of business roles present at the workshops: end-user, governance, business analyst and someone responsible for the source systems.

The output from the sessions should be a thorough requirements specification signed and approved by all parties including:

· Business area owner

· Measures

· Dimensions and their hierarchies

· Grade of detail regarding time

· User roles and how they will use data

· Non functional requirements (load frequency, availability, security etc)

· Presentation/end-user tools

One part of the deliverables is the information matrix below. The matrix maps dimensions against measures for specific questions/requirements (Q1) the business asks for.

   

Measures

   

Dimension

Grain

Measure 1

Measure 2

Measure 3

Dimension 1

Level 1

Q1

Q1

Q1

 

Level 2

Q1

Q1

Q1

Dimension 2

Level 1

Q1

Q1

Q1

Dimension 3

Level 1

Q1

Q1

Q1

 

Level 2

Q1

Q1

Q1

 

Level 3

Q1

Q1

Q1

This requirements specification doesn’t include technical implementation requirements or data specific demands. These requirements should be gathered in a so called source system analysis but this is another story and isn’t included in this post.

Advertisements

Successful BI project – Step 1 – Ownership

I’d like to start this series about successful BI projects with a little post about project ownership.

For a Business Intelligence project to be successful the business has to be involved. It is the business that owns the analysis area and therefore they also have the responsibility against the users. IT’s role is to understand the requirements and use available techniques to fulfill them.

The method for developing BI could be called “Agile” with frequent releases and changes (of requirements) in every version. The interesting part is that agile projects should be owned by the business, not by technical developers.

If the business owns the project, common problems like lack of vision, strategy and commitment will be reduced.

Conclusion: Let the business own the project and let IT do the development.

External data available in Excel services

If you want to work with table data based on an external data connection in Excel Services, you can’t use an external data range (ordinary excel table). You must create a PivotTable and make it look like an ordinary table by flatten the hierarchies into a table.

Create a connection to a data source, get data and place it in a pivot table.

Place all your "columns" in Row Labels area.

image 

Set the options so that the pivot table don’t show subtotals and grand totals.

image

Select the option to show the pivot table in Tabular Form. You’ll find it on the ribbon under PivotTable Tools tab and then Design tab.

image

To remove the +/- signs before each level click the Buttons button under Show/Hide group in PivotTable Tools, Options on the ribbon.

image

If you publish this to SharePoint using Excel Services to show it you can now filter, refresh and all the things you can do with a pivot table in Excel Services. With the look of an ordinary table.

BI Design and Architecture

I would like to start of this blog with a link to a great article about the methodology to implement an advanced BI solutions using Microsoft’s tools.

You can find it at sqlbi.com.