Greenfield Data Warehouse - Problems faced and solutions found

I've been leading the building of a greenfield data warehouse - among many other things - for an SME over the last 18 months. My team work closely with an excellent Analytics department who are responsible - among many other things - of translating the data we provide into usable MI for stakeholders. 

We recently presented the stakeholder focused, agile methodology we've adopted to another part of the organisation who require a reboot of their current data management practices and will be building a data warehouse of their own either with our guidance or under our direct control.

The two slides featured here show the before and after position. Although it has to be said that our project is far from finished we have nonetheless built a solid foundation on which we can expand. Both from a technical and business point of view our solution is very pragmatic. We knew all along that anything we built, whether process or technical solution, would have to be modular so it could adapt to the changing environment present in the vibrant, fast-moving and maturing SME in which we operate.

The Difficulties

As the business has grown the number of systems has grown and so has the complexity.

This creates challenges unique to us but comparable to challenges faced by all businesses of our size and composition.

We began back in January 2012 and at the time we didn't have many of the foundations we required.

1. Our data is mostly insurance premium and claims but from many different sources.
2. Within that data there were technical differences but also semantic differences so the same thing is known by different labels in different systems
3. There was no existing method of sharing data with colleagues from a central source
4. Even if there had been an MI Portal there was no central source for reporting from which such a portal could have been fed
5. The DBA and Analytics team worked together but none of us was dedicated full time to this project
6. We didn’t have a plan or a PM to drive it forward
7. And everything was needed yesterday

The Solutions

1. We identified an existing mid-range under-utilized server and declared it our reporting environment. Where ever possible we switch reporting – which was still running on our live servers – onto this environment. It gave us the power to run resource intensive processes away from any system that serves customers.
2. We were already a business run on Microsoft software by colleagues comfortable with that environment. It made sense to use SQL Server as our Business Intelligence back-end.
3. We already had data distributed across multiple servers and we expect that environment to get more complex over time. We created replica’s of all the reporting data sources on our reporting environment. Now we had all the data in one place but it was still in different formats.
4. Next we identified key business areas – led by stakeholder requirements – and we built data marts for over each process. We did this sometimes across multiple data sources so we had to create a translation layer between the inputs and the outputs.
5. We then shared the data to users via Microsoft Excel, as it was and remains today the easiest tool we have to get data in front of consumers. The Excel workbooks we provide are dynamic cubes which means they are connected to the reporting databases and function like massive pivot tables.
6. The early developments were really only straw men that we used to create a feedback with users until we were happy we had accurate data in the shape required to satisfy “all reasonable questions, and some unreasonable questions too.” Without an Agile approach we wouldn't have progressed as fast.
7. We work closely with the stakeholders. Without there constant input, testing and feedback we may have followed the unfortunate path that many IT geeks take of building a gold-plated technical solution which is of no use to the business.
8. We've never pretended that the data we present is perfect. Recently we uncovered a few glaring mistakes, not with our development, but in an underlying third-party database. Before we found that issue we were blind to it. Now we have our single view of the facts we’re discovering errors in previous reports and processes.

In Summary

It's horses for courses. As a business intelligence architect it's important to balance stakeholders and technical needs. There are many technical elements of our data warehouse which I would love to spend more time on as I love that type of work. But the opportunity to build a data warehouse solution from scratch comes with an implicit responsibility to provide ROI from day 1.

Big Data in the Real World - Part 2 of 2

Part 1 of this series described the first 2 stages in the processing of clickstream data to allow sales conversion analysis. This post describes the most complex part of the process - the pivoting of Key-Value-Pair data into a flattened schema.

Loading New Data

The third element to the process is transforming the KVP data from staging into a persistent schema. There are many reasons for collecting this data – it is a rich dataset - and each reason may require the data in a different format. We don’t know what analysis will be done in future on the data or the data format that analysis will require. As a result, we continue to store the data in string format for now and assume any future analysis will have to construct a layer on top of the base collection or extract the data into further persistent storage if specific transformations are required.

The staged data is still in a KVP format. This is difficult for users and report writers to consume so we pivot the data to create 1 row per click event. This has the effect of turning 150 narrow rows of KVP data per click event into 1 wide row in the persistent schema with a 90% reduction in storage space.

Isolating the click events

To achieve this we isolate the click events within the data. Each event has;

• SessionID stored as a GUID
• BornDateTime timestamp stored as a datetime
• StepID which is generally an integer but can contain characters so is stored as an NVARCHAR(3)
• LayoutID also stored as an NVARCHAR(3). 

LayoutID is rarely used but exists in two table Log_Flow and Log_Quote but not Log_Sales. It is still part of the unique key for these tables.

StepID is used but in only Log_Flow and Log_Quote.

Together these 4 columns constitute a unique key across a single click event. We use them to create a Primary Key in the persistent storage tables.

Step one of the SSIS process identifies each distinct unique within the data. These keys are then inserted into the corresponding persistent storage tables in a DataSource schema as shown in the table below.

Staging Table	Persistent Table
Staging.LogFlow	DataSource.ConversionLogFlow
Staging.LogQuote	DataSource.ConversionLogQuotes
Staging.LogSales	DataSource.ConversionLogSales

Pivoting the data into the persistent schema

Each set of key columns we detected in the data and insert into the persistent storage schema as a row must be completed by updating the rest of the columns (the non-primary key columns).

We do this by joining between the Staging and DataSource tables on those recently isolated primary keys. But we don’t know which columns we’ll be updating because, as we found out in the second stage of the process, the schema can change. The only way around this was to use a dynamic SQL statement which lists the columns required to be updated. These columns must be source from the metadata we stored about the KVP data during stage 2 of the processing.



We build up variables and ready for the dynamic SQL through a series of steps which gradually build up the list of columns and augment the SQL to construct a valid SQL Update statement. The ultimate aim is to populate one VARCHAR(MAX) variable and then execute the dynamic SQL. That dynamic SQL uses a Common Table Expression which itself utilises the PIVOT function. The CTE and PIVOT reference the Staging KVP data and turn it into rows for which the subsequent Update statement then uses to populate the persistent schema.

The final element is to Update the process control table which stores the last successful timestamp.

In conclusion

Upon successful collection of data we’re able to use this data to measure conversion rates on our websites. Each GUID represents a session and we can measure the number of quote and sales resulting from that session. This data undergoes some further transformation before it’s loaded into a Clickstream Fact table and processed into an OLAP cube hosted by SSAS. The collection process is automated from raw data through to the final OLAP cube and requires very little maintenance. At present we collect about 100K rows which equates to 70Mb of new data daily.

The only issues found to date was after the number of KVP columns increased and the dynamic SQL which uses a list of those columns exceeded 8000 characters. That failed the process for a few days until we could fix the issue. The fix involved trimming white space from the dynamic SQL to reduce the character count but this is not a solution merely a temporary fix. A better solution is to break the dynamic SQL into segments and to run separately or to identify unused columns and ignore them.

Previous DBAs failed to get anything useful out of this KVP data primarily because they looked at the problem inflexibly. One had built a report over the raw data and once the data changed the report failed. Another had used nested cursors – OMG!!! - to perform the data loads and that process was so slow it never succeeded. Finally, it was outsourced to Mumbai with a poor data brief and without stipulating that the process had to be automated. As a result, the process was defined in a series of rigid steps which were followed by some poor soul manually and then emailed back to the UK for analysis. It regularly came in different formats and was constantly over 2 weeks late.

The current process took a couple of months to get right but now works automatically every day. It’s flexible and we’re able to use it for detailed analysis. We no longer pay the outsourcers a significant amount per month for poor quality data. Above all it was a really challenging but fun piece of development.

Big Data in the Real World - Part 1 of 2

There is so much talk of Big Data nowadays, mostly from sales people but also from technologists. Karen Lopez aka DataChick took issue at the Wikipedia entry on Big Data which describes it as "data sets so large and complex that it becomes difficult to process using on-hand database management tools or traditional data processing applications". I think that definition is pretty good.

Over the last few months I've tackled a problem which within my environment counts as a Big Data problem. The solution is outlined in these next 2 articles.

Clickstream Conversion Data Collection Process

The daily process

Clickstream data is collected on a schedule, currently daily, via an SSIS package which is initiated by a step in a SQL Agent job. The underlying package uses the timestamp of the last successful load as a starting point for the next process which allows flexibility in the collection schedule.

The data source

All clickstream data is collected in a Key-Value-Pair (KVP) database. There are 3 main tables used to store the KVP data. Those tables are;

• LogFlow - information about users web journey 
• LogQuote - all data related to a quote 
• LogSales - all data related to a sale 

Only the last 5 months of data stored. This is a hangover from legacy environment where storage was limited. We wish to retain all the data so we collect it each night and store it in our Data Warehouse.

Staging data

As we perform a number of actions on the data once collected and we don’t want to run those actions over highly transactional replicated tables a set of staging tables exists which mirror exactly the schema on both out production and replication servers. Diagram 1 shows the SSIS steps for this section of the process. The staging tables are truncated to remove data from the last process before data flow tasks collect the data since the last successful load. This achieved using a view of the source table filtered to return only the rows created after the last datetime in the process control metadata.

The KVP data is large due to the volume of transactions and because the values for each row are contained in NVARCHAR(1000) format. For each action on our website over 150 rows are currently written to database. Often the data in the NVARCHAR(1000) key value column only uses a fraction of that space. It’s a drawback of the KVP implementation and as a result the database is approximately 90% smaller when converted to a relational schema.

Schema processing

Following on from a constraint for the KVP implementation we have an advantage. The KVP key names can be added to at any time which allows us to collect new data without extensive reconfiguration work. However, this means when collecting the data we cannot transform it into a rigid schema because the schema may change.

In addition, we can’t constrain the data within the key values to definite data types as this may also change therefore we continue to store the data – at least during collection – as NVARCHAR columns.

Finally the length of the data within the NVARCHAR columns can vary. We know the maximum lengths in the current data for each key name but that could change in future consequently we have to allow for potential changes.

To account for these data constraints we must scan the incoming data for new key names and key values which are larger than previously seen before each data load. If we detect new key names we must create a column to store that data from now on. If we detect a key value has been collected which is larger than the current NVARCHAR length in the storage schema then we must expand that column. These two actions require prior knowledge of the key names and the length of the key values which we store in a single metadata table. This table continues to be used throughout the remainder of the collection process.

This post is continued in part 2.

Pragmatic Principals - Agile

The Data Warehouse exists to provide Business Intelligence. Its users will not always start with the end in mind. They will browse, discover, probe and dig. As designers it is our responsibility to construct solutions that allow users to answer questions which emerged as they were browsing. 

How then, can we ask for a precise specification document typical of waterfall projects? 

Sure there are elements we can map out in advance; Dimensions we know will be needed,architecture which will form part of the ETL process. But not everything can be defined.

My preferred approach is to get a proof of concept up and running quickly. This stirs the creative juices of the business users, especially those who think visually, and leads to feedback which drives the design and development forward.

This Agile is not the same as the Agile methodology, but it works. 

Data Warehouse High Level Overview

Most data warehouses have multiple data sources. Conforming similar, but not exactly the same, data from a variety of systems whilst retaining an ability to rollback to a previous version of the truth presents significant challenges. Data from flat files adds a further challenge. In addition, a variety of front-end tools need to be supported which ads a requirement for the presentation areas of the warehouse to be generic enough to support these tools plus others we don't yet know about.

The main elements of my high level design are;

• A Metadata control database 
• Replicated source data with change tracking enabled.
• An ETL staging area with no persistent data
• A data warehouse for persisting the data
• Data marts for specific business focused OLAP cubes
• SQL Reporting services

Metadata

I like SSIS but I dislike having to fix other peoples poorly written packages. I spent over 3 weeks in my last job documenting the processes buried in SSIS tasks so I could understand 1 of the 2 main ETL loaders I was supporting. The process had been well designed but never documented and relied too heavily on SSIS when a parameterised stored procedure would have been more transparent and easier to amend 2-3 years after the system had been operating.

The lesson I took from that was to use SSIS where appropriate. I believe T-SQL can achieve many, but not all, ETL tasks better. I like to use SSIS to drive parameterised stored procedures where the parameters are sourced from a metadata database. So for example, During ETL cleaning I iterate through identified cleansing steps using an SSIS package that references a metadata process table. This enables me to add, amend, enable, disable or sequence each cleansing step. And as I add more data source to my warehouse all I have to do is add rows to the cleansing process table and the automated SSIS package picks up the new processes.

The Metadata database acts as the brain of my warehouse design.

Replicas

Change Tracking and Change Data Capture in SQL Server offer a way to identify data which has changed since the last load. Both come with performance overhead so I don't implement them over production databases. By Replicating those production databases, or just the elements we need for data warehousing, we suffer less overhead and we can work on a copy of the data source in an ETL environment without the constraints of a production server.

By appending the change tracking number to our imported rows we can roll back to a previous version of the truth to fix issues and then roll forward again when ready for up to 90 days depending on the change tracking history you store.

ETL Staging

Data does not persist in the staging element of the warehouse. It simply migrates through on it's way to a cleansed and conformed schema. The only exceptions are bad data we don't want to allow through which often requires manual intervention.

In the element all the cleansing and validation of data is performed. No users, other than the DW Admins, have any reason to be here.

Data Warehouse

Once we've established a clean set of data we can conform it. Data warehouses tend to store data from multiple sources and what at first appears to be common data is often of different data type or length and sometimes even different collation. That's no good to our dimension model so we need a place to store conformed data before it goes into the organisations reporting objects.

It does mean storing a lot of data but the cost of ETL is high and once you've got it right you want to keep it.

I follow the Kimball methodology quite closely but he probably wouldn't like this. But I try to be pragmatic not ideological and I've seen this method work really well even in organisations were data quality was high. For organisations with data quality challenges this could be a real benefit.

Data Marts

Terminology gets confusing sometimes. To me a data mart is a subset of the organisations data conformed into a model focusing around a specific business function. Examples would be underwriting performance, click stream analysis or stock control.

In my data mart database there are dimensions, both common and those specific to a data mart, as well as facts tables. Nothing else. Users may have access to this area and/or the data warehouse so I used schemas to both secure and for ease of identification.

Reporting Services

Even in an OLAP reporting world, traditional canned reports still have a place. SQL Server SSRS Report Manager's ability to distribute data is invaluable and it comes with a nice front end. I advocate using it get OLAP and non-OLAP reports to users.

In Conclusion

This high level overview outlines my preferred architecture. I believe it answers many of the questions I've faced. But I'm keen to improve it. If you have any feedback that might help improve it just comment below. After all, we're standing on the shoulders of others.