Book on SQL Azure - Reviewed by Siddharth Mehta

Packt Publishing generously provided me an opportunity in the past year to be part of a book on SQL Azure titled "Microsoft SQL Azure Enterprise Application Development", in the role of Technical Reviewer. I am very passionate about cloud based business intelligence, and MS BI on the cloud is one of my favourite topics. This book became available at stores in Dec 2010. As I have been a part of this project, I can confirm one thing, this book is worth every penny. I am not trying to sell the book, but as I have been a part of this project in a deep technical role, I had the opportunity to have a thorough analysis of the subject when the book was in drafts. If you are looking out for a book on SQL Azure plus a decent starting lessons on Windows Azure, this book is definitely for you. Book doesn't expect any level of maturity on cloud and you can start from zero and reach at least intermediate level at the end of this book. In case if you have/had procured the book and had a chance to go through the book, please feel free to share your view with me.

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.

Siddharth Mehta - Microsoft SQL Server MVP 2011 !!

I started my blog way back in 2006, just to book a domain name on blogspot. I could not find the domain on my name "Siddharth Mehta", as it was already booked, so I used my nick name "siddhu" and choose the domain name "siddhumehta". I was not mature enough or serious at that point in time about blogging and technology. In 2009 I thought of starting my blogging again. I never felt like a super intelligent personality who is going to start blogging to enlighten the world with his knowledge, and help others. My motto was help others to help yourself, i.e. I wanted to put my knowledge to test on how I could solve problems that professionals face in real time, as sitting on my office computer, I cannot face all the kind of issues that professionals face across the globe. I was having the idea that by blogging, I would learn a lot as well as create a name in the field of MS BI.

When I started blogging, the only audience I had was a couple of my friends, and the was the result of blog marketing that I used to do in my professional network. I was never interested in re-blogging content that MSDN already provides. My vision was to share what I face in my day to day work, my viewpoints, my findings, my learning and issues that professionals generally struggle. I thought I would share about topics that I struggled to deal with, topics where I derived solution after a lot of struggle due to lack of reference material / guidance from technical experts / lack of hardware-software resources / other reasons. I come from a very modest background where having a personal laptop is considered a luxury, and I thought this blog would help those who share the same story.

I found my bandwidth is utilized too less on my blog, and there are other popular mediums where I can contribute. After my blog started getting little more recognition, I started authoring for different online publications. It was not easy, and I remember that publications were not ready to consider my work as I had no proven track record of authoring. I didn't lose heart and I kept on blogging about genuine technical topics, and my experience as well as insight in the field grew even deeper. Finally my authored work started getting acceptance. I started taking my baby steps in the field of authoring, and by this time I had already completed more than a year in blogging.

Gradually the popularity of my blog grew more day by day, more and more professionals started interacting with me through email. My articles started getting recognition on different popular websites and portals. I got an opportunity to author a whitepaper for MSDN Library. I got opportunity to review a book on SQL Azure title "Microsoft SQL Azure Enterprise Application Development". People used to ask me why I don't put GoogleAds on my blog, and my answer was/is that I consider my blog as a showcase of my journey, technical abilities, my technical face to the outer world.

In my definition, "Expert" is one who has not only thorough knowledge about the subject, but has applied the same to address a huge number of problems. It is about applying your knowledge to derive solutions to address known / unknown problems with a vision that gets developed only through experience. Today Microsoft has certified me as a MVP in the field of SQL Server, and I feel speechless. Two years of genuine blogging, affiliation with technical community / professionals / blog readers, authoring, technical reviews, and thought leadership.

It has been a very huge journey, and I would like to thank Microsoft for this grand recognition and motivation as a SQL Server MVP. This is an encouragement that I cannot explain in words. I would like to thank everyone who had helped me to reach the level that I have attained today. This post would be incomplete without expressing my special gratitude to certain people and organizations, and they are: MSSQLTips.com, MSSharepointTips.com, SQLServerCentral.com, SQL-Server-Performance.com, Microsoft documentation team - Doug & Carla. All of you have helped me directly / indirectly and I thank you for the same.