The answer is 42 - what is the question?

The answer is 42 - what is the question ? (regarding our logo)

Holisitic Data Warehousing Book and free Template is now launched.

Today our Holistic Data Warehousing book and free downloadable & universal Data Warehouse Template for SQL Server 2008 has been released.

The book is not the typical IT book that is written by I.T. professionals for I.T. professionals/students. Instead we are both business people with vast experience in using I.T. for reporting purposes and the book is mainly directed at fellow business people with good skills in spreadsheeting, databases and extracting data.

The template is groundbreaking in that it is designed for any business to use without modification to implement a full, across the business, data warehouse. The book fully documents the model, explains our take on data warehousing and explains the strategy behind our idea of Full Supply Chain reporting. The template download includes some reporting models that enable Full Supply Chain reporting from the one system. Also inlcuded in the book is documentation of a comprehensive load from the Microsoft Adventure Works SQL database for demonstration purposes.

We expect the book will receive mixed reviews with some being very critical and others favourable. Critics will make the point that our methods are very basic and not "correct" according to standards that have been established over past decades. We acknowledge that the Holistic Data Warehousing methodology is rule breaking but this is a deliberate attempt to simplify the model as much as possible. There is a chapter in the book devoted to the topic of data warehousing rules. The Holistic model was built with current and future technology in mind and would have been practically impossible to use 10 years ago for mainstream businesses.

As a business person, with some experience with databases such as Microsoft Access, your will learn from this book how to implement a full data warehouse and to do it yourself. This is not just any data warehouse it is the "Holistic" data warehouse that has been designed by the Authors to provide a system capable of virtually any business intelligence reporting.

The only cost to your business to implement Full Supply Chain reporting using the Holistic Data Warehouse is your time, Microsoft SQL Server and Windows Server software and a server class PC. This can be done for as little as $30,000 depending on the Microsoft server license costs.

42sight.com exists to support the users of this new data warehousing methodology. Existing literature explains the complex traditional methods. Our book and this website will for a long while be the main source of information on how to use this new method. We expect our methodology to not gain acceptance amongst the supporters of the traditional data warehousing methods. This website will provide a focal point for supporters of the Holistic method to help each other and for resources to share.

By purchasing the book you are gaining full documentation of the model and are helping to support the authors to support the Holistic Data Warehousing community. If you like our book please "review" it on Amazon and pass on recommendations to your colleagues and acquaintances that might be interested in the book. Our goal is to "free-up" data warehousing and making it affordable for the masses of small to medium business operators.

You will find links to purchase the book, along with links to the model and links to an extract from the first few chapters from the book all on the blog.

Addendum, typos and grammatical error log

We know there are small errors throughout the book including some in very ironical places

Please feel very free to "comment" any errors (typos or grammatical) that you find. We will include in this post any corrections too.

Latest Update on the book

We are in the final stages of preparing the book for printing, aiming for it to be available from Amazon in the next 4-6 weeks.

The E-book version will be available sometime later on iBooks as the Kindle option is not a viable proposition for us.

We will provide a free extract of the 1st few chapters, the free Microsoft SQL Server Holistic Data Warehouse Template, demonstration load using the Microsoft Adventure Works data and reporting models will be available at the time of publishing. This blog will have links to all of these.

Become a follower of the blog and recieve automatically the latest news on the book.

Using Visual Basic to process a large text file vs using SQL Server

Earlier this year I rose to the challenge on the Microsoft “Transact-SQL” forum where someone was trying to “parse 1 million records in one minute” using SQL server.

The problem was basically to take a large text file with “60 million rows resulting in 200-300 million rows”. Their best attempts using SQL server were taking 9 hours. The data appeared to be generated from a television viewer tracking system and these records needed to be processed every night before being analysed in SQL server.

Every solution that was being offered using SQL server had apparently been tried.

My alternate demonstration using Visual basic (within MS Excel) took 4 minutes per 1 million records and this was using a basic laptop. This was a time saving from 9 hours down to 1 hour and even better if a server was used.

It shows that it is best to use the right tool for the job - even in the world of I.T.

Google ‘social.msdn "parsing data" "transact-sql" ssdl gerry phillips ‘ to find the thread with the “answer”.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d868cb65-90bd-4857-9fc5-521e35e73ef9

Update on the Book

Despite Gerry being on a world trip for work we have been making good progress on the data warehousing book. Here are a few summary points about the book

The book will cover our Data warehousing strategy & philosophy and the Holistic Data warehousing method and will include a free download of the working template.
It will have full documentation of how to use the Holistic data warehouse template
The book is currently just over 300 pages with over 225 colour screenshots and diagrams.
It will be printed in black and white for hard copies and in colour for ebooks
We are optimising the graphics for presentation in the ebook format for display on the Apple iPad - using the (free) Kindle app.

We are expecting the book to be published within 3 months and available on Amazon and Kindle only.

Stay tuned

Experimenting with Recursive Common Table Expressions (CTE)

Having read Johnathon Parker's excellent post on how Recursive CTEs work in SQL Server
http://jonathanparker.com.au/archive/2007/04/16/recursive-common-table-expressions-ctes-in-sql-server-2005.aspx

We decided to try it out on something more complicated

The challenge we gave oursleves was to take the "SalesQuota" table which is in quartlery buckets and split it into monthly Buckets.

This can be acheived with 3 queries in Union as follows:

SELECT SalesPersonID, QuotaDate, SalesQuota / 3 AS SalesQuota
FROM Sales.SalesPersonQuotaHistory
UNION ALL
SELECT SalesPersonID, DATEADD(m, 1, QuotaDate) AS QuotaDate, SalesQuota / 3 AS SalesQuota
FROM Sales.SalesPersonQuotaHistory AS SalesPersonQuotaHistory_1
UNION ALL
SELECT SalesPersonID, DATEADD(m, 2, QuotaDate) AS QuotaDate, SalesQuota / 3 AS SalesQuota
FROM Sales.SalesPersonQuotaHistory AS SalesPersonQuotaHistory_2

An alternative Solution using Recursive CTE is as follows:

WITH SPQ AS (SELECT SalesPersonID, QuotaDate, SalesQuota / 3 AS SalesQuota , 1 as c1
FROM Sales.SalesPersonQuotaHistory
UNION ALL
SELECT SalesPersonID, DATEADD(m, 1 , QuotaDate) AS QuotaDate, SalesQuota, c1+1 as c1
FROM SPQ
WHERE c1<3)
SELECT SalesPersonID, QuotaDate, SalesQuota
from SPQ
ORDER BY SalesPersonID, QuotaDate

This repeats the second part twice in a recursive manner - providing the same results as the 1st triple union query.