Building Social Analytics with MS BI

Every form of analysis needs data, but it's not possible that one might have that data generated and stored in organizational repository. Many forms of analysis depends upon data from third-party, and platforms like Windows Azure Marketplace are based on the same principle.

Social Analytics is widely used to forecast the impact on the business and extract insights to counter the same. The interesting question here is, what is the data source that can be used to calculate / derive sentiments of customers related to the respective business ? A majority of this data would come from social / professional / collaboration forums. Examples of such sources are Facebook, YouTube, Twitter, LinkedIn, PInterest, IMDb, Blogs etc. Anyone would agree that the analytics derived from unstructured data created by the public interaction on social media can be expected to be much more close to precision than even any data mining algorithm. But the big question here is, the amount of data - very very very big data. On a daily basis, there are 400 million Tweets, 2.7 billion Facebook Likes, and 2 billion YouTube views. Even these figures might have been outdated today.

Say an organization is influenced by Sharepoint 2013 enhancements related to social media collaboration, and intends to add an ability to derive sentiment analysis in their client offering. Let's say that as a starting source, Twitter is selected as the source of data, and all the public tweets for a particular product would be analyzed and the results would be stored for future use. 

The first challenge is that according to a study, Twitter generates approximately 1 billion tweets in less than 3 days. So how to deal with processing such a huge amount of unstructured data and just consider the kind of infrastructure required to handle this processing. To proceed with the case study, let's say that we live in the age of cloud and we just signed up on AWS and have beefed up a fat Amazon EMR that uses Hadoop and HBase NoSQL database.

The second challenge in this case is how to get access to Twitter Firehose - an API that provides streaming access to Twitter public tweets. One needs to partner with Twitter and pay millions of dollars to get licensed access to it's sea of unfiltered dataset. Also you would need rights to publicly sell this dataset to your end-clients. Considering this complexity any organization would give up the idea of implementing it for own use.

Sometimes the answer to the problem is not technology but it's partner technology. Only three publicly known companies have licensed rights to Twitter's Firehose - Topsy, Datasift, and Gnip. These companies have established partnership with hundreds and thousands of social media platforms, established a web scale and google inspired flavor of infrastructure based on Hadoop clustering methodology, and also have been maintaining a huge archive of historical social data. On the top of it, these providers provide real time access to live stream of social media and also provides social analytics using intelligent methods. An interesting case study of how Datasift manages infrastructure for huge processing, storage and analytics can be read from here.

How MS BI is related to it ?

Even if one selects to sign-up with any of these providers and source analyzed data from them, one would have to keep storing the results. These providers have pay-per-use pricing model depending upon the selected source. After  intelligently extracting analyzed data from different sources through these providers, one would have to warehouse the same to avoid paying repeatedly for the same data. Considering the volume of data, even if analyzed data from these social media providers is warehoused, it would easily create a huge warehouse of data.

Microsoft have two different flavors of analysis models (Tabular mode SSAS and OLAP mode SSAS) under the BISM umbrella and a very strong set of end user collaboration platforms including Sharepoint and Excel. Analyzing the warehoused data from social analytics providers with MS BI and including the same in solution offerings can be a deal breaker than implementing complex data mining algorithms or such methods.

I would really like to hear what Microsoft thinks about my idea around social analytics with ms bi. Anyone reading this post is interested in sharing their thoughts about this idea, I would be more than happy to receive the same.

Data warehouse certification , Business Intelligence and Analytics certification

In Data warehousing and Analytics, lack of standard certifications have made it very hard for recruiters to identify "A" league professionals from impostors. When the topic of certification comes, there are basically two questions, why to get certified and how and what to get certified. I would first answer what and then answer why. 

Irrespective of technology, The Data Warehousing Institute (TDWI) has been a provider of Certified Business Intelligence Professional (CBIP) certification where professionals can choose their core skills and appear for the exam that suits the same. The flip side that professionals see to it is that it's not associated with any product in specific and just theory can be good at Architect profile but at career levels where professionals need to implement solutions this might not be able to impress recruiters with this certifications.

Being a Microsoft patron, I would discuss about certifications related to MS BI. Microsoft has recently introduced two certifications for Data Warehousing and Business Intelligence.

1) Exam 70-463 : Implementing a Data Warehouse with Microsoft SQL Server 2012

Skills Measured:

• Design and implement dimensions
• Design and implement fact tables
• Define connection managers
• Design data flow
• Implement data flow
• Manage SSIS package execution
• Implement script tasks in SSIS
• Design control flow
• Implement package logic by using SSIS variables and parameters
• Implement control flow
• Implement data load options
• Implement script components in SSIS
• Troubleshoot data integration issues
• Install and maintain SSIS components
• Implement auditing, logging, and event handling
• Deploy SSIS solutions
• Configure SSIS security settings
• Install and maintain Data Quality Services
• Implement master data management solutions
• Create a data quality project to clean data

2) Exam 70-467 : Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Skills Measured:

Keep in view this exam has approx 30% weightage on designing and planning of BI Infrastructure.

• Plan for performance
• Plan for scalability
• Plan and manage upgrades
• Maintain server health
• Design a security strategy
• Design a SQL partitioning strategy
• Design a backup strategy
• Design a logging and auditing strategy
• Design a Reporting Services dataset
• Manage Microsoft Excel Services/Reporting for SharePoint
• Design a data acquisition strategy
• Plan and manage reporting services configuration
• Design BI reporting solution architecture
• Design the data warehouse
• Design a schema
• Design cube architecture
• Design fact tables
• Design BI semantic models
• Design and create MDX calculations
• Design SSIS package execution
• Plan to deploy SSIS solutions
• Design package configurations for SSIS packages

Why to get certified on Business Intelligence platform ? There are two groups of people divided by faith in certification, one who believes in certifying and benchmarking their skills, others who do not feel there is any value in investing and getting certified.

1) For those who are not in the favor of certifications, the prominent reasons are:

• Product version changes after few years and the certification is seen as outdated by recruiters
• Software required for the same for practicing is not available easily
• Many professionals indulge into malpractices, use exam dumps and pass exams with full score even without any knowledge of the subject
• Its hard for them to self-study and prepare for certifications, and they don't have enough resources to invest into a professional training programme.

2) For those who are in the favor of certifications, the prominent reasons are:

• Certifying your skills with changing product versions reflects your attitude to your employer, about how seriously you take your skills that earns your bread and butter.
• With cheap developer editions coupled with free virtualization software like VirtualBox and readily installed images in VHD format, resource management is possible for them.
• Professionals who pass exam using corrupt methods are digging a backfire gunshot for themselves as they are raising expectations from them, and inviting their interviewer to screen them more thoroughly as they are certified professionals.
• Those who can't self-study in IT and can't manage in investing for resources and training programmes for themselves, have already surrendered to the thought that one or other day they would go obsolete in technology. And delivery management or other avenues are right for them than remaining technical. Even in that area, certifications like ITIL or PMP or PGMP would be required.
• Certifications adds bargain power to your resume to negotiate better for your skills as you go up the ladder. With a couple of years of experience and few certifications you can't ask for 1.5 times the salary that your peers get paid, but the benefit is when your experience meter hits double digit in number of years, you won't be appearing your first ever certification at that age and experience !!

Summary: Invest in your career. Many would ask what certifications have I appeared till date. I am certified MCSD in Microsoft .Net, MCTS in SQL Server Implementation and Maintenance, MCTS in Business Intelligence and MCTS in Performancepoint Office Applications and now working as a senior architect with Accenture Services Private Ltd at Mumbai office. Still I am interested and positive to take up above mentioned certifications.

Columnar Databases and SQL Server Denali : Marathon towards being world's fastest analytical database

Have you ever heard of what are columnar databases? You might be wondering this is something new - The answer is No and Yes. Columnstore is not a new technology that has evolved suddenly and is making waves in the database community. It has been in the industry for quite some time. Generally database stored data in the form of records which resides in tables. The storage topology is typically known as rowstore as records are physically stored in a row based format. This methodology has its own advantages with OLTP systems and limitations with OLAP systems. The main advantages of columnstore are better compression, reduced IO during data access and effectively huge gain in data access speeds. Scaling data warehouse computing resources by scaling memory resources and using massively parallel processing does not fit with every business due to budgetary and architecture constraints. Columnstore seems to be a breakthrough technology to play the role of a catalyzer in analyzing enormous amount of data of the scale of billions of records, from enterprise data warehouses.

One of the best examples of columnar database success stories is ParAccel - one of the world's fastest analytical database vendors. Gartner in its latest report, has positioned ParAccel in visionaries category in the magic quadrant. You can get a deeper view on how ParAccel harnesses the power of columnar storage from it's datasheet and a success story.

Microsoft seems to have started its marathon in adding the nitro to SQL Server for adding data access speeds to DBs for OLAP engines. SQL Server Denali is introducing a new feature known as columnstore indexes, know as project Apollo and you can read more about this from here. This is just the first spark in the race of being one of the worlds fastest analytical database, a market into which IBM, GreenPlum, Kognitio, ParAccel and others have already plunged quite some time back. In-memory processing engine like Vertipaq combined with columnstore indexes can yield some blazing speeds in data warehousing environments. Time would tell what is the strategy of Microsoft to incorporate this concept in SQL Server and how SQL Server community reacts to it. Whatever be the case, it's a welcome news for end clients as of now.

Data Warehousing and Analytics on Unstructured Data / Big Data using Hadoop on Microsoft platform

A major community of data warehousing professionals grow up from the old school of Kimball and Inmon methods of data warehousing. Lots of professionals do boast on virtualization, complex MDX querying, performance tuning OLAP engines and managing data warehouse environments of the size of a few hundred GBs or several TBs, as the most niche and challenging jobs they have on their resume. But there is another world of data warehousing and analytics which most would not have explored, and slowly this revolutionary and emerging wave is reaching SMBs which would effectively challenge the world of data warehousing as we practice today. You might come across a question while reading this post, that what has Microsoft to do with it and answer to this question is towards the end of this post.

Data warehouses and data marts developed using Kimball, Inmon or any hybrid methodology can deal with structured data and have scalability challenges too. Appliance solutions such as Parallel Data Warehouse are Microsoft's candidate to deal with such challenges. Some might think that this is the answer to warehouse largest volume of data and build analytical capabilities on the top of it. But this data volume is just a very small piece of the ecosystem. According to Gartner, enterprise data would grow by 650% in 2014 and 85% of the same would be unstructured data, which is also termed as BIG Data.

Have you ever thought of how organizations like Yahoo, Google, Facebook etc organize their data? Which databases do they use? Whether they have data warehousing and analytics? These organizations have some of the largest data volumes in the world. For example, Facebook is heard to have 12 TB of compressed data added per day and 800 TB of compressed data scanned per day. Can you imagine structuring such volume of data using ETL, storing it in data warehouses, aggregating it using OLAP engines in data marts and extracting analytics out of the same ? To handle such volumes of data for data warehousing and analytics, innovative technologies and infrastructure design are required that can support massively parallel processing, and the one I am talking about is named "Hadoop" which is an open-source distributed computing technology and "Hadoop Distributed File System" which is the storage mechanism for handling unstructured data.

Cloud environments like Amazon are already supporting Hadoop, organizations like Cloudera and IBM are supporting commercial distributions of Hadoop, and a lot of big and famous international business majors are already using Hadoop implementation. The biggest implementation is used by Yahoo with 100,000+ CPUs running on 40,000+ computers running Hadoop. An exhaustive list of organizations using Hadoop can be read from here. Organizations are using Hadoop to implement data warehousing and analytics for purposes like Event Analytics, Click Stream Analytics, Text Analytics and more.

For those who are completely afresh to this part of the world, can go through some very interesting reference material mentioned below:

1) The Google File System

2) Data warehousing and Analytics Infrastructure at Facebook

3) Apache Hadoop Wiki

4) Apache Hadoop MapReduce Implementation at Yahoo

5) Setting up Hadoop on VM

Microsoft is aware of the challenges using unstructured data and Hadoop, and is gearing up slowly for the same.

1) Microsoft Research is developing Project Daytona on Azure platform and Project Dryad, which is perceived by the industry as Microsoft's candidate as an alternative for Apache Hadoop.

2) Those who believe that MDX is the top query language that can deal with huge amount of data from OLAP engines, should check out LINQ to HPC to update their GK.

3) With the increasing popularity and success of Hadoop, Microsoft is also supporting Hadoop on Azure platform. You can get an idea of how to deploy Hadoop cluster on Azure platform from here.

The way Microsoft professionals felt that cloud is something new when Azure was introduced, same would be the case when Microsoft would start supporting Hadoop commercially or introduce a commercial alternative for the same. But neither cloud is a recent invention nor technologies like Hadoop to handle, ware house and analyze unstructred data. In my viewpoint, architects and organizations should develop their readiness to deal with the emerging winds of change and upcoming potential business opportunites that unstructured data can offer.

Data warehouse planning tool

Data warehouse planning is practiced more as an art than as a science. Art is something that one learns with a gifted skill to pursue the craft and deliver the end product. While science is something that anyone with the correct logical algorithm can apply to create the end product. Consultants step in with their own set of questions and start a psychometric analysis class in the office of a CxO of the company, this is a typical way in which most of the data warehouse planning and assessment starts. The end users or the analysts with the organizations have very little clue of why the questions are being asked and what would they derive from it. If someone from the IT Operations of the enterprise approaches the consulting firm regarding the credibility of the process, consultants would pull out a Data Warehouse Toolkit book and justify their theory, making clients almost perceive that data warehouse planning is an art not a science.

How can data warehouse planning become a science ? If the regular set of processes defined to plan and asses data warehouse engineering are available, and the same processes are built into a tool in the form of a workflow, then it becomes science. Any reasonably experienced data architect / BI analysts can use the tool, understand and fill up the workflow with data points and create an assessment, plan the model and come out with a prototype to evaluate the design of a prospective data warehouse.

In the Microsoft BI world, till date the most easy and popular tool of choice for planning a data warehouse, in my knowledge, has been the data warehouse modeling worksheet available from Kimball's Data Warehouse Toolkit book. This worksheet helps at the requirements gathering and modeling level, but not much at the planning level.

The motivation of this post is a new tool that has hit the BI market, and the name is WhereScape 3D. Presently as of the draft of this post, this tool is available as a free trial beta. I gave a try to this tool, and it seems quite of the modeling flavor. In my personal opinion, the tool is not that self explanatory even for a BI Analyst to start using it in a fast track manner, it would require the analyst to learn the tool for a day or two and then start capturing requirements and planning for the warehouse. The striking features is the documentation part, and its one of the unique tools I have come across till date that help the user in modeling the requirements itself to plan the DW right from source systems to data mart. You can read more about this tool from here and download the same from here.

At this time, I am not sure too much about how effective is the tool, but I am pretty impressed by the value it aims to bring to the table and that is indeed one area that has not been targeted by BI product vendors till date. I am sure this is the start of a new race where vendors would start converting enterprise class DW to science, rather than restricting it as art !!

Creating data marts from data warehouse : Architecture Design considerations

In a typical BI architecture, the regular layers that you find in an architecture diagram are source systems, ETLs, data warehouse and reporting layers. But whether to encompass data marts into your architecture is one such design decision that demands some convincing reasons.

Below are a few scenarios when you might want to consider creating data marts in your architecture design.

1) Customization for business units: Different business units of an organization can need their own version, shape and volume of data which would be originating from a set of common source systems. Customization to this degree is not possible at the data warehouse layer, so independent data marts can be created to cater this requirement.

2) Performance Optimization: A single cube / sets of cubes created out of a single data warehouse and sourcing data from the same data warehouse can be real challenge to performance. By creating data marts you can divide the load depending on the user base and corresponding volumes of data access.

3) Detailed What-If Analysis: Data needs to be manipulated for what-if analysis and for the same it might require a write-back to your underlying data. This is not possible when you have a lot of users who would be accessing the same data warehouse. This can be very well catered by creating a data mart for this requirement.

4) Isolating data discovery related initiatives: Lots of research and development related activities needs to be carried out on a OLAP system for intelligent data discovery like predictive analysis, adjusting the data model to use with advanced analytical visualizations, data mining, forecasting and budgeting activities by applying external data to your data in DW. Such RnD are safe to carry out on an isolated environment, and data mart can be one perfect solution for this requirement.

Creating a Data Mart is not free of efforts. It requires additional ETLs, additional space, additional maintenance overheads. But considering the business value it brings to the table, it is worth creating data marts in certain scenarios. Above list is not an isolated list of scenarios, but in my experience, these has been the prominent ones. Feel free to share your experience with me on the same lines.

Fast Track Data Warehouse 3.0 - Implementation, Tools, and Planning

Fast Track Data warehouse (FTDW) 3.0 got announced a few days back. After reading the official announcement, some of the points which made me happy were:

1) Now partners are encouraged and enabled to create their own flavor of reference architectures. When you let businesses come out with their own creativity / business differentiators, you build an ecosystem of partner businesses attached to your product. This effectively gives your product more strength to survive in a competitive environment.

2) ISVs like WhereScape has already come up with a solution supporting Fast Track Data Warehouse. I interpret this announcement as WhereScape has developed tools and/or software to develop and/or maintain FTDW.

3) The business division to which I belong - Avanade, along with other system integrators are offering services for FTDW.

4) FTDW home page shares useful tools for planning like Fast Track 3.0 System Sizing Tool, Fast Track 3.0 Schema Wizard and more.

When you deep dive in the technical details, many of these documents would look like a typical matrix screen saver to your brain. But the reason for the same is that, without having experience of being involved in a real-time implementation or with almost no background of storage systems, it's hard to plan the configuration of FTDW.

At least what you can do is study reference architectures, and keep the tools in your repository for any future use. FTDW is not a very common implementation, and it's perfectly normal to get confused with it at the first sight. Probably what industry demands is a book on FTDW !

SQLBI Methodology - Review

Marco Russo recently provided me an opportunity to provide my feedback on SQLBI Methodology, which is an architecture designed by Marco Russo and Alberto Ferrai to the best of my knowledge. This well documented architecture can be read from here.

Below are my views / feedback after analysing the architecture document. To better understand my views, please read the architecture document prior to reading the below points.

Size of BI solution Vs Complexity: In my views/experiecne, the volume of data that needs to be processed ( right from OLTP till it gets stored in MOLAP ) combined with the size of the BI solution sponsor is directly proportional to the adoption of BI solution develppment / adoption methodology.
In simple words, if SMBs can manage their BI solution development using a SaaS methodology compared to developing DW after buying software licenses, most would approach the former methodology. Those businesses who adopt the full refresh of DW every time, would not care much for a methodical approach as change management and incremental loads are not their concerns at all due to the short-lived historical state of DW. Organizations having a large DWs, often in units of TBs would definitely care for a methodical approach as the magnitude of impact of any change is quite huge.

Components of a BI Solution:

1) Source OLTP database - OLTP has known issues, which majorly affect delta detection and accessiblity of DB to DW development team. I completely agree on this part. Here a concept called "Mirror OLTP" is introduced.

If Mirror OLTP is used as a facade, it would not be of much sense as if you can create database objects in other database and then fire a cross-db query, then logically speaking the same facade should be allowed to be created in source OLTP with the isolation of a logical boundary like schemas.

If Mirror OLTP is considered as a snapshot, which is almost a clone of the original DB, one can exercise full control over the source DB, but this is not as easy to implement as it sounds. Consider that a source DB that lies on a SAN and is horizontally partitioned across geographies and you are trying to replicate the same. For this Mirror OLTP you would require to constanly maintain another DB, which demands an increases TCO of the solution. It would require a pass from information security policies and guidelines as audits like BS7799 / SOX etc would require strict compliance.

Instead of developing a Mirror OLTP, one option can be, creating a script of views / SPs / any database objects, and create them just-in-time (which would be also easy for approval from DBAs as they would be more happy for this temporary gateway opening than a permanent cross-db gateway), use them for delta detection and then purge out the same. These scripts can be deployed using VSTS 2010 / VSTS DB edition / any other change management tools you would have at your disposal.

In worst cases, where this option can't be worked, we can opt for what we call as Permanent Staging area, completely suide with data and metadata aligned towards facilitating ETL for DW loads. To me, Mirror OLTP seems to be a compound of the same.

2) Configuration DB - This seems like a facade opened up for users to configure the Mirror OLTP / Staging area / DW / Data Marts, with some built-in configuration settings / logic for each layer.

3) Staging area - This is identified as a temporary staging area. Here it's mentioned as one cannot store persistent data in staging, and I opt to differ from this theory. For managing master data from different source systems, which would not contain delta everytime but would still be required for ETL processing due to ER model design, a permanent staging area can exist. Temporary staging area is also required, and this section is completely alright with me.

4) Data warehouse and Data Marts - This details mentioned in this section seems almost Inmon methodology, where you develop a DB containing your consolidated data from ETL and then you build marts which can be thought of as a limited compound of DW DB. This can be thought as synonymous to what perspectives are to cubes. Data Marts are basically crafted here to compartmentalize different functional areas in DW. Here you would be required to create what I term as "Master Data Mart" and other data marts would be based on functional areas. Maintaining and populating these data marts can be quite challenging.

5) OLAP Cubes - You would create cubes containing data from one functional data mart + master data mart.

6) SSRS Reports - To me this deparment seems to be struggling, due to the design of data marts. Reporting requirements can be extremely volatile which can be complex enough to induce a change, which would require manipulating ETL -> DW -> Data Mart. Also there can be cases where you might introduce an another small ETL layer between DW -> Data Mart. Operational reporting would be done against Data Marts and not DW, as this architecture is an adoption of Inmon's view to a greater extent.

7) Client Tools - This section is okay with me.

8) Operational Data Store - This section clearly identifies that ODS should be used with care.

Summary: In my views, this architecture can be perceived to act like a Prism. You have a ray of light, and after passing through the prism, it splits out in different colors. And you can catch the color you need.

One big issue that I see with this architecture is Lineage Analysis. In this architecture lineage analysis becomes very very complex, as deriving lineage of data from a dashboard till OLTP is highly challenging. In addition to the configuration DB layer, there should be one more vertical layer where lineage of the data is tracked.

Considering a practical example, say you have a corporation that consists subsidiary companies, for ex CitiGroup has child companies like CitiCorp, CitiBank, CitiSecurities, CitiMortgage etc. When data is intended for CitiGroup level to CitiBank level, this architecture can hold good, as each child companies is a different business unit & model with it's own level of complexity. And each child company's analysis would have a dependency on data from other child companies to a certain level. This architecture seems effective at this stage.

If I were to implement this only at the CitiBank level, I would clearly go for Kimball methodology. But this is my understanding, analysis and choice. There are a lot of scenarios in the sea of business models and requirements, and I am sure this architecture with certain modifications (which is my personal preference), can help in a very effective manner.

Dashboards to monitor data warehouse development and execution

If you have watched any military combat kind of movies movies, you would have noticed that when the military / armed forces reaches the target site, and deploys a radar to monitor the operations and establish communication within the teams as well as with the headquarters. You must be thinking that am I going to share some movie story? The answer is "No", I am just trying to make my point.

In the way described above, whenever the DW incremental load execution life-cycle begins, I see ETL as the primary driver of the process whether DW is in development / production phase.

1) Using SSIS you can drive the entire process right from extracting delta from OLTP till processing of cubes from within SSIS packages. If you follow a methodical and process oriented ETL approach, you should essentially log each task in some table which you might consider as a "Process Control Table".

2) During the ETL execution phase, you would have audit and log tables that would be populated for each cycles. Over the period of time this table would / can grow huge and might need archival. Many ETL designs do involve a staging area which would also contain data depending on whether permanent / temporary staging is considered in the design.

Different stakeholders like power-users/end-clients, project management, team members, client's IT support staff, development team, testing team and others would like to get access/view of the data created as mentioned in points 1 and 2 for their own needs. This creates a dependency on the development team to constantly facilitate them for the same. And here is where Dashboards can be of great help.

If you create a few reports on the top of this data, respective teams can just access these reports and view data on their own without any dependency on the team that owns these data. Also these helps different teams to have a common communication medium to share updates and status of the current activity on the development / production systems. For ex, if Project Management is constantly bugging the development team to send out email updates of the incremental load during a production release, a dashboard fuelled from PCT can make them self-sufficient to check the updates. If testing team constantly bugs you to provide data from the staging area for their test cases, a dashboard / report can make them self sufficient to mine out any data. If IT support staff needs to check for error logs, reports can make them self sufficient.

The only question after this is where and how to create this dashboards. SSRS Reports can easily cater this requirement. SSMS can host SSRS reports, but the ideal place would be deployment over sharepoint so people can collaborate on a single medium. You can even facilitate this reporting using Excel 2010 and use Excel Webapps for collaboration.

Coming back to the idea of military installation, DW development is like a military exercise and the first thing is to set your radars and establish communication within teams (development, testing, QA) and with your head quarters (end-clients, PMO, BAs), so that everyone is aware of the progress especially during the development phase of the project. I hope you like this theme of radar installations by military superimposed on DW development :)

Data warehouse development life-cycle

I have been attending a number of meeting these days, and I am encountering a common question everywhere - What are the layers that you plan to incorporate in the architecture design of a data warehouse development life-cycle? I found this question rather interesting and thought of sharing my views on the same.

Speaking from a high level, from a DW development perspective, firstly one needs to figure out the boundaries of development. Generally the extreme boundary starts from OLTP and ends at Dashboards. After having these boundaries considered, the following are the layers / development arenas one can consider for a DW development from scratch.

1) Delta Detection - This is the first exercise that you would plan with your OLTP system. This is a very important exercise, as this would decide a few other exercises in the life-cycle.

2) Staging Area - Based on the requirements and considering the complexity of delta detection, a temporary / permanent staging area development would be required.

3) Master Data Management - Do not confuse it with the standard MDM practice, which is more towards modeling. Here MDM means how you would manage your MDM in the staging area / in the delta, as delta applies only to transactional data. Master data do not change that often, and you need master data for your ETL processing. This has to be managed at the facade layer you would build for delta detection / in the staging area, but it has to be planned along with the points 1 and 2.

4) Dimensional Modeling - This is the exercise where you start modeling your dimensions using the Kimball / Inmon methodology.

5) Data Mart Design & Development - Only after the point 4, one can start developing a data mart which lays down the base for the next exercise of ETL development.

6) ETL Design & Development - Points 1 - 2 - 3 are to serve the ETL processing. ETL basically serves as the processing engine to transform your relational data to suit the model of your data mart. One can consider the above points in E and this is T + L. Until you Data Mart is in place, one does not have any idea about the target schema, so ETL development makes sense only at this level.

7) Cube Design & Development - This exercise can be done in parallel with point 6. Once you have your data mart, you can start with this exercise. In fact, you can start your exercise even before your data mart is in place, but if your data mart is not ready means your dimensional modeling is still too volatile / evolving. So better start this exercise after you have some concrete model ready for your data mart.

8) Operational and Analytical Report Design - Cube is generally refreshed at regular intervals and only exception are real-time cubes. For those refresh windows where cube does not have the data from OLTP that is loaded after the latest cube refresh cycle, operational reporting needs to be provided. And analytical reports would serve as the constituent for scorecards / webparts that would be used in Dashboard development.

9) Dashboard Development - After the above phases are ready, you have the engine ready and it's time to give a face to your machine. Generally dashboards are fuelled from cubes to a major extent, and this is generally the final phase of a DW development life-cycle.

I have tried to describe the various development cycles that form a DW development life-cycle from a very high level. Feel free to add to it.

Tool to create / support BUS architecture based data warehouse design

Whatsoever powerful SSAS may be, when it comes to starting a fresh new dimensional modeling exercise, using SSAS is the last step in the process i.e. data warehouse implementation. Dimensional modeling starts with the understanding of how the clients want to analyse their business, which implicitly involves identifying the ER of the targeted business models. Right from there, one needs to develop a BUS matrix (provided you are following kimball methodology and BUS architecture) followed by a Data Map / Data Dictionary.

Once you have the blue-print ready, artifacts required to build the anatomy of the data warehouse needs to be built, and two of the major ones are:
1) ETL routines to shape your data compliant to Data Mart design.
2) Relational Data warehouse / Data Mart i.e. dimension and fact tables and other database objects that would hold your data transformed by ETL.

The process sounds quite crystal clear, but when you are developing from scratch, and when your data warehouse and dimensional modeling is in the phase of evolution, there is one tool which can be very instrumental in designing the same. The wonderful part is that this tool / template comes for free from the courtesy of kimball group, and it's called Dimensional Modeling Spreadsheet.

Dimensional Modeling Spreadsheet: This template spreadsheet can help you to create your entire data dictionary / data map for your dimensional model, and it contains samples for some of the basic dimensions used in almost any dimensional model. The unique thing about this spreadsheet is that once you have keyed in your design, it has the option to create SQL out of your model. You can use this SQL Script in your database and create the dimension and fact tables right out of it, which means that your data mart / relational data warehouse is ready to store the data. Also this spreadsheet can form the base for your ETL routines. The only other tool in my knowledge which can serve near to this functionality is Wherescape RED, and of course it's not free, as it serves a lot more than just this.

You can read more about this spreadsheet in the book "The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset". For those who are fresh to dimensional modeling concepts, read this article to gain a basic idea of the life-cycle.

Products for Data Warehouse management on cloud and in premises : CloudOlap and WhereScape RED

There are two ways to create Data Warehouse, one is using the traditional development life-cycle and other is by using accelerators (i.e. products) that build on the top of data warehouse related technologies provided by vendors such as Microsoft, IBM, Oracle, Teradata and others. In the traditional development methodology, we start with profiling metadata & cataloguing data quality in OLTP, develop relational DW and Cube design, and corresponding ETL tasks in parallel. This process is the suitable way when you have sufficient time, resources and funds to roll out your enterprise wide DW in a phased approach, mostly when your DW is integrated into your proprietary application.

But when you need to ramp up your DW in a short period of time, and you have a limited IT Staff with the right skills, within your organization for developing your DW, one can consider investment into products like WhereScape RED. Some of the noteworthy features of this product are:

1) It provides a GUI for developing DW in a very agile manner

2) It builds ETL processes for fueling the data into DW that you build with this tool

3) It has support for a majority of standard DB / BI vendors

4) It creates automated documentation as a part of the development process

5) License of this product can be quite costly, and the way it can be compensated is by saving on the duration of time for which technical staff needs to be working on the project.

A better idea can be gained from this client story ( Coinstar ) of WhereScape, where they used it for developing their corporate data warehouse.

This product works on the top on DB and BI technologies that work in-premise. Had it been on the cloud, it would be one of the most impressive accelerators for MS BI DW development. WhereScape has been around in this market since quite a few number of years, but seems like cloud on not on their agenda till now.

One another product that provides similar kind of functionality in-premise or on the cloud ( Amazon EC2 ) is CloudOlap. Though I have not evaluated this product much deeply, this video gives a very nice overview of the functionality of this product. CloudOlap provides various power products for Microsoft and SaaS Data Warehousing, in-premises as well as on the EC2 cloud environment. If this product is what it claims to be, I would rate this product much higher compared to WhereScape RED, and the reason it that it is one of the rare products that leverages MS BI to a SaaS platform on the EC2 cloud, and it provides functionality similar to WhereScape in-premises. May be WhereScape should start thinking about it !!

Data Warehouse as a Service (DaaS) in MS BI Stack

Fast track data warehouse architecture and parallel data warehouse are two entirely different kinds of solutions but targeting a common domain of data warehousing. As SaaS solution is not available in MS BI Stack, similarly DaaS solution is also not available in MS BI Stack. If these kind of solutions are made available on private cloud solutions like Windows Azure Appliance Solution, a DaaS kind of offering can be made available on Microsoft platform. I am not sure if this would be of interest to Microsoft, but in my views, this is definitely of interest to solution provider kind of organizations. I feel that combining Azure and MS BI Stack, Microsoft has all the raw material that is required to develop SaaS or DaaS solutions, but the integration part is missing.

One of the leaders in DaaS solution is Kognitio. Enterprises are looking for on-demand services, and Azure can be seen as the first step in this direction. And whether it's for self-service business intelligence or data warehousing, there is a huge market for each of these solutions and microsoft is eagerly awaited to make entry into these (SaaS and/or DaaS) markets. Integrating MS BI on cloud would not be simple, and again making the same available in the form of service would change the existing MS BI markets. Not only that, solution providers would also have to adapt to this change, and look at a completely Service Oriented Architecture (SOA) solution proposition.

Below is an excerpt from Kognitio DaaS page, to which I fully agree.

• DaaS solves several issues that are common within the data analytics industry. Most notably, the inability to prove business value from data warehousing and analytics projects before spending large capital sums of money.


• DaaS allows you to run your large-scale data analytics projects (marketing campaigns, business reporting etc) and you simply pay on a pay-per-use basis. There is no overhead of implementing a data warehouse onsite, there is no need for your IT department to service, maintain and support the database and its users.

More about Kognitio and the services offered by this company can be read from here.

SQL Server 2008 R2 Parallel Data Warehouse ( formerly known as project Madison )

SQL Server 2008 R2 would be released in different flavor never heard before, and it is called SQL Server 2008 R2 Parallel Data Warehouse which was earlier known as Project Madison. This flavor of SQL Server 2008 is unique and first one of its kind. Some of the points that make it unique are as follows:

1) This version of SQL Server can't be bought as an independent piece of software, it has to be bought along with the hardware.

2) Hardware would generally consist of one controller node, and rest of the compute nodes (3 minimum). This controller node would manage requests and route it to compute nodes. Also the licensing for installation of SQL Server on each node is on a per CPU basis, which means that minimum 4 licenses would have to be procured.

3) To the best of my knowledge, I read somewhere that SQL Server 2008 Parallel Data warehouse edition would cost above $57,000, so consider the price of the same for minimum 3 compute node CPUs. Also the rest of the software and integration and controlling would cost extra. Add to this the installation and consulting fees that one needs to house for maintaining this setup.

Clustering brings concurrency to the system and reduces load, but it can't reduce the time that a single query would take without any resource latency. To break this barrier, parallelism would be required to execute bits of the same request simultaneously and this is what exactly this setup would bring to the table. SQL Server can also run queries in parallel, but in a data warehouse it would be interesting to see how parallelism is being brought, and claims are that queries that takes hours would come down to as low as minutes. Massive Parallel Processing is claimed to be obtained thru this implementation, and the architecture would be hub-and-spoke.

By partnering with vendors like HP and IBM who are some of the leading runners in providing hardware required for different kinds of data warehouse setups, Microsoft has created a unique kind of sales package leveraging business for themselves and it's partners. This idea can be used even by solution providers organizations by partnering with local or international hardware vendors, and coming out with a sales package that incorporates end-to-end data warehouse development in addition to specialized hardware setup as a single package. Fast Track Data warehouse is a nice start up architecture where solution providers can make a start for a similar package offering.

More information on this setup can be read from this data sheet.