Tag: Data Modeling

Posted on by

Collaborative Data Modeling

dimensional modeling

Get in touch to discuss your data modeling requirements; we have very high levels of expertise and don’t like to be beaten on price. You can read more on our methods below.

Our approach is called collaborative data modeling; it brings data modelers together with business subject matter experts to collaboratively design data models that work. BI System Builders uses a structured approach known as Business Event Analysis and Modeling (BEAM) to achieve this. We’ve been associated with the author Lawrence Corr for several years, and you can read about the BEAM method developed by Lawrence in his book Agile Data Warehouse Design (ADWD). You can also read our book review of ADWD here.

We use the BEAM techniques in collaborative workshops to identify the data stories within a business. This is achieved through lots of idea generation, whiteboarding and scribing; a technique known as modelstorming. Modelstorming is similar in concept to brainstorming but the effort is aimed at developing data models.

We’ve been operating the collaborative workshops for several years and at the following companies, Volkswagen Group, Dixons Retail, NFU Mutual, Vision Express, and Interflora.

The data models developed are known as dimensional models and are based on Ralph Kimball concepts. The main data modeler is Russell Beech who has twenty years dimensional modeling experience across numerous industries and also took training directly from Ralph.

There are three ways that we can help you:

  1. You’ve already tried modelstorming and dimensional modeling yourself but things are not progressing as you’d hope. Don’t worry, we can come on site and work with you to run some workshops to get you started, passing on our knowledge and experience as we go.
  • You already have reports and analytics but you feel that they are overly complex, they’re difficult to customise and support, and when business users come with new requirements, developing the new reports can be very challenging. The issue here is frequently the underlying data model. We offer a service where we analyse and assess your existing data models against best practice designs and make recommendations. If the recommendation is a new design, we can help you with the new design.
  • You don’t have any data models yet but you would like to. Perhaps your current reporting is based on spreadsheets and you now need more flexibility. You want to get started on the right foot with best practice data models that meet your reporting and analytical needs. We can come on site, take you through collaborative data modeling workshops, and design your data models for you.

If you’re considering contracting BI System Builders please leave your details here.

Posted on by

The Cornerstone Solution ‘101’

BI System Builders

 

The Cornerstone Solution® ‘101’

At BI System Builders, our expertise is in helping businesses, small, medium and larger enterprises to get the greatest value from their data by identifying the right business intelligence tools and data warehousing solution for that organisation.  This document introduces the way we work with our clients to achieve this, and sets out the BI System Builders methodology and approach – the Cornerstone Solution® – at a high level.

What is the question that most organisations want our help to answer?

Market environments are changing faster than ever and at BI System Builders we understand that organisations need to manage data in a timely way that helps produce results.  Today’s senior business leaders need metrics, insights and performance indicators that really help to inform decision-making.  We find our customers often ask “how do we join up our different types of data so we can really know what’s happening in our organisation right now?”

How do we get to the answer?

To provide the best answer to this question, BI System Builders has developed the Cornerstone Solution® – a rigorous process where we work hand in hand with customers to really explore their needs and to help them to an end position where they can actually get this intelligence.  Harnessed in the right way, business intelligence can be the key to unlocking how an organisation is performing. (You might see us refer to this approach in other documents as a vendor and technology agnostic framework.)

We find that using this tried and tested methodology is a really effective way of understanding current reality, and of cutting through to what is (and isn’t) needed now and in the future.  We can then build solid recommendations and deliver robust solutions (systems, tools and data warehousing) based on actual business priorities.

The Cornerstone Solution® explained

The Cornerstone Solution® is a way of lifting the lid on an organisation’s existing data management today and its future requirements, and then moving through the logical steps required to get to a working solution.  It’s a fully integrated and thorough end-to-end process and is ideal for ‘green field’ projects.  Its modular approach also makes it directly applicable to existing data warehouses and business intelligence solutions that require updating and improving.  We know that by laying a strong cornerstone in a structure, you can build out from it to get a solid, robust house – and we believe it’s exactly the same with business intelligence.

The Cornerstone Solution® has four key elements to the process and within these there are a number of sub-systems or modules.  These comprise:

  1. Strategic Requirements System – At this stage we take a look at the long term business intelligence vision for an organisation; so what do they need and across what timescales. We ask what must the solution look like? What’s the Corporate strategy it needs to support? Is there anything else that the solution will need to fit with?  We identify and understand who are the senior stakeholders (along with their wants and concerns), so that the context and requirements for the work are really understood.
  1. Solution Architecture System – Once the first element is complete, it’s possible to move onto looking at the architectural solution that’s needed (the recommendations for what is to be put in place). This is a comprehensive piece of work which includes seven key modules. (If you’re short on time, take a look at sections on Business Architecture, Data Architecture and Service Architecture.)

One of the most important things is to get under the skin of the business; what are its objectives, capability and the existing ways of working (the Business Architecture work).  Understanding what this means we can ensure the solution is really focused on moving the organisation forward.

We look at what data exists and what information is being managed today and how (Information Architecture).  What are the data flows, how is data captured and who owns it?  Is data managed internally or externally and how should this be approached? Where and show should this data be optimally stored? With this knowledge, it’s possible to look at what applications might be best to provide what’s needed in the future and how these will need to work together (the Application Architecture)It’s then a clear next step to look at what IT Infrastructure (Infrastructure Architecture) is needed to support these applications and how it will be made ‘fail safe’.

Through Data Architecture, we get to the heart of what’s really needed in terms of data warehousing, specifically what drives data requirements and how the data itself should be managed.  We use Business Event Analysis Modelling (BEAM) for this and it involves some detailed workshops.  This is a really useful opportunity to get stakeholders to engage in to the future by stating how things work and need to work.

All the modules of the Architecture must work effectively together such as the data models and the BI tools set (this work is Integration Architecture).

However ultimately, all this work needs to deliver a service to internal customers, and it’s essential that their needs are addressed – so what will they need? What service level agreements does the organisation require to make use of the data? (For example what time is the data wanted and how frequently?)  How will upgrades and back-ups be managed so that minimum disruption is caused?  How the essential platforms and systems are to be supported in the future.  (This work is Service Architecture and it’s fundamental that the final business intelligence solution delivers this.)

Some of the information needed here may already exist in an organisation. In other cases we’ll need to carry out interviews and workshops with key stakeholders.

  1. Control System – Delivering complex projects on time, to quality and to budget is essential for every organisation. To achieve this we use a rigorous project planning and governance framework.  It’s tailored to fit with the needs of each organisation so that interdependencies and risks are all handled in the right way.  Our customers can be confident about how the project is being managed, what it’s costing and on the progress being made.  An experienced project manager is in place to manage the project delivery.
  1. Delivery System – This is where the planning comes together and actual products are created; source data is mapped to target data (raw or legacy data to new style data targets), data models and databases are built, software is installed and the required analytics, reports and dashboards are created. The project is carefully brought to life and released into production through the live environments in a way that works for each organisation.

Programmes run in an ongoing review process to ensure that learnings are continually captured, documented and handed over to the customer with appropriate training and orientation.

 

As you might imagine carrying out this work is a sizeable task, but the methodical approach of the Cornerstone Solution® has been shown to work across larger enterprises with complex operations.  Backed up by the ‘know how’ of the BI System Builders team, this really is a logical way to make sure the resulting business intelligence solution delivers what’s needed.

Focusing on the bottom line

By using an established methodology where we’ve already developed templates, tools and plans, we can swiftly customise to fit each project.  This saves our customers cost as we’re not ‘re-inventing the wheel’.  Overall, using the Cornerstone Solution® lowers the total cost of ownership for customers.  Once new systems are in place costly legacy systems can be decommissioned.

Why choose BISB and the Cornerstone Solution®?  

  • With the Cornerstone Solution®, we provide a complete end-to-end integrated business intelligence solution, meaning we have a holistic approach that ensures all the appropriate areas are covered.
  • Our ways of working are tried and tested and have been used to contribute heavily to successful business intelligence solutions for organisations such as Vision Express, NFU Mutual and the VW Group UK.
  • We’re not tied to one specific technology or application provider – one size does not fit all and we will identify the system that’s right for you.
  • As a niche business intelligence consultancy we’re able to draw on the right skills, expertise and knowledge without our customers needing to pay for large consultancy overheads.
  • We’re interested in building a long term relationship with our customers, where we help to build your capability in managing your own data and business information
  • We expect high standards of ourselves and we believe our customers can see this in the results they experience.
Posted on by — Leave a comment

Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema by Lawrence Corr and Jim Stagnitto

BEAM Business Event Analysis & Modeling

BACKGROUND

BEAM Business Event Analysis & Modeling

I first worked with Lawrence Corr back in 2002 whilst I was designing content in the Analytical Applications Division (AAD) of the BusinessObjects Product Group.  At that time Lawrence was engaged as an external consultant to BusinessObjects, critiquing and advising us (AAD), on our BusinessObjects data warehouse design. Back then Lawrence also gave me my first formal dimensional modeling training when I attended his Data Warehousing Design Techniques course. Lawrence already had a very impressive reputation and was closely associated with Ralph Kimball.

It was therefore of interest to me when Lawrence said that he was writing a new book entitled Agile Data Warehouse Design.  Here’s what I found…

AGILE

There are two initial points to make about the book. Firstly, about the title of the book Agile Data Warehouse Design. Despite what the title might initially suggest it does not explain how to deliver a data warehouse through existing well known Agile techniques such as functioning as a scrum master or doing Extreme Programming (XP). It is rather about a structured method of bringing together Business Intelligence requirements analysis and dimensional modeling techniques using an Agile mindset.  The goal being to deliver logical models that work, in a highly time efficient fashion.  As such the Agile Manifesto is listed at the rear of the book and it is easy to see how the methods described meet the aims laid out in the manifesto.

PRACTICAL METHOD

Secondly, Agile Data Warehouse Design is a pragmatic book. It is not just agile theory alone. It will provide you with practical techniques, artifacts, and tools that will enable you to model successfully. I say that because I have already implemented these techniques, known as BEAM, extensively at a leading insurance company, at a leading car manufacturer working across all their vehicle brands and for a well known high street retailer. I have found that business users became actively engaged when introduced to the BEAM technique of the 7Ws (more on the 7Ws later).  Furthermore the BEAM tools made it easy for end users to contribute in an intelligent and structured way. That said, the business users did not need to understand the BEAM techniques themselves; in fact I never mentioned that we were using BEAM at all and they didn’t need to know. They simply attended the interviews and enjoyed having their brains picked and taking joint ownership of the developing dimensional model.

BI BREAKPOINTS

I became all the more interested in reading Agile Data Warehouse Design when I began to realise that it tackles head on several key ‘BI Breakpoints’:  the term used by BI System Builders to describe weaknesses in an End to End BI solution that can become points of failure. While the term is not explicitly used in the book it quickly became clear to me that the BEAM method will help developers address the specific BI Breakpoints between Business Analysis and Data Warehouse design.  As such, I found Agile Data Warehouse Design to be highly complimentary to the Cornerstone Solution® BI method.  The Cornerstone Solution® End to End BI method is used by BI System Builders to address BI Breakpoints. You can read more about BI Breakpoints here.

WHICH BI BREAKPOINTS DOES BEAM ADDRESS?

BEAM addresses BI Breakpoints around business analysis and dimensional model design.  A key issue for effective dimensional modeling, that I’ve faced many times, is that it requires the combination of three different contributing skill sets: Business Domain Expertise, Business Analysis, and Dimensional Modeling. The domain expertise is provided by the business. However, it is the role of the business analyst (BA) to extract that expertise, understand the business process area and then document the business requirements. To do this successfully requires the ability to ask the right questions.  Once the BA’s document is available it is translated into a dimensional model by the Business Intelligence and Data Warehouse (BI/DW) team.

Generally speaking I observe that BAs will have a predominantly business background while  dimensional modelers (DMs) a technical one.  Frequently a BA is assigned to go to the business and gather user requirements, the result of which is a copious document. Once the document is complete it is handed over to the BI/DW team to work with. Although the document is useful, typically it will not explicitly describe critical dimensional modeling design elements such as fact granularity, and fact and dimension table types and relationships as required by the BI/DW team for development purposes. Consequently, this handover can become a BI Breakpoint.

The BI/DW team will attempt to interpret the business analysis document as best they can. However, issues can arise because the BI/DW team had no involvement during the analysis stage and could not ask pertinent questions whilst the business analysis was being undertaken.  After sign-off of the business analysis document the BA may move onto another project and not be available to provide further help. As contractors and consultants are often used as BA’s they may even have left the business all together. This can cause a chasm of understanding to open up between the BI/DW team and what the business users had been describing and requesting.  Needing clarification or finding information missing and not knowing or wanting to approach the business again the BI/DW team may fall back onto something that is more securely under their control as a means to drive their modeling effort – source system data analysis.

The risk of building out dimensional models based on source system analysis is that the final tables will be close in design to the source data but may not model the business process area or meet business user needs.  The tables may not meet business requirements and they may not be a true dimensional design at all. To my mind this is a failing, because ignoring for the moment the new SAP HANA, I have always found dimensional models to be the most effective performance design for use with SAP BusinessObjects tools against a relational database and the best way to think of business process measurement in general.

To help avoid the BI Breakpoint that can occur between the BA and the BI/DW developers we have the notion of cross-functional teams.  A cross-functional team is superior to the structure previously mentioned.  The team members work closely and simultaneously together often in the same project room. The DM from the BI/DW team sits in on the BA’s interviews with the business users and starts to construct the logical model design. The dimensional modeler can ask clarification questions directly to the BA and business user at any point in the process. Furthermore the evolving logical model design can be frequently replayed to the rest of the team to confirm it. In my experience the cross-functional team has been more successful than the polarised BA and BI/DW (chasm-forming) teams. BEAM takes the concept of the cross-functional team much further and provides an intelligent and effective framework for the BA and BI/DW teams to work together in.  Following the BEAM method is an effective antidote to creating BI Breakpoints.

WHAT IS THE BEAM METHOD?

BEAM stands for Business Event Analysis and Modeling.  As the name suggests it combines elements of requirements analysis and data modeling. Its key concept is to use 6 dimensional types and a measure type (the 7Ws) to identify and then elaborate business events. BEAM concentrates on business events rather than known reporting requirements so as to model whole business process areas.  This provides a major advantage.  Modeling a business process area yields a design that can be readily scaled as requirements grow. Modeling for a set of reporting requirements alone can lead to a narrow solution. ‘Narrow’ because the design may not lend itself to be scaled when new requirements are on boarded. Therefore, the BEAM approach helps avoid the BI Breakpoint of non-scalability. BEAM’s 7W approach also lays a solid foundation for ad-hoc reporting and self-service BI by teaching business users – by stealth – to think dimensionally.

The 7Ws used by BEAM are: Who, What, When, Where, How, How Many, and Why.  A similar conceptual technique is used in investigative journalism to ensure full story reporting coverage. For a specified business process area the BEAM idea is to identify event stories by asking a ’who does what’ question and then expressing the answer as a simple story.  An example of this would be ‘traders buy commodities’.  A series of these ’who does what’ stories are captured and then the remainder of the 7Ws such as the ‘when’ and ‘where’ are asked to drive out their interesting details. All the results are documented in a BEAM table template.

The BEAM table template is one of several tools employed, you will also learn how to use the BEAM tools of hierarchy charts, timelines, event matrices, and enhanced star schemas. The BEAM method will then take you through modeling events, dimensions, processes and star schemas to provide working software and documentation as detailed in the Agile Manifesto.

From the information gathered in the Business Event Analysis stage it is then possible to easily identify dimension and fact table types. Dimension and fact table patterns are explained in the second half of the book  ‘Modeling’.  If you are new to dimensional modeling you will learn much from the vast design and implementation experience of Lawrence and his co-writer Jim Stagnitto. The BEAM method and notation walks you through a natural continuum from the interview stage right through to the end dimensional model.

When the BEAM method is properly understood and implemented it will effectively bridge the gap (BI Breakpoint) between the BA and the DM. Both the BA and the DM can work together using BEAM or for someone with hybrid skills the two roles can become one. In summary Agile Data Warehouse Design is a thoroughly well written book that addresses BI Breakpoints and brings with it four key benefits.  It will show you how to practically apply an effective combined analysis and modeling method (BEAM). It will help engage business communities so that full business process areas can be modelled making your solution scalable. It will lower costs to the business by reducing analysis and modelling time. It will reduce the risk of a project struggling by delivering working software and documentation on time.

You can buy Agile Data Warehouse Design from Amazon and find out more about BEAM and matching agile/dimensional modelling courses on Lawrence’s Decision One Consulting web site.