dinsdag 8 juni 2010

DTS migration project.

In my current project we have to migrate a couple of hundreds DTS packages, jobs and other stuff from a SQL server 2000 Cluster to SQL Server 2008 cluster. For certain reasons we decided to take two steps:
  • Phase 1 : migrate DTS packages to a legacy server (SQL Server 2008 with DTS backwards compatibility components)
  • Phase 2 : migrate DTS packages to a new environment with SQL Server 2008 SSIS. A complete rebuild to Integration services.
So we divided the project in the following steps:
- Global analysis current situation.
- Global analysis desired situation Phase 1
- A Proof of concept for learning.
- Per system part
  • Plan
  • Migrate
  • Validate
- Global analysis desired situation Phase 2
- A Proof of concept for learning.
- Per system part
  • Plan
  • Migrate
  • Validate
The system part are parts of processes that have a strong cohesion with the other packages in the systempart and loosely coupled with DTS packages in other system parts. Per system part we created three main steps:
- Plan : In these steps we have to gather information about DTS packages, applications, jobs, linked server, access applications, system owner, etc etc. This information is processed in a migration plan. A DTS/databases and job/database matrix will be used for impact analysis
- Migrate. In this step the migration takes place. The system owner is notified that the software is migrated and the software is copied to a development environment, changed and deployed in the production environment, the system owner accepts the software and the last step will be evaluation of the best practices
- Validate. In this step the production is checked and validated by the system owner, The projectmanager is notified and lessons learned evalution is done.



We hope to start soon with a POC for a lessons learned. I'll blog about this laterss..

Greetz,
Hennie

woensdag 26 mei 2010

Risks and (managed) Self Service BI

Dear friends,

Currently, i'm building a presentation about Managed self service BI (MSSBI) and i'm investigating how to set up an risk management process for judging the risk of certain Self Service BI (SSBI) solutions. So what's the story: (power) users will be creating a lot of SSBI solutions and they have to be managed somehow. Why? Well, if certain SSBI solutions are used in critical business processes and the power user knows all the details of that solution that's a great risk for your company. For example, if that employee leaves your company, great damage can happen. So there should be a governance committee which collects information about the solutions built by the (power) users.

Risk can be split into a function of  'change that it will happen' and 'the impact WHEN it happens'. That will give something like this R(isk) = P(robability) x  I(mpact). This means that you could have a critical process (high impact) but the change that it will hapen could be low due to good documentation, multiple powerusers who created the solution, etc. Or you could have a non critical process but a high risk that it will happen. So the following diagram, shows what kind SSBI solutions you should put into a managed environment.




Below, i have some indicators summerized, which should be taken into account for SSBI risk analysis:
  • What kind of business proces is that SSBI solution built for. Is it for a critical process or is it for a non critical proces built?
  • How many users are using it? If a lot of users are using it, it could be critical. Or when the solution needs scalability and the solution can handle that (anymore). The SSBI solution has been built in the past and the use of data has grown and the number of users has grown. It has grown into a solution that can't be handled efficiently anymore. Then, it needs to be managed.
  • The number of times it has been used. If a SSBI solution is used once each year than it won't be important? This is quite arguable because you could say that if it isn't used much it won't be important but a profit and loss report could be used a couple of times per year but it is important for your company.
  • The data that is being loaded into the SSBI solution.
  • The kind of data that is loaded into the SSBI solution.
  • How well is the solution documented? If a SSBI solution is very well documented and the (power) user needs the flexibility of the solution to create changes to it by him-or herself, there is less need for integrating it in the managed environment.
  • How well is the solution secured? If business critical information is in the SSBI solution stored, the risk of hacking the solution is larger than a good secured solution.
  • Is the solution properly backupped and are these backups tested in a good manner?
Well that's it for now. If i can think of more riskfactors i will blog about it in the near future.

Bye,
Hennie

vrijdag 21 mei 2010

BI event 2010

On Tuesday 2010, 18th may i attended the BI event 2010 in the Netherlands. It was a day full of presentations about BI and it was mix of good presentations and some were less good, but overal i enjoyed the day.

The day started with a crash of a truck on the A28 at Amersfoort and so a lot of people were late and the seminar started a half a hour later. There were sessions with everyone and there were parallel sessions with customers, consultancy and vendor parties. These are the sessions i attended:
  • A textual Database for decision making - Bill Inmon
  • Semantic Media Wiki - Mark Greaves
  • BI Efficiency - Robert Mansour
  • Dataquality - IntoDQ
  • Management excelence - Frank Buytendijk
  • Real time BI - Intersystems
  • Extended BI - Enterprise technology
  • How a DWH suppors a transaparant and customer driven organisation - Woonbron
  • Saas BI - Rick van der Lans
A textual Database for decision making - Bill Inmon
Bill told about he concepts of a textual datawarehouse and DW2.0. I've never heard Bill speaking and i was quite interested in what he had to tell. Overall it was entertaining to hear him speak but answer on how to do textual stuff were not part of the presentation. That's was a pitty. He sounded a bit grumpy about people who built systems and calls it a 'datawarehouses';-)

Semantic Media Wiki - Mark Greaves
Mark Greaves is of the Vulcan company of Paul Allen and he associated with Bill Gates, and he told quite a technical story about Wiki's and analysis of text.

BI Efficiency - Robert Mansour
Robert is partner of TOPBI and he told an interesting story on how to improve the efficiency of BI projects and administration. He approached this with 3 points of views : Technology, organisation and process. These categories will give you grip on certian aspects of BI and implementation. Quite interesting to see some BI projects from the viewpoint of consultancy company.

Dataquality - IntoDQ
This presentation was a more or less a productpresentation of Trillium tool, a dataquality tool. Nothing to report about this.

Management excellence - Frank Buytendijk
Frank is a good speaker and he entertained the audience with a excellent (!) session. He approaced BI from the point of mananagement decision improvements. Operational processes are described due to many governance programs like SOX. Management processes are rarely described and Frank states that should be more improved by adding more stages in the PDCA cyclus.

Stakeholders like customers and suppliers add value to a organisation but are rarely measured by performance management tooling. He used the example of K-mart. K-mart was very successful in customersatisfaction, employersatisfaction and they had a good prospect. But they were out business when Wall mart beated them. So they had everything on control but forget the external factors.

Extended BI - Enterprise technology
This presentation was more about search technology than it was about BI. A google like search functionality was shown,  to search data from the enterprise and it was shown in as in a dashboard. Another demo was about pictures and selecting them based on colors. With a slider you could say more or less red andfor example if you choose more red with the slider, pictures with more red were shown. Nice but how do you link this with a BI solution?

How a DWH suppors a transaparant and customer driven organisation - Woonbron
This was a presentaion about a large housingcooporation. He explained the solution built at Woonbron. An interesting approach on how they built their architecture of information delivery. They also built a data warehouse based on datavault. First they created a logic data model to create a uniform naming convention. For example, There was a discusion about a fysical building and a living unit in tha building. This could 1:1 but also 1:n.

Saas BI - Rick van der Lans
Rick told about BI SAAS. He stated that SAAS BI is something different than an application that you would host in a SAAS cloud and i agree with him. SAAS BI is mostly a project that you should build from scratch and a SAAS Application not. He also told about a vendor lock in principle and there is no standard for switching between parties. He talked about the developments in the IT world like Selfservice BI and SAAS BI. He gave us something to think about: why is the business taking away activities from IT? Is it because they're not satisfied with what we do?

Before answering this question we have to look deeper in this question: What is meant with satisfied? What could be the reason? Quality of service of IT, cost effective, speed of solving issues, liability, etc?

There is a distinction about Selfservice BI and SAAS BI. The emerge of Selfservice BI is about exploding information, faster decision, IT backlog and perhaps it's better to let the user do this kind of stuff. IT can focus on consolidating Selfservice BI solution in the mainstream of  a company information strategy. SAAS BI is technical administration move. The user won't get more functionality with SAAS BI. The application is only somewhere else placed. So two different things in my opinion. And they should be treated both in their own way.

Conclusion
An interesting day with high peaks (Frank Buytendijk, Woonbron, TopBI) and some disappointments (Bill Inmon, Intersystems) and some in the middle (Mark Greaves, IntoDQ, Rick van der Lans). But overall it was a day i didn't want to miss.

That's it !

maandag 10 mei 2010

Managed Selfservice BI

It has been a while back since i wrote a blog and that’s because my daughter is born!! She is a healthly young girl. So sleepless nights, here I come!! Okay well let's go back to business.

At this moment I’m creating a presentation for managed Selfservice BI. A couple of months ago, i didn’t quite understand the concept of managed Selfservice BI. Why? Well because when I read the papers of Forrester and Gartner about agileBI, Managed BI Selfservice BI, etc information workers complain about information they can’t find or they had traditional BI questions. How do you solve that with managed Selfservice BI? If you'can't find the information a tool won't find it for you.

So what is Managed Selfservice BI about? Well in my opinion it’s not a tool but a complete concept. A concept of the following parts:
• An Enterprise BI Environment
• A non official circuit
• Validated and non validated sources
• Information workers
• Governance committee


So this is happening: Information workers will get information from validated sources. This is the information that they can count on that it is correct. It is validated by a special committee, a governance committee (powerusers, IT) which approve non official information (reports, data, etc). This non official information is like a grey circuit. Information comes from all kind of sources like internet, email, partners or what ever. When the governance committee approves a source the data is integrated in the enterprise BI environment. A set of acceptance criteria will be needed to make a clear vision of what is accepted and what not.

The information that the power users created during their work will be monitored. A risk analysis will be done whether the information is critical, or it needs scalability or it needs to be standardized, etc. When certain risks are too high, it needs to be integrated in the BI environment.

So still reading a lot about selfservice BI and more information can be expected in the future..

Hennie

zondag 25 april 2010

Datavault : Identifying the Satellites

In the first parts (part 1 and part 2) i wrote a small introduction about Datavault and identified the Hubs during a small analysis. Today i'm gonna identify the Links in the 3NF model of the AdventureWorksLT database.

So what are links, again? Links tie two or more hubs together. Links represent the relationship between two or more business elements. These are usually the business events or transactions. Each link has its own surrogate key, the keys from the hubs, a load date time stamp and a record source.

Below you can see the diagram in which i've identified the link tables:



How can you identify the link tables. Link tables can be identified by looking at typical transaction tables and many - to -  many tables (facts). Secondly, Lindstedt states that any table that doesn't  have a respective business key becomes a good link identity. First, lets start with identifying the easy link tables (many to many)

  • Salesorderheader: Many to Many table.
  • Salesorderdetail: Many to Many table.
  • CustomerAddress: Many to many table.
  • ProductModelProductDescription: Many to Many table.
  • Product: Many to many table
So there 1 more link table left:
  • The first one is the recursive Product Category table. This table has also a prent child relationship and therefore a link table is neccessary.
So that's it for now. Next time the satellites.

Greetz,
Hennie