Using Microsoft Office Project Server with MS BI ( SSIS, SSAS, and SSRS )

Microsoft Office Project Server (MSPS) is one of the healthiest source of data in the microsoft ecosystem. Many departments especially CIOs have the greatest potential and probability to make extensive use of the data contained in Project Server. Almost every organizations have different projects for which they carry out planning, tracking, monitoring, resource assignments and related activities. MS Project Server is a chef's knife for this purpose.

From a technical standpoint, the way MSPS stores data is very interesting. Like Sharepoint, it stores data internally into SQL Server. But unlike Sharepoint, it gives a very neat and clean mechanism to use to data it stores internally in the form of a database intended for reporting known as Reporting database and is operated using a service known as Report Data Service. Also it has a service called Cube Build service (CBS), which can be operated using a web based console known as Project Web App (PWA).

The Reporting database (RDB) is the staging area for generating reports and OLAP cubes. Data in the Reporting database is comprehensive and is updated nearly in real time. The tables and views are optimized for read-only report generation; for example, the RDB tables are denormalized to provide redundant data and reduce the number of relational tables. As data is updated in real time in RDB, in case if you are considering extracting data from it to some other data store, consider reading how data gets to the RDB and Report Data Service. Schema documentation of the reporting database as well as the OLAP cubes is available and  can be downloaded from Project 2010 Reference: Software Development Kit, in the documentation\schemas subdirectory.

Microsot Office Project Server 2010 Architecture Diagram can be seen below:



As apparent in the above diagram, MS Project Server is very well integrated with Sharepoint 2010. Hence using reporting related tools like Excel Services, Performancepoint Services and BI + Dashboarding capabilities in-built into Sharepoint, a rich reporting platform can be provided to end users from data contained into Project Server 2010.

From an MS BI perspective,

• SSIS can be used to extract data from reporting database and merge this data into a corporate warehouse
• SSAS can be used to source and enhance cubes and OLAP database exposed by project server
• SSRS can be used to generate reports on the top of OLTP reporting database and cubes contained in OLAP database exposed by Project Server.

I seriously wish that perhaps Sharepoint can expose such databases for reporting and analysis, as that makes it very easy to facilitate reporting and analysis of the content stored in sharepoint.

To understand more about Project Server, you should consider reading about Project Server Architecture and Project Server Programmability. Also consider reading more about how to configure reporting for Project Server 2010.

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.

Data Integration Services on SQL Azure platform

They say that any knowledge never goes waste, but in IT parlance this saying can be redefined as any knowledge or DATA never goes waste. In my career till date, my experience has been that any successful business would have lot of external data processing as a part of its business functions. Competitive Sales Intelligence for example is one of the category of data that many IT organizations would keep of processing to defines best analytical insights for their sales team. Companies like Facebook and Google accumulate hoards of data and makes a fortune out of advertising business. But even these giants depend on external data providers for their business functions. Example of one such data provider is Factual, that provides data to Facebook. Monetizing on carefully curated and certified business databases has become a very big business.

Cloud takes this platform of sharing and trading data one step ahead. Windows Azure Marketplace provides DataMarket for the same purpose where applications can share and trade data.

1) Private cloud and public cloud comes into question, as organizations might want to share their datasets but limited to the scope of the organization only. Microsoft codename "Data Hub" claims to provide a flavor of managed self-service enterprise data integration on the cloud, which generally takes a huge team and data centers to serve the same needs of an organization. This platform is expected to provide private data-market to enterprises which can be very interesting in terms of agility and cost-savings.

2) Any sizeable organization would generate and consume lot of internal as well as external data. Integration and sharing of data is implementation of the solution after the source of data has been recognized. Data discovery from within and outside organization for business needs, is a bigger challenge in itself. Microsoft Codename "Data Explorer" can be seen as self-service SSIS on Azure platform. It provides data discovery from the windows azure marketplace as well as provides features for self-service data mashups from a variety of standard data sources. Hadoop is not yet included in the supported data source list, but if its gets included in the future, this platform can reap immense value and can acts organizations private Google blended with SSIS to create self-service data mashups and again publish the same as a source of data using Data Hub.

Power of Hadoop combined with cloud based tools like Data Hub and Data Explorer can generate business for lot of data providers as well as bring immense value to organizations. Also it would enable better use of data and provide cost-savings in enterprise data integration.

 

SSIS on Cloud with SQL / Windows Azure : Future Applications

Generally cloud and ETL have a typically known application of facilitating ETL, which mostly harnesses the elasticity of computing resources. ETL on cloud is ideal for applications where data is already stored on the cloud. Regular line of business applications would process data from OLTP sources and load the data on another data repository on the cloud. For ETL to source and load data from in-premise data sources, WCF and related RIA services are employed. Using Amazon EBS cloud and customized VM images, you can setup your ETL on cloud and Amazon EBS out-of-box supports SSIS standard version. Right when Azure was in it's CTP, I had authored two articles on SQL Azure, on how to read and write data to SQL Azure using SSIS and SSRS 2008 R2. But this is something that is already well-known. What's new ?

Semantic Web, Unstructured Data and technologies that store, process, analyze, warehouse such data and extract intelligence out of the same is the new challenge that is at the horizon of the IT industry. Few front line IT majors have tsunami sized data generated everyday due to the virtue of popularity of social media, and such organizations like Google, Yahoo, Facebook, etc have already started wrestling with these challenges. The benefits of processing unstructured data, and driving your business based on the extracted intelligence are very clear from the example of these companies where they within a duration less than a decade, their advertising revenues are worth billions and still skyrocketing. All standard business house have lots of unstructured data like emails, discussion forums, corporate blogs, recorded chat conversations with clients etc. Organizations generally have aspirations to build a knowledge base for all the different areas of business functions, but when they start seeking consulting on the strategy to implement the same, they find themselves in a whirlpool of processes and financial burdens. Unstructured data has the potential to generate data for such knowledge base.

Still the question in your mind would be, what has this to do with SSIS and Azure? Regular applications of SSIS are known to everyone, and more than that professionals would be knowing that SSIS is not supported on Azure cloud platform, which might be the motivation of reading this post as the subject line reflects that is might be supported now. SSIS is an in-memory processing architecture, and implementing the same of shared / dedicated cloud environments has its own challenges. I am also sure that SSIS Team must be on its way to bring it to the crowds in the time to come. But my interest in on the future application of SSIS on unstructured data, and hosting it on Azure cloud platform.

When SSIS would gain such capability, applications like Extractiv would be common across enterprises. To build application like Extractiv using SSIS, solution design would be too crippled as SSIS is not inherently blended with cloud. One day I would like to see SSIS packages getting executed as a crawler service in Sharepoint, which would crawl entire site and data on Sharepoint portals like FAST Server, extract entities from unstructured data and populate next generation of warehouse on the Azure cloud platform, that would be queried using LINQ for HPC kind of technologies. For me, applications like Extractiv are very fascinating, as they inspire to foresee ideas, that are window to opportunities which most are not able to envision right now.

DQS Cleansing Transform in SSIS - Data profiling and cleansing using SSIS and DQS

Data Quality Services has come up with some interesting concepts.

1) Two main pillars of DQS concepts are - Knowledge Base and Domains.

2) In laymen terms I would consider KB as tables and domains as fields of those tables. Domains have datatypes, constraints, and other attributes just like fields of a table. Domains can also be hooked with reference data providers, to get enumeration of valid and possible values.

3) Composite domains are combination of one or more domains and makes up a complex combination, which is synonymous to calculated fields.

4) When data is passed against these domains, it acts like a regular expression match on the data that is matched against the domain.

5) After the data match against the domain, it applies fuzzy logic kind of algorithm similar to what SSIS Fuzzy Lookup transform. Based on this feature, concepts like "confidence" against the matched value and options to correct the data is offered.

A tool packed with all these features is very useful for data stewards i.e. data quality analysts and operators, and that tool is Data Quality Client. From a higher level if you analyze these points, they all collectively fit in a way to function as a transform. DQS Cleansing Transform in a new transform introduced in SSIS Denali. A very informative article from SSIS Team demonstrating the use of this transform can be read from here. Another great informative article on DQS Cleansing Transform is available from DQS Team, which can be read from here.

Creation of knowledge base and domains is much like creating tables and fields, and if DQS provides an interface to import tables from source systems in the form of KB and domains just like Data Source View, it would make the process very easy and make the tool more friendlier. Honestly, I feel that organizations would have their knowledge embedded in the metadata of database objects. And creating that kind of KB and domains again is not a light weighted exercise. Let's see if such facilitation gets added to DQS in the time to come.

The bright side of DQS is that cleansing becomes more decoupled, powerful, and data profile task and data profile viewer would complement the capabilities of DQS as data profiling and data cleansing are closely associated processes. I would not be surprised if Data Profile Viewer and Data Profile Task gets dissolved into DQS and a new transform called DQS Profiling Transform gets added to SSIS toolbox in Data Flow along with DQS Cleansing Transform !!

MS BI Architecture Design Layers - classifying layer specific logic

Any architecture design diagram is composed of several layers vertically and horizontally. Horizontal layers are discrete logical areas and their association in the diagram describes the way they are connected to each other. Vertical layers run across the entire solution and all these logical areas, which means they are applicable throughout the solution. For example, data repository can be classified as a horizontal layer and auditing can be classified as a vertical layer. This is a very well known fact and most of us would be knowing this very well.

When it comes to implementation, the association of these layers are honored and the solution is developed keeping this layers in view. But this is only in terms of how these layers are associated with each other, i.e. data and process would flow vertically and horizontally as defined in the architecture diagram. One very vital point that many miss is where to deploy your logic. Below are few logic deployment challenges or confusions that most of us would have faced as decision makers at some point of time in our careers:

1) Should logic be stored in .NET App or in DB Stored Procs

2) Should logic be stored in Stored Procs in OLTP DB or in ETL package

3) Should logic be stored in scheduled batch jobs or in ETL driver package

4) Should logic be stored in Stored Proc or SSRS Report

5) Should logic be stored in SSAS MDX Script or Client App

6) Should logic be stored in Dashboard or SSAS Cube

I have seen many genius taking their comfort route to make their jobs easy and jeopardize the future of the solution, just by deploying the logic that belongs to one layer of the architecture into another layer.

Once I had come across a scenario where one genius project manager tried to defend a solution with the argument that as the application was designed as a reporting application, entire logic is stored at the report level. This means SSRS RDLs contained the entire query logic and formatting logic within it. The solution in discussion was developed as a reporting application, and after few months down the line the requirement came up to act as a data source for other systems. As the logic was completely embedded in reports, it was not reusable at all and the solution design fell flat. Looking at embedded SQL in RDLs, any logical developer would ask, what an uncompiled SQL is doing in SSRS report ? SQL belongs to DB inside a SP and formatting the UI of the report is the report specific logic that can be contained in reports.

I have also been evident of scenarios where an application architect is in the driver seat, and the approach pursued it to embed entire logic into .NET code and treat DB as a blackbox to pump-in and pump-out data. In any corporate IT systems history you would find that application layer i.e. the User Interface / Web Front End layers are changed like changing the curtains of your windows, but corporate DB are hardly changed and whenever DBs are migrated they are a result of a large scale corporate IT systems revamping exercise.

I do not intend to hint that all logic should reside in the DB. Whatever logic that belongs to a particular architecture design layer, it should be deployed in that layer only, which is one of the implicit communication of layering in an architecture design diagram. Entire functionality can be achieved by deploying code in a single layer of the solution, but in the long term it would defeat the very purpose of layering and design patterns. Dissecting the right part of the logic in the right layer, followed by best practices of developing the layer would provide the most ideal solution from a stabilized solution design perspective.

In the field of technical architecture design, my career experience has been that each piece of logic should remain with it's deserving layer. Feel free to prove me wrong !!

Planning integration of source systems in your data warehousing solution

Any ETL designer would think of integration with source systems, with ideas like, ETL would connect using X driver, X database, stage to X server, load to X destination etc. But an architect cannot take anything as provided and would have to validate even the basic questions. Some of this basic questions are listed below, and when you start figuring out answers to these questions, you would feel a need to start planning the integration from an infrastructure and integration perspective, even before ETL designers can think of the feasibility of an ETL solution.

1) Where are the source systems hosted, on a physical cluster or a VMWare based server in a virtualization environment ? Are load balancers placed on these servers ? You would require to collect Virtual IPs to connect to those servers and keep in mind how your ETL solution would read data from these servers.

2) What is the classification of data in source systems and is this in sync with the zone in which your ETL server is hosted ? Generally in any data center based hosting or enterprise class IT environments, source systems / data is classified as RESTRICTED / UNRESTRICTED / CONFIDENTIAL etc, and different data traversing policies revolve around this classification. Data can generally be exchanged only within systems in the same zone of data classification.

3) Is your connectivity uni-directional or bi-directional ? Many systems just allow uni-directional connectivity from other applications to the database server. So database server might be able to connect to other system, but other systems would not be able to connect back to the DB server i.e. connectivity can be initiated only from DB server and network policy itself would impose this restriction.

4) What is the type of connectivity allowed with the source system database server - Windows integrated or SQL based ? Mostly enterprise class IT environments allow only windows integrated connectivity, as policies can be enforced on windows id.

5) Where is your scheduling server located ? Generally application specific scheduling agents are not allowed in any enterprise class IT environment. You need to locate your scheduling batch server, and check whether you would be able to trigger execution from that server, or you would need an agent installed on your DB server and trigger execution from DB server. If your ETL execution is triggered from a scheduling batch server, connectivity needs to be available to source and destination servers from the batch server. And if execution is going to be triggered from your DB server, connectivity to source system should be available from this server.

Unless and until your solution environment has no governing policies, and you have complete discretion and governance over source and destination systems, and your solution is just hosted on application specific servers and not in any data center based hosting environments, you need to plan your integration at a much deeper level. I hope the above points would provide a few hints in this direction.

SSIS Balanced Data Distributor Transform

Microsoft download center has a new download available that would be of interest to many SSIS professionals. Its a new transform named Balanced Data Distributor transform, which takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. Below mentioned is the description of this transform as mentioned on the download page:

"Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. "

Considering the test performed on this transform, from the results it seems that performance of this transform is a little better over other transforms like Script and Conditional Split. But it should also be taken into consideration that this transform buffer by buffer to its output ends, and the other two checks the data based on the specified logic and then divides the data. Its a nice transform to have in your existing SSIS toolbelt.

The best use of this transform that I can think of is when your input is extremely fast, and you have replicated blocks of logic to keep up the pace with the incoming data flow. In such a case BDD acts as a distributor bridge pumping data to all logic pipelines. But if data is to be dissected conditionally, Conditional Split would be the option again. The speed of distribution harnessed by this transform seems to be due to two reasons: 1) No distribution of data conditionally, just pass buffer by buffer 2) Multithreading architecture.

Certain curious questions the my mind raises are:

1) Denali is already in CTP mode and more CTPs are expected to come. Then why this transform has been released separately at this time and so silently?

2) Will this transform be a regular transform available with Denali, or would it remain a mysterious separated out transform?

3) This transform is available only for SSIS 2008 and SSIS 2008 R2, not SSIS 2005. Why ? Actually it's SSIS 2005 that needs more help with transforms like this, where it would be a value addition to customers who have already made investments in SSIS 2005 !

Let's look forward to the next CTP of Denali to checkout whether this transform would have a seat in SSIS Denali.

Using a scheduler in the technical design of your BI solution

In Microsoft world when you think of scheduler, SQL Agent is one of the most famous name that would come up on the list. Scheduling can be seen as a very nominal and regular requirement in almost every project, and most professionals would think of addressing it by installing a scheduler on your target server. There is a difference between having a mission critical scheduler service and an enterprise scheduling service. If you see from the eye of an architect, you would never want a mission critical scheduler, as in the long run it becomes a maintenance bottleneck. There are two points to consider before designing solution for your scheduling needs. Firstly you need to figure our an enterprise class scheduler and secondly you need to get right design in place.

Personally, I do not see SQL Agent as an enterprise scheduler. It is very much useful for scheduling database related tasks, but even there I see some very serious shortcomings. For example, there is no file-watcher capability built-in with it, and watching for file / feeds is a very essential and common requirement with application as well as BI solutions. An example of enterprise class scheduler is Autosys, that I have seen in many enterprises class IT environments. Some of the strong features are scripting support, client-server architecture, multi-platform support, file-watcher event-watcher and other intercepting capabilities. ETL / Data warehousing solutions mostly need intercepting capabilities for data feeds, and if you need to depend on your ETL tool for interception rather than your scheduler, this is a shortcoming of your scheduler. In SSIS, developers use third-party File-Watcher and WMI tasks to watch for feeds, but I personally see it as a shortcoming of SQL Agent and not SSIS.

Now comes the question of how you frame your scheduler in the technical design of your solution. Mostly designers are concerned limited to the scope of the solution they are developing. But an architect needs to foresee how the solution would grow and fit in the enterprise. Any successful solution and business would mostly flourish, and so would the IT systems surrounding the same. With this growth whether it is in the form of scaling the solution and server across geographies, or more solutions getting rigged with your solution, your scheduling needs would also grow. So in case you have opted to use mission critical database or application or system schedulers, it would be extremely hard to manage your scheduling. Consider you have your BI solution spread across geographies with multiple servers and you intend to manage different scheduling needs on different servers, and you have opted to use scheduler on each server by installing SQL Agent. So how do you enforce policies, deploy scheduling routines, as well as control and monitor the same ?



The solution you need to have is a scheduler with a client server architecture. Generally the regular practice is to have a centralized scheduling batch server and scheduling agents are installed on each server. Scheduled jobs are always punched in the centralized batch server and agents synchronize with this server to retrieve jobs respective to their servers. In this way you can replicate your BI solution by creating different server clusters at different geographies without any maintenance , and scheduling of each cluster can be managed using the centralized batch server.

Enterprise class scheduler + Scheduling design pattern = High Performance. Delivered. (It's accenture punch line !).

MS BI Infrastructure Architect - Developing readiness for the role and responsibilities

Architect is a very appealing word to all the aspiring technical minds, but most people do not realize that architect is an adjective-free role. Have you ever heard terms like SSIS Architect, SSAS Architect, SSRS Architect, PPS Architect etc.. ? If yes, then I would say that Architect word has been loosely used instead of the term SME.

The first difference between developers and architects in my vision is the broadness of domain. Most developers would stick to a technology instead of a platform, whereas this ideology does not suit the JD for an architect. If you have the ideology that "I have worked with SQL Server for 5 - 8 yrs, I am good at T-SQL programming, SSIS, SSRS and have theoretical idea of DW. And if you ask me what is Sharepoint, .Net, Webservices, Cloud, Infrastructure, Data Modeling, etc.. this is not my domain.", I would stamp "Biased MS BI Developer" on your CV. Architect requires changing many hats like Technical Architect, Data Architect, Solution Architect etc, and being an Infrastructure Architect is one such hat. If you are an Architect, many a times you would find yourself in a role where you are the Infra Architect + Data Architect + Application Architect + Solution Architect, and you might be given few technology specific SMEs for consulting. I have been in such situations as I had experiences with all these individual roles as a tech lead through the course of my career. Whatever I am sharing is based on my experiences.

When a solution encompassing application development technologies and MS BI technologies are promoted from environment to environment i.e. from dev -> staging -> APT -> Prod, this requires infrastructure estimation, capacity planning, software configuration, server connection topology etc before the environments are built. If you think that to setup such environment, one can just procure servers, add memory and rig the systems, probably you must be setting up infrastructure for solutions of very modest size. If you are developing solutions for an enterprise class client, there is a high probability that there would be a Data Center with shared application environments where your solution would be hosted.

Here comes the first lesson as well as challenge for the Infra Architect. Virtualization is the SQL of Infrastructure capacity planning. You would have to deal with infrastructure teams, who would discuss, advise and challenge your estimations and talk about technologies like Hyper-V, VCPUs, RAM, Ports and Protocols. You might be using MS BI Stack, Sharepoint and .NET Stack, Microsoft System Center and each of these would have different connectivity and hardware requirements. At a minimum you should know what MS BI stack needs in terms of infrastructure design. Ideally in a virtualization environment, development servers run on 4 VCPU, 4 - 8 GB RAM and Production servers of modest size run on 8 VCPU and approx 16 GB RAM. If you are not aware of what is a core, vcpu, ports etc you should start developing an understanding of the same.

The next major challenge you would be faced with is memory capacity planning, this mostly depends upon data and load. Application Performance Testing environments would be setup to test performance, and you should learn how to interpret the results from those environments. Testing teams would be using Load Runner kind of tools to perform a load testing, and you would be getting regular reports containing performance counters, concurrent users, memory utilization, CPU utilization etc. This is second area where would act as your profiler.

The final major challenge is allocation right amount of memory for different aspects of the solution. For example, if you have SQL Server and MS BI technologies, you need to allocate memory for logs, backups, installation, data etc. Based on this calculation you need to estimate total memory requirements and also setup designs for hosting application environments on the planned infrastructure.

You would not be practically building servers and installing softwares. In an enterprise class IT environment, there are dedicated teams for the same, but the order to march forward comes from the architect of the solution and not the architect of infrastructure teams. So at the minimum you have to create a technical architecture diagrams from infra setup to communicate your design and estimation. Being in such a role is a challenge, and fortunately or unfortunately I have been in such role and had learned a lot from the same. I hope this post brings some vision to professionals prone to such challenges. If you need to borrow my experience, feel free to drop me an email.

SSRS 2005 to SSRS 2008 R2 Migration Strategy , SSIS 2005 to SSIS 2008 R2 Migration Strategy, SSAS 2005 to SSAS 2008 R2 Migration Strategy

Product edition upgrade and migration of solution artifacts from lower to higher edition is quite a challenge and needs careful planning. The more experience you have on different migrations, the more you would have anticipation of possible problems for migration. However deep may be one's experience, data and platform migration is one such area where one can always expect surprises. Everyone has a first time, and in migration you would want to be sure that you have all the supporting tools and some higher level strategy in mind to design your migration. Below are some guidelines which I had found useful in my career experiences.

1) Firstly collect all the tools, at least freewares that can help you in your migration analysis. SQL Server 2008 R2 ships with SQL Server Upgrade Advisor, which can be the best starting point. This tool is also a part of the SQL Server 2008 R2 Features Pack. You can learn more about the same from here. This tool covers all areas, right from database engine till SSAS.

2) When you start your design, you would have to make a clear distinction between whether you want to perform an in-place upgrade or create a new instance -> deploy solution on the new instance -> ensure synchronization between old and new instance -> abandon old instance. Check out this article for some more info.

3) You should keep in view where you plan to do the upgrade, i.e. on the same box, in the same domain, or across different servers and different domains. This would throw up the challenge of security configuration.

4) Environment configuration needs to be planned for each service separately. For example, SSIS packages can be expected to use configuration settings from different sources like environment variables, configuration files, database and other sources. SSRS configuration might reside in config files for reports server as well as reports manager. Virtualization is the key factor is testing all such scenarios.

5) Finally the biggest risk factor needs to be calculated, i.e. identifying the right sampling to test on the targeted edition. SQL Server 2005 came with it's first mature BI offering. Several components of different services have undergone architectural changes, several features are discontinued, several features have behavioral changes and several features are guaranteed to break when migrating from lower to higher editions.

a) Deprecated Features in SQL Server Reporting Services

b) Discontinued Functionality in SQL Server Reporting Services
c) Breaking Changes in SQL Server Reporting Services
d) Behavior Changes in SQL Server Reporting Services

I prefer creating out a consolidated list of these features. Then all the reports should be analysed to check if any reports have used these features, which would mean that these reports qualify to be considered as a sample to test on the targeted edition. This sampling exercise would not only generate right size of samples to test, but also the same sample would act as the Acceptance Testing Procedure.

Generally production environments are handled by operations team, and they remain in charge of migration too. Development teams need to confirm whether migration was successful and works as expected. The successful functioning of sampling identified from the above exercise would act as the Acceptance Testing routine, which is a contract that needs to be agreed between development and operations team in advance before migration is performed. Keep in view, that this exercise needs to be performed for each service individually - DB Engine, SSIS, SSAS and SSRS.

It's a very brief list, but these points can at least help you align your strategy in some direction when you are totally blank on how you would plan your migration. If you have better tips that can add value to this post, please feel free to share your comments.

SSIS and StreamInsight Integration

Have your ever heard of something called "StreamInsight Multi Stream Transform" ? I am sure the answer would be "No", else you know what I am talking about. When I read about this subject, I instantly started thinking about why I need to use StreamInsight from SSIS ( probably to calibrate the incoming / outgoing data stream from StreamInsight engine), and is it even possible. A technical mind immediately jumps at the implementation level as soon as the requirement subject strikes the mind. I immediately started thinking, whether I can use Script transform and hook into StreamInsight by some means. StreamInsight is one of the least explore technologies in SQL Server stack of technologies, and generally most of us have explored CPM (Corporate Performance Measurement) much more than CEP (Complex Event Processing).

SSIS is gradually evolving as a rich platform that can facilitate much more than the very definition of what we term as ETL.

1) Script Transform is the first window connecting SSIS to the managed .NET / unmanaged COM world. We already have established examples where we can use this transform to use services like Bing Maps, SSRS and others.

2) Data Correction transform is another upcoming transform that connects SSIS to SSDQS in SQL Server Denali. Considering this transform coupled with Data Profiler Task and Data Profile Viewer, SSIS has sufficient ammunition to target corporate data cleansing assignments.

3) The transform that I discussed above "StreamInsight Multi Stream Transform" is a transform that has been developed in a POC carried out by SQL CAT Team. Entire explanation of to and fro integration of SSIS and StreamInsight has been wrapped up in a whitepaper, which can be read from here. This whitepaper is under the umbrella of SQL Server 2008 R2, but one of the screenshots which shows SSIS implementation has the icons of SSIS that are only available in SSIS Denali CTP1, which means that it might have been tested in SSIS Denali. So my forensic sense tells me that, it should be very well possible in SSIS Denali too, and logically speaking it should.

I would wish that this transform becomes a part of SSIS Denali, or least the same can be made available on the SSIS Tasks and Components Gallery, so that professionals can leverage this transform instead of building it from scratch.

SSIS Denali CTP1 Designer Enhacements and Improvements

SSIS Designer in SQL Server Denali CTP1 had undergone a lot of changes, and it really looks much more developer friendly. Just by browsing the designer area, one can make a note of the new features getting introduced in SSIS Denali. Below are some notes of the observations that I have captured while working with the designer.

1) When you open a new package, the first glance goes towards toolbox. All tasks and transforms are not categorized in discrete categories. Favourites and Common are two common categories that are available at Data Flow and Control Flow level, and rest of the categories vary depending upon the selected tab. Also toolbox now has a small description tab, and each task / transform has a description which gets displayed here. A help button is available in this tab, and it opens up context specific page from BOL. Even icons of tasks and transforms looks much relevant to the functionality that the task / transform caters.

2) UNDO was one of the most awaited feature and it can be seen now in the toolbar in active mode. Test is and it should work fine.

3) Data Viewers are now operated in a new way - you can enable / disable data viewer on a data flow path.

4) A new menu option is available when you right-click on a data flow path - "Resolve References". It's a better mechanism introduced in this version, to resolve mappings using an organized editor.

5) Package designer area has a zooming scale on it. Using the same one can zoom-in and zoom-out the package view. Locator window has disappeared but this new scale can be considered as the replacement for the same.

6) Tab options area now contains two small new buttons - "Parameters and Variables"and "Toolbox". SQL Server Denali introduces a new concept of parameters and it changes the way we work with variables too.

7) New transforms like Data Correction, Source Assistant, Destination Assistant are available.

8) Right click on the solution and select Deploy. You would find a new Deployment Wizard, and effectively a new deployment model. You would also find new menus like "Convert to Legacy Deployment Model" to support older model of deployment which we are used to in versions prior to Denali.

9) Try creating new variables, and you would find data types like DBNULL. There is a long list to check out in the data types section.

10) Try creating a new derived column, and you would find that the default data type is of DT_STR i.e. string data type and not DW_WSTR i.e. Unicode string. I am still to analyze more thoroughly on this behavior.

Some of the features that I still miss are coloring of icons when they are configured using expressions, ability to specify data type in derived column transform by using editor itself without the need to change it from advanced editor and exporting package design to jpeg/pdf formats. As of now it looks that BIDS is here to stay, but it's too early to comment anything.

These are my observations just from the initial glance. There's a lot more to explore and I am sure that by the time I would complete a thorough analysis, a new CTP version would be on the horizon with a lot of new offerings and fixes. Feel free to share your observations and/or experiences and help to make this list more comprehensive.

Data Quality Services and SSIS in SQL Server Denali

SQL Server Data Quality Services (SSDQS) is one of the new advancements in SQL Server Denali that is getting crafted at Microsoft labs. If you try to google out on this subject, you would find almost no content even on MSDN, as this development of this service is still is progress, and this is probably one of the reason that it has not been released in CTP1.

These days I have been spending quite a lot of time analysing SQL Server Denali CTP1. SSIS has already opened an interface to take advantage of this service, and this is in the form of a Data Flow Transform - "Data Correction" transform. The below image shows the interface of the editor of this transform.

Data Cleansing is a very big exercise and is one of the most integral part of any data migration project. There are a lot many different kind of portal based tools available in the industry for data quality, having a sort of operational interface that Master Data Services provides. The beginning of Data Quality related services and components began with SQL Server 2008 with the inception of Data Profiling Task and Data Profile Viewer. In my views, making data quality related services available in ETL is a very wise move and symbiotic for SSIS and SSDQS. This would help to open another very strong avenue where SSIS can serve in a very promising manner i.e. Corporate Data Cleansing projects, and SSDQS got it's very first integral client as SSIS apart from the DQ Client.

SQL Server has introduced many management libraries like AMO, SMO, DMO, RMO etc over the period of time. Data Quality Services can be expected to introduce a brand new object model something like Quality Management Objects - QMO, that can be operated by client applications like .NET and Sharepoint, where service providers can build their own flavor of data quality solutions using this framework. These are just some thoughts that my mind is sketching out, knowing SSDQS is on the horizon. I would have to wait to see if my vision about the roadmap for SSDQS is correct, or Microsoft surprises us even beyond our imagination.

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 :)

SSAS Engine , SSIS Engine and SSRS Engine

Speaking about Microsoft Business Intelligence stack i.e. SSIS / SSAS / SSRS, there are different engines associated with each services. If you are ignorant about these engines, you probably are not fit to design the architecture of your solution using the respective services. The major engines that comes into consideration when you are using MS BI stack are as below:

1) SSIS Runtime Engine - This engine takes care of the administration and execution section of SSIS. In a developer language, I would consider it a Control Flow + SSMS of SSIS.

2) SSIS Data Flow Engine - This engine can be considered as the Buffer Manager of SSIS in-memory architecture.

3) SSAS Formula Engine - The engine takes care of resolving the retrieval of members on any axis of an MDX query. Tuning the performance of this engine has much to do with MDX tuning.

4) SSAS Storage Engine - This engine can be considered as the Data Manager of SSAS, which decides what data needs to be fetched from where. If you trouble Formula Engine, there is a good possibility that this would cascade to Storage Engine, which directly deals with aggregations.

5) SSRS Service Endpoint - This cannot be technically considered as an engine, as most people would argue that rendering / authentication / processing are engines, but I consider these as extensions rather than engines. This endpoint takes care of the administration part of SSRS. Anything that you can do with Reports Manager is a virtue of this endpoint.

6) SSRS Execution Endpoint - This is the endpoint that one would like to award the medal of being an engine. This endpoint takes care of executing the report right from processing the RDL till rendering the report.

You can read more about each of these in MSDN as well as different books and blogs. But until you thoroughly understand the function of these engines and you are designing the architecture, I am of the opinion that one should not feel confident about the architecture design.

SSIS Architecture Design Document

When I was quite junior in the IT industry, I use to wonder how one designs an architecture document. I used to collect templates of these documents from different sources. Today when I have matured enough to design one myself with my own sense and understanding of system, I feel that a basic idea of the sections that the SSIS architecture design document should contain would be of interest to all those minds who are growing up as future architects.

Delta Detection - This section would contain details about incremental load and Change Data Capture.

Extraction & Staging - This section would contain details regarding the treatment of extracted data and whether permanent / temporary staging are being used. If a staging area is used, it would require more elaboration about the same.

Facades - Interfaces and contracts are a vital part of any architecture design. This section would contain the views / SPs that you would create in your OLTP system. Even your CDC SPs can act as your facade. Also this section would describe whether you are using a push / pull model.

Trigger mechanism - Many solutions contain a application which triggers the ETL cycle. Whether you would use any scheduler like Autosys / SQL Agent should be described here.

Process Control Structure - Each category of packages classified by functionality should log which package was doing what and when. This would provide monitoring layer and control over the entire ETL execution.

Environment configuration - All details about environment variables, package configurations, and the storage locations of those configurations should be described in this section.

Tracability - Logging details should be mentioned in this section. Auditing can be of huge importance to support teams, so this section should be documented and designed keeping support teams in consideration.

Transaction Handling - Details about transaction handling within and across packages belongs to this section.

Error Handling - This section is very important to almost all teams screening your architecture document. Also is your error handling is weak, your transaction handling might suffer and this can be catastrophic to any kind of data loading.

Automation - Particularly in data warehousing projects, automation is one of the regular requirements. Trigger mechanism falls within the automation umbrella upto an extent.

Scalability - The more you loosely couple your package structure to take advantage of parallelism and more you design your logic keeping memory (synchronous and asynchronous transformation)in consideration, the more you gain in scalability. These details should be mentioned in this section.

Hardware Configuration - Server configuration details right from the number of cores till the amount of RAM that you intend to use belongs to this section.

Change Control & Deployment - Deployment methodology and location should be elaborated in this section. Though change control is a part of the configuration exercise, it's linked with how you would manage deployment. So it would make more sense to elaborate Change Control methodology in this section.

Feel free to add your comments to make this section more complete.

Zip and Email SSRS reports requires use of SSIS - True or False ?

Recently one of my blog reader posted me a question where the requirement is to zip and email SSRS reports to users. Reader wanted to check with me if this can be done using subscriptions and any kind of delivery method. The two main delivery methods available are file-system and email delivery, and to the best of my knowledge, none of them include zipping of files.

When report files are huge in size, to the magnitude of several MBs, emailing those files is quite a big challenge and also it's not an advisable option. In my views, SSRS team should make an option available of zipping the file to at least any one compressed file format, when the file are delivered using file-system or email delivery methods. In my views, these are some of the very common reporting related requirements that SSRS would have to incorporate to gain acceptance as a serious and enterprise class reporting platform.

The order of this process can be as follows:

1) Create report output stream using subscriptions, and the output can be in requested file format.

2) Zip the output stream to any compressed file format, at least .cab format which windows has been using since the times of it's evolution.

3) Deliver the file using file-system / email delivery methods.

As of now, there are no out-of-box options to directly deliver zipped reports to file share output / email the same. SSIS can be used for the same purpose. In the whitepaper that I have authored, I have explained how to generate SSRS reports in bulk using SSIS and SSRS 2008 R2. With little more coding in the Script task, you can zip the created files and email them if required. Two of the articles that demonstrate how to implement the zipping functionality can be read from here and here.

Achieve high availability of cubes using SSAS and SSIS

Recently, I was faced with two different questions at two different events and both of questions were directly or indirectly linked to high availability of cubes for querying. Those two questions were:

1) What is the difference between scale out and scale up?
2) How would use ensure 24 x 7 availability of a cube, considering the point that globally users are accessing the cube, and the cube should always remain available for querying?

The answer to the first question is when you need to achieve parallelism for concurrency in querying or processing, you distribute / replicate processing operations and/or data on multiple nodes. Scale up usually means that you increase the capacity of the host to enable the server to cater the incoming load. When the capacities of scaling up ends, scaling out steps in.

The next question was quite interesting, and the challenge was that I was in a situation to instantly think of a design and answer this query. I answered this question correctly, and to my delight, I found this whitepaper which is exactly what I answered. Such moments bring a lot of happiness and confidence that my knowledge has not gone stale and I can continue to provide consulting in MS BI business.

The presentation layer is coupled with SSAS query server. Data is read from relational engine and cube is processed on a separate server, which can be considered another layer altogether. After the cube is processed, query server and processing server are synchronized. For multi-server synchronization, SSIS is used. The below two diagrams demonstrates the same. Entire whitepaper can be read from here.

How SSIS Performance can be optimized using Change Data Capture and/or Change Tracking , MERGE , and Filtered Indexes

Delta detection, source data extraction and smooth sourcing of input data from OLTP and/or Relational data warehouse is a part of the overall ETL process. Though the performance optimization boundary is outside the regime of SSIS, but these are the fuelling stations of SSIS. OLTP and relational data warehouse are the two main data stations from which SSIS fetches and/or loads data, and relational data warehouse (i.e. data marts / ODS, whatever be the case) is often used to read as well as write data, and optimizing these relational sources can contribute to performance optimization of SSIS packages. In case if you are using SQL Server 2008 platform, some of those that can be effectively used for optimizing data access especially by SSIS packages are as below:

1) Change Data Capture (CDC) and/or Change Tracking: I would not go into the details of the difference between Change Data Capture and Change Tracking. But this is one of the effective means of delta detection. Actually more than performance, it provides a controlled and automated infrastructure for delta detection. Depending upon the kind of algorithm followed in the master ETL package that handles delta detection and extraction, CDC / Change Tracking can be used. A whitepaper that explain in depth methodology of integrating CDC with SSIS can be downloaded from here.

2) MERGE statement: MERGE is one of the smart means to off load processing of data for Slowly Changing Dimensions. A nice brief article from kimballgroup explaining the same concept can be read from here.

3) Filtered Indexes: Mostly everyone is aware of two types of indexes in SQL Server - Clustered and Non-Clustered. But Filtered Index is a new advancement and is a very useful weapon to optimize OLTP as well as relational data warehouse for optimizing data access without impacting the overall system or impacting only the system for which SSIS needs a helping hand from the relational sources for faster data access. Again I would not go into the details of filtered indexes, and a thorough section that explains the same can be read on MSDN from here.

Mostly we talk of how to optimize SSIS package performance, but the bigger picture is how do we optimize ETL process where SSIS is the driving ETL tool, and these pointers can provide some direction in an effort to optimize SSIS package performance, though indirectly.