BI White Paper - BI for Midsize Organizations

Today I’m opening a new section on the blog… the “BI White Paper”! And in this first post, I want to share with you a very interesting whitepaper that I was reading during the weekend. This whitepaper was written by Michael Schiff from SAP and is free (but requires registration). The article is written in a very interesting and objective way. I’ve included below the conclusion provided in the whitepaper to let you know more about it. I hope you enjoy the paper as much as me!

"Managers have the responsibility to make the best decisions possible, based upon the data available to them to learn more about how products from at the time. If their ability to analyze this data and transform it into useful information is improved, the overall quality of their decisions will improve as well. Business intelligence provides a spectrum of tools and solutions to support this aim. It’s the underlying technology behind more effective decision making. By helping align individual and departmental efforts with overall corporate strategies, it should lead to improved organizational results.

While many small and midsize companies have relied on spreadsheets as their primary Bi tool, most of them have come to realize that this is a stopgap solution. This is not to say that spreadsheets should be abandoned; rather, they should be a part of an organization’s Bi tool set. The key is to choose the right commercial Bi product suite that integrates with spreadsheet environments while supporting your long-term growth"

Read more downloading the white paper here

BI Book - Free Business Intelligence e-book

Today I bring you a link for an interesting free e-book from Logica. This e-book doesn’t require any registration and I think you should take a look!

"Knowing that we address Business Intelligence (BI) experts with our mailing, there's no need to convinve you of the importance of monitoring and measurement of the performance of your organisation in times of uncertainty and economic downturn.

With our book "The BI Framework - How to turn information into a competitive asset" we introduce how BI can help to reach your business objectives. Knowing that most organisations already have BI solutions in place we focus on cost effective management of BI and provide you with a clear roadmap on how to lower the total cost of ownership of your current landscape"

Table of contents:
1. Introduction
2. Business Value of BI
3. Business Intelligence definition
4. Managing BI
5. BI Lifecycle
6. BI Solution Engineering
7. Best Practices
8. Ferrari Case Study

Download link
I hope you enjoy it!

BI Thoughts – Writing on BlogNotions about BI

After Karen's (BlogNotions) invitation, today I’m starting a new adventure… writing about BI more often at the Business Intelligence BlogNotions website.

"BlogNotions Business Intelligence Blog delivers a diverse series of perspectives provided by BI thought leaders. Presented by NetLine, this forum delivers compelling updates on the latest technologies in the marketplace, discussions of competitive decision making, new ideas for analyzing data, and much more. Here you can find helpful information, ask questions, and collaborate freely"

This first post is dedicated to the Business Intelligence concept and for that reason, I should refer my main reference in this world: Drª Maria José Trigueiros.

I hope you read it and also enjoy it. Check it here
Give feedback and let me improve with your experience and knowledge.
Regards,
Pedro

BI Article - Writing about BI in a Magazine

Today I accomplish one more important step in my professional evolution. Together with Drª Filomena Lopes (IT Director of Universidade Portucalense) I wrote an article for a Portuguese magazine about Business Intelligence. Naturally, this article is more interesting for Portuguese people than those that don’t have Portuguese as a mother or second language. But for those that don’t understand Portuguese don’t worry, because I was invited to write some stuff about BI in a english website... I’ll tell you more about it during this week.

Article published last week on "Semana Informatica" magazine.

I hope you enjoy this article.
Regards,
Pedro

BI Training - Free remote BI Sessions

This month is being hard for me because I’m writing my master thesis… and the deadline is in the final of this month… and for that reason I’m not writing so often. Almost two and a half years writing in this blog and there's a new idea... in addition to send my BI messages/opinions through writing, I'm thinking to contact via video/audio with all you that visit this blog and make it alive with all the comments and suggestions. At the moment I’m only formalize the intention and hoping to receive feedback for all you.

In brief I’m describing below the session plan:
What?

Business Intelligence training focused on learning the concepts and using the Microsoft tools that are more flexible than other players in the market.

Why?

To share knowledge, make new friends and also to train my oral english language.

Who?

For all the persons that usually visit and support this blog with comments and sugestions. (For portuguese people I’ll prepare soon a on-site training)

When?

I will try to do it on October or November of this year 2009 after finishing the master BI thesis and after the 20h00 (Portuguese time... because I have a job…)

How much?

Free

How attend?

Through Office Live Meeting that you can use to engage audiences in online meetings, training, and events. I need to make some tests to this software, even appoint a date for testing it with all the registered users.

Registration?

In order to know the number of interested users, please fill the registration form in the link below. (The number of users are limited)

Registration form

I’ll be waiting for you.

TDWI - The importance of BI Standards

As you have noticed from previous posts, I appreciate a lot all the work that is being doing by TDWI ... not only for their great organization but also for its importance in the creation of standards that everyone can follow and speak the same "language". As I have already said earlier, I am currently preparing for the global certification of TDWI Business Intelligence in Germany ... when I feel prepared I take a flight and go to Germany and make the exams… of course with my princess because Business Intelligence only makes sense to me with the unconditional support of my princess Joana.

But not moving too much from the main reason of this post, I want to share with you some interesting posters from TDWI that you should print. Attention… follow this mind map or other posters from TDWI but not be a fundamentalist… you don’t need to follow all this approaches in details, because each project is different and the requirements as well.

TDWI's BI Usability poster visualizes all the elements that contribute to making a BI solution "usable." The mind map clusters the attributes into four categories, each of which has multiple branches that support related attributes. The four categories of usability are: Change Management, Architecture, Support and Analysis/Design.

The poster also depicts the dynamics of BI growth and decline using a model from systems theory. The model shows that usability is one of several key leverage points that BI managers can use to alter systems dynamics and move a program from a negative reinforcing loop to a positive one.

Request your free copy

Also take a look to other posters from TDWI:

Master Data Management

Business Intelligence Maturity Model

Regards,

Pedro

BI Discussion - Concepts Oriented vs Tools Oriented

Today I will talk about an issue that I think it matters to all IT professionals... which is until the point that we should guide by the concepts and tools? There surely a point of equilibrium between the two and for that reason I’m opening this discussion to hear from you.

The recent discontinuation of the performance point server Planning module by Microsoft, was alerting me to an important problem that starts becoming increasingly important, mainly for high increasing of globalization.

In our day to day during the work or during the leisure time, we should give more importance to the concepts or to the tools? Well ... it seems a simple question to answer ... but in fact there are many professionals in this area of BI that are very targeted to the tools ... which on the one hand can give an advantage to acquire a unique know-how, but on the other hand, is somewhat to isolate that knowledge, which could bring great disappointments fruit of great changes and development of technology that is easy to aware. Who guarantees us, that tools like SAS, Microsoft, Microstrategy, Cognos, SAP ... will not be used less or simply disappear?

So, I’m increasingly seeking knowledge through concepts, for example acquired during my master BI degree at ISCTE (MSIAD), which started in 2007. So many of you ask me ... then why are you an unconditional fan of Microsoft tools? And I say that for the simple reason of being a company with a strategy geared to the communities, for customers and for students, and has tools for easily accessible, portable, flexible and a great community always ready to help ... (where you can find me at the MSDN forums or in Experts-Exchange)

With Microsoft, is easy to implement concepts in practice with their more intuitive and simple tools, because besides the risk of opting for Microsoft to be lower due to its policy of acquisitions really strong (For example the acquisition of Panorama, ProClarity, some Dundas funtionalities, DataAlegro, Mobicomp, and some day Yahoo...) there is a lower risk in terms of knowledge ... because in spite of wasting long time learning the tools, we can release time for learning the concepts that it’s for me one of the keys to success.

An interesting research from DM Review can give you a better perception about the most used tools and the reasons to be on the top… take a look here

And since we talk about keys to success, I would ask you another question... What in your perspective, the other keys to success? Do you have discovered it yet? Well ... unlike the previous question, this is undoubtedly not easy to answer ... in my opinion, the other keys to success are with no doubt the motivation and the professionalism that we show in our day to day.

By Motivation I mean focused on ourselves and in what encourage us day to day, to be better and better.

By Professionalism I mean:

Our image to others. Reflects the level of consistency, punctuality and the capacity to assume our mistakes and errors
Be organized Reflects the capacity to be organized. As my project management teacher told us in the classes, if you are not organized, how you are able to convince the others that you can organize or manage their projects?

Help me identify the keys to success! Leave your opinion... This is just my first draft...

BI Education - SQLBI Methodology

Marco helped me two years ago in a dimensional model design problem. He probably does not remember me, but I’m following his work, mainly all the stuff relating to dimensional modelling. Marco published last week the first draft for an alternative purpose to Inmon and Kimball approaches. I spent yesterday and today reading all the 44 pages of this first draft. And came to me all the issues and problems that I felt in the past… it’s really a very good approach not only for BI professionals using Microft tools.

I’m comment Marco Russo and Albert Ferrari draft, only because I want to contribute with my opinion for their project and advertise it. And I would be happy if professionals and friends like Subhash, Bruno, Amin, Tomislav, Alex, Ella and all other experts give their opinions about this subject.

It’s an approach near the real world and my next comments are only to clarify myselft and make this draft even better. Like they say in the draft:

“Always remember that these are not fixed and immutable rules. Your specific implementation of the architecture will probably be constrained by other considerations. You – as the BI analyst – are the one and only person that will be able to take these decisions in a correct way. “

Download the draft in the official website

http://www.sqlbi.com/sqlbimethodology.aspx

Some comments/questions

1. The main use of the configuration database is for dealing with bookkeeping information and with data that come from more than one source? An example is to deal with the same customer that comes from two different sources with a different name… Which more funcionalities this configuration database could have? I think this configuration database should be shared with several BI solutions and use SQL schemas to split them. If you have one database configuration for each BI solution you’ll have more cost and more work to maintain it.

2. I’m agree that mirror database, filters only the required data, and the staging area is a relational database that will be a bridge between mirror and datawarehouse denormalized database. If you need to process all the cubes again, datawarehouse database stores all the historical data or you need to go again to mirror database or operational sources?! (sometimes the historic data in operational sources is available only once)

3. SQL Schemas could create problems in third party products, and I always avoid them. What you think about it? In Data warehouse or Data Mart databases you change the schemas used in Staging Area, could be good for understand the solution, but I’m afraid that could be an issue in ETL and OLAP designs. What you think?

4. Your definition of Metadata OLTP DB makes me a confusion… do you have views to OLTP operational systems to work as a bridge to DW in case of not using a staging area, but normally metadata is contextualizing your data… describing what each field, each table, each database is … I think you could explain this better.

5. Following your draft, in Kimball approach, the Data Warehouse database has a denormalized structure in spite Inmon aproach that has a normalized structure. Am I correct? There is some confusion in this point…

6. I don’t think that in SCD type 2 the status column should be used. I only understand the use of this column in this type of dimensions, only for users that goes inside database and need a fast understand of the current row, because in practice, if you lookup for the status column, you may have a problem if you need to import rows from a previous date (late arriving facts)… and you will map this rows to the status and could not be true

7. My defition of ODS was a little different. Your ODS description (only used by softwares) may cause several views of “true”. What do you think?

8. I’m completed agree with you about Natural and Surrogate Key in date dimension. In my last project I used Surrogate Key as an identity integer, that make me felt lost. I always needed to join the fact table with date dimension to understand the mean of each fact.

9. In page 36 when you wrote “Mirroring is not part of the ETL. “, I think create the mirror is the extract step of an ETL (ExtractTransformLoad)… you don’t do any transformations… only the basics like change column names and extract the data, and if you can apply some filters. Changing column datatype could generate an error… and we must avoid accessing source operational systems more than once

10. In pag 42, in which database you store logs added by SSIS Log provider? (Sysdtslogs). I like the answer you gave to the question “what is the difference between log and audit?” and also the use of audit information directly in the cubes but hidden for business users. Some people are a little confused in this subject.

11. How BI analysts control the data lineage? And how we can make an impact analyst on a change in a database?

Improvements

1. I think could be interesting include some standards in database objects (tables, views and stopred procedures names). Which nomenclatures you normal use to each object in each database (Mirror, Staging, ODS and DW e DMart)?

2. More focus on business and technical metadata of each database.

3. Create an addittional shared database for SSAS cubes performance using used based optimization. (Activating query logs of olap cubes and creating aggregations based on those logs)

4. In configuration database described, could be interesting the use of data profiling

5. Include a data mining structure

The original image was mofified by me and got it from draft 1.0.

I think that some comments could be basic for some of you, but because there aren’t any standards and any ISO for this subject, BI Analyst still some has doubts about the best pratiques to follow implementing a BI solution

So, I hope to receive Marco and Albert here to clarify me the points described before. It’s a very important step that these guys are doing, because they are creating rules, standards and make we speak the same language.

Good Luck!!

Updated 29-09-2008 with Albert Ferrari response. I will reply in the comments section.

Albert and Marco response:

Pedro,

I'll try to give you some answers, even if the space here is not enough for a complete discussion:
1. Yes, the config DB can be shared between different solutions for the same customer even because, in the DWH world, "different solutions for one customer" means "One complex solution for one customer, composed of different subject areas". :)

2. In some situation we store in the OLTP Mirror several snapshots of OLTP tables, in order to be able to rebuild the history. Clearly the DWH contains all the history but, sometimes, you will need to rebuild the DWH too...

3. I would not avoid schemas in order to satisfy third party tools. If some tool need to query the DWH then it will have its views and, if it cannot use schemas, then its views will live in the dbo schema.

4. OLTP Metadata is just a set of views. If you cannot build them in the OLTP DB and you cannot afford an OLTP Mirror, then an OLTP Metadata might help.

5. The Kimball approach is de-normalized but the extent of normalization that is needed in an Inmon one is not very clear, it all depends from the analyst. Inmon leaves all these "details" to the analyst, giving just some advices.

6. I agree with you. What else could I say? :)

7. What is your definition of ODS?

8. Yep. Even if, sometimes, I had to change the key, using some very creative way of expressing a date. If you have several billions rows then the INT representation of YYYYMMDD might be a problem and a smallint solution might be helpful. Clearly 20080101 is not a smallint... so you'll need some differente ways to express the date.

9.I don't understand your note. During mirroring you should not do any kind of transformation and/or column rename. If a column is called C1P8D3B0 ans is of type INT, then you will have that column name and type in the OLTP mirror and a VIEW that will change name and type when you will extract from the mirror.

10. I think the log DB is the perfect place for thos logs.

We are about to publish a second whitepaper that might be interesting to look at, it will provide a demostration of the methodology on the very (ab)used AdventureWorks DB.
Thanks alot for your comments, I will use them and your improvement section for a future review of the whitepaper.
Alberto

Business Intelligence Architecture

Dear Friends,
Once again, I’m writing a post with a long delay… Sorry… Today, I will follow the sugestion of several blog visitors, which is to describe and write more about what I’m doing in my master BI degree and my point of view of BI.

After nine months of master classes I’m able to describe my definition and my opinion of BI. My purposes with this post, and based not only in the classes but also in the several books I read and work experience as BI Developer, is to launch the discussion about what is BI and mainly how is BI structered.

Business words

The image shows the four pilars but also in which context the several business words related could apply in BI. Here in Portugal, and I thinks is the same in rest of the world, IT Consulting Companies usually create some confusion into the market, invented new business words, and curiously, almost times only these business words are new!

For me, BI is based in four main pilars:

Strategy

This pilar is where the top management defines the strategy and the path that must be followed to achieve the organization goals! The typical metodology to structure this strategy is the Balanced Scorecard (BSC) created by Kaplan and Norton. They recommend that managers gather information from four important perspectives:

Customer's perspective. How do customers see us?

Internal business perspective. What must we excel at?

Innovation and learning perspective. Can we continue to improve and create value for our services?

Financial perspective. How do we look to shareholders?

Processes

This pilar is not yet very evolved, and only few years ago, organizations start looking to processes in other perspective. Following Paul Harmon, in the article he wrote in http://www.bptrends.com/ about maturity level of organization processes, he realized that almost organizations are in a low level (2,5 in a range of 1 to 5) and still worst here in Portugal... And in my opinion, processes is the better way that an organization has to improve performance, reduce costs, and unfortunately in some organizations a better way to implement downsizing, because automating and improve a process may cause that a person could be moved to another unit or to be fired reducing costs for organization. It’s cruel, but almost organizations in the world focus on reducing costs finding a solution to fire people… but this is another discussion point! Business Process Management (BPM) is based in other past investigations like Business Process Reeigineering (BPR) and Total Quality Management (TQM). BPM or processes, could be monitorized by a Business Activity Monitoring (BAM) that is nothing more that a solution with KPI to monitor the performance of each process, and could be implemented a Business Process Management System (BPMS) that is responsible to follow the life cycle of each process (business object)

People

This pilar has the organizational hierarchy which shows who commands who, but not what each people do in organization. A good BI implementation must focus on people, not only to better manage the change of a new system, but also to gathering implicit knowledge that exists in each people of the organization transforming it in explicit knowledge.

Technology

This pilar as the other pilars it’s very important and requires a big understand not about the tools but about the concepts. There is a lot of people is this subject that are tools oriented in spite of be concept oriented. What I’m trying to describe, and mainly after my master degree, is that the tool is not the most important thing in this pilar. What’s important is the concepts and the understanding of what you can do with the tools. For example, to create a BSC you don’t need to be worried about the tool you do that, but in the concepts required to create a sucessful BSC. You can use a tool from SAS or you can simple create it for example in Excel… maybe you don’t know, but excel is the most powerfull and more used tool in the perspective of end user… and in my opinion, with new version 2007, Excel will be even better!!! Tecnology and mainly data mining algorithms could answer important questions to the organization that could change the strategy of the Organization.

These pilares are all important and dependent of each other, but in my opinion, people is the higher important pilar, and not only the people that implement the new system, but also the people in organization that accept this system, because there is a tremendous resistance by people to a change and there is a lot of implicit knowledge in organization that must be converted in explicit knowledge.

There is a lot of things to write and explain in each pilar, but I will try to do that if you think that itcould be usefull for you!!!

Cheers!!!