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.

PowerView with SSRS 2012 Native mode and Excel 2013

Office 2013 Preview has been released and is available for download from here. More about Office 2013 enhancements can be read from here. Powerpivot has now become a native part of Excel 2013, which was available as a separate add-in in prior version. This very of powerpviot comes with few new inevitable enhancements like ability to edit tabular models. This is a very welcome news. But the news that I am not happy with is, there is also an add-in available for PowerView with Excel 2013.

After reading some of you might wonder why am I not happy with this news as this makes Excel a very powerful client tool as the reporting capabilities would get a silverlight based animatory touch. The reason is that as of this draft and to the best of my knowledge, PowerView is not available with SSRS 2012 native mode. It's a Sharepoint only available feature. In my opinion, it should be made available in BIDS as well as SSRS native mode too, in the form of an external add-in and rendering extension respectively.

Another thing that seems strange to me is why is PowerView add-in available as a COM add-in. I am not that deeply aware of office add-ins, but from a general development standpoint, from the name I understand that its a COM component. In .NET terms it's unmanaged code / component, instead of a .NET Framework based managed code. If that's the case, my curiosity asks why COM ?

An interesting outcome of this can be that Powerpivot in Excel 2013 would become the new BIDS as well as reports manager for PowerView based reports, instead of buying entire enterprise class license for better compressed reports, data alerts and powerview which are the major enhancements available in SSRS 2012 Sharepoint integrated mode.

One another such very interesting capability is provided by PivotViewer Extension for Reporting Services, but its in CTP2. But even this application is available with Sharepoint only.

How to install PowerView ? Why Data Alerts and Powerview are not available in native mode SSRS ?

This blog has been silent for quite some time. I am recovering from an accident, a few turbulence in personal life including laptop crash. I am also busy these days in my new book authoring assignment. I would like to keep the details of the book under the covers, till the time gets mature to announce it. SQL Server MVP Deep Dives Volume 2 got released last month, and I have authored a chapter on this book titled "BI solution development - Design considerations". Coming back to business, this is my blog post after a long break from blogging.

SQL Server 2012 RC 0 got released by Microsoft 2 weeks back, with a deadly bug on Distributed Replay Controller in the setup. Due to this bug I had to skip installation of the component itself. SSRS enhancements is sharepoint integrated mode are shipping two new kids - Data Alerts and PowerView which was formerly known as Project Crescent.

1) I have been receiving queries from many developers on how to install PowerView and how to verify whether PowerView is installed. The answer to this question is, if you have installed SSRS Add-in for Sharepoint products on your Sharepoint farm, consider it granted that PowerView has been installed. This component is the one responsible for installing Data Alerts, PowerView as well as the entire user interface related to SSRS in sharepoint. Keep in view, PowerView and DataAlerts are silverlight based applications and can be operated only in a browser. Simplest way to verify whether powerview is installed, is by deploying a shared dataset on a sharepoint document / reports library. Select the drop-down options for this shared dataset, and you should find an option named "Create PowerView report".

2) Why PowerView and SSRS are not available in native mode SSRS ? Question is very valid, as Reports manager is also a web based application that queries SSRS web service. If these silverlight based enhancements are intended to be made available in native mode, I don't see a technical challenge to it. But these enhancements are packed only with the add-in, which means they are strategically intended to be made available only in sharepoint. So to me it seems more of a business decision than a technical decision. Affording Sharepoint just for a client tool is too heavy, is the reaction that I have been observing from the community. Let's see how community reacts to it in the long term.

I promise my blog readers that I would try to come up to speed on blogging as soon as possible. Keep writing to me.

SSRS Denali enhancements in Sharepoint integrated mode

I apologize to my blog readers that I have been very passive in blogging these days, but I am just recovering from some personal issues and getting on track. I promise to be back on normal pace of my blogging within a weeks time.

Advancements in SSRS Denali, as heard and seen in TechEd seems to be bridging the gaps that should have been ideally filled up in the R2 release itself. But its better late than never !!

1) The first gap that is being filled up is SSRS Sharepoint integration. Though integrated mode has been supported, but implementing this mode has required a cross IT team efforts. Also its a know fact that reports deployed on sharepoint integrated mode, have been found to be performing slower compared to native mode. In my understanding, SSRS Denali brings SSRS as a shared service in sharepoint, and effectively it would benefit from all the advantages of being a shared service in Sharepoint.

On the other side, alarming situation is that endpoints that used to work for sharepoint integrated deployment, might not work in the same manner with SSRS denali. This can be a major migration blow for applications accessing reports programatically from application servers using these endpoints.

2) SSRS Logs have been very limited to ExecutionView3 tables, and rest of the help was provided by tools like Fiddler to troubleshooting. In integrated mode, logging also seems to have been considerably improved.

3) Data Alerts is one of the new enhancements in SSRS Denali, which can be thought of a SQL Scheduled Job implementation that sits in Sharepoint DB, to watch over the change in data. This sounds very good, but it looks little risky from the way it can continuously trouble database servers to check for alerts, as I have not heard about how much control is available of the frequency of alerts.

4) Crescent is another flavor of self-service reporting, requires silverlight and works in a browser in Sharepoint only. Presentation of data looks like fancy, and even controls similar to motion framework for trend-analysis are being introduced in this tool.

5) SSRS Denali is also bringing better export options like ZIP formats, support for Office 2007 based export formats, better compression, better performance in sharepoint integrated mode and more.

From a higher level, most of these are welcome changes. But from an architecture standpoint, it would be interesting to see whether sharepoint integrated mode ssrs deployment, changes architecture in a big way and whether it brings dead end to seamless migration from R2.

Planning visually appealing dashboard using MS BI

The notion of developing something that is very visually appealing is so much ingrained into the minds of business stakeholders and professionals, that whatever technology that provides fancy, animating, and colorful visuals, they develop an image that its the technology of choice to develop visually appealing dashboards. There are several points to consider, while planning to develop a visually appealing dashboard. Below are few points which are very important to consider when you plan to develop a visually appealing dashboard using MS BI and Sharepoint BI.

1) Visually appealing is a subjective measurement, so the design should be developed keeping the end users in mind. There is a difference between a visually appealing website design and visually appealing dashboard design. Keep in mind that websites might be meant to capture data whereas dashboards always report data.

2) Consider controlling the level of fanciness on your dashboard, as the intention of dashboard is to make the user concentrate on data and facilitate the user to visually and effortlessly analyze data. Too much fanciness like animations, too much colors, etc would take distract users attention from data and might even confuse the user.

3) You might have seen MS BI VPC and got impressed with the visuals of Excel Services, Performancepoint Services and other elements. But do not get carried away but just what you see. Consider studying the inventory of features that these technologies provide and map it against your requirements.

4) Do not base your POCs or analysis based on easy data sources like XML files, Tabular data or Sharepoint lists. Mostly data flows from an OLAP model into dashboards (at least in tactical or strategic flavors).

5) Consider studying the architecture of the technology before selecting it for your visually appealing wonder. You might create a POC and find that data from one spreadsheet displays data well with excel web parts and when you need context-sensitive and user role and privilege specific data, the model might fall apart.

6) In the dashboard parlance, visually appealing would mean analyzing data interactively like drilling, slicing-dicing etc and other form of visually appealing would be data analysis using visualizations and info-graphics.

You do not hire a fashion designer to design the interiors of your home, to make it pleasing to the eyes. In the same way, theory of making a visually appealing website cannot be applied to develop a visually appealing dashboard.

Load balanced BI Solution - Storage and deployment strategy

In a MS BI platform based solution, Sharepoint it mostly used as the deployment platform for web-applications, reports and other artifacts of the solution and it's a very standard practice. But this remains as simple as it sounds only till you have one server per technology / hosting platform, for eg Application Server, Database Server, Report Server, Analytics Server, ETL Server etc.

As soon as you start scaling your solution infrastructure, like load balanced federated server solution, implicitly your solution gets replicated across servers. There are standard practices to keep the codebase synchronized, but the caution area that is not visible implicitly is that Sharepoint itself can act as a data storage medium for a variety of content. In fact Sharepoint is recognized as a Content Management System. You can store SSRS reports, Performancepoint artifacts, Excel Services worksheets, Visio diagrams and much more on sharepoint. If you are not well informed of the internals of how sharepoint stores data, you would end up replicating tons of these artifacts on different servers.

The key piece to this issue is a one piece of information - every sharepoint site has a content database and that database is a SQL Server based database. Application can store data in sharepoint in sharepoints lists and other objects, and BI solution artifacts are also stored on sharepoint sites. When you replicate your sites to different servers in a load balanced environment, all sites should refer to a common content database, and configuration should be done accordingly for all the sites.

This strategy gives a huge benefit, the prime ones being centralized deployment and bullet proof shield against data integrity issues. Anything stored on one site is automatically available on all other sites, avoiding any synchronization between sites replicated on different servers. Also you deploy artifacts on one site, it would get stored in shared content database and effectively the same becomes available on all the replicated sites.

Dimensional Modeling, Cube Design, Dashboard Design, ETL Design, Reports Design etc are popular BI areas, but solution design is the ultimate expectation from an Architect. Professionals can shy away by taking refuge under their strength area like Data Architect, Application Architect, BI Architect, Integration Architect etc, but someone would have to assume role of driving these genius and this class of architect is known as Solution Architect.

MS BI Dashboard Design : Identifying right components for your dashboard

When you think of dashboard development in Microsoft BI world, the only name that would pop-up is Performancepoint. A general perception is that professionals who are skilled at using performancepoint are dashboard development experts, but this is a very big misconception. To develop an appealing and effective dashboard, one needs the skill of translating user requirements to reporting elements. Even dashboard ultimately is a summarized report.

One of the common issues that can be seen on dashboards developed by professionals, who do not have right kind of reporting experience, can be seen on KPI reporting. Mostly KPIs are reported using traffic light indicators, and this has built-in support in performancepoint to create a scorecard and host KPIs. But over the period of time when user start using dashboards regularly, they feel the need to see the functional detail on the KPI on the dashboard itself. For example, when the user is analyzing a KPI (Cost Performance Index, for example), user would like to check different thresholds associated with this KPI. The values of interest can be, what is actual, what was target, what were the thresholds for Green, Amber and Red indicators etc.

Using stop light indicators, you can just display the status of the KPI but you can't display thresholds associated with the KPIs. Here a PPS professional would not know the solution, and the most that PPS developer would do is show the threshold values in the tooltip, which is not a solution at all. A seasoned reporting professional would immediately suggest a bullet graph for this, which is available in SSRS. You might have gone with the option of PPS just due to the scorecard feature, and when you figure out that even SSRS with Bullet Graph would have sufficed, the earth under your legs might start shaking !! This example is a very commonly found example, and if you have worked on a few reporting projects, you must have come across this scenario.

Selecting right element for the right requirement is the key to any reporting solution, whether it may be a simple operational report or a sophisticated dashboard. So when you start thinking about your dashboard design and development using MS BI tools and technologies, you need to make sure that the skill that should be primarily available with you is a reporting expert and not a SSRS / PPS expert.

Free dashboard development tools and softwares

In today's budget economy, everyone wants free tools and softwares, and whosoever provides the same FREE, manages to invite a tsunami full of attention from the target audience. But I see it from a different perspective, and one point is what you want for free and why you want for free ?

I recently received a query from one of my blog readers, requesting me to share any free dashboard development tools or software for use with SSAS. In my perspective if you are having a data warehouse and/or data mart and you intend to use a free dashboard development tool, I interpret it as any or all of the below:

1) You are not serious about your dashboarding solution, so you do not intend to invest much or anything on this front.

2) Your dashboard solution is expected to be of a very modest size, so you do not intend to invest.

3) You might be wanting to get a feel of a dashboard, so you might be intending to create some POC or flavor of dashboard.

4) You want a dashboarding solution, but you are out of budget.

In case if you have a enterprise class or even SMB class data warehouse and/or data mart, right at the time of solution design and estimation, dashboarding would have been considered into your estimates. Now as you are looking for a free dashboarding tool, considering the above points, I derive that you do not need a very high-end complex professional dashboard development platform and/or software. In that case below are the options that can help you create your dashboard with your existing licenses, which would be almost free for you.

1) SSRS 2008 R2 has great UI capabilites like data bars, sparklines, indicators, charts and more. Using the same you can emulate a very rich dashboard UI. If you have an enterprise SQL Server license, you already have SSRS and you would not need anything extra to develop your dashboard.

2) .NET / MOSS Development, is one of the other options to create a dashboard. .NET Framework is free and redistributable, and there are many editors using which you can develop .NET based UI and host it on your reporting / hosting platform to emulate a dashboard.

3) Excel is also known as poor man's or business users dashboarding tool. You can develop dashboards by exploting rich UI capabilites of excel, and make the workbook available through some office viewer component to give the feel of a dashboard to your users. Most enterprises would have MS Office license inevitably and the only cost challenge would be to figure out how you would make your excel based dashboard available to your users. Also if you have Excel Web App and Excel 2010, you have a kind of collaboration platform for your dashboard too.

Performancepoint Services is definitely not free, comes only with enterprise edition of MOSS 2010, has limited customization capabilites and skilled professionals on this tool would be scarcely available in the industry. I do not intend to convey that these options can replace PPS, but considering the reasons you would be looking out for free dashboarding tool, these options should be able to cater most of your requirements. If you are on the Microsoft platform and you are in hunt of free dashboarding tools, you should check your existing set of licensed tools and you might find that your best bet is just below your sleeve !!

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.

Planning MS BI project / solution deployment

In my views, "Failing to Plan means Planning to Fail". Most project managers and technical leads undervalue the importance of deployment in the solution development life cycle. Deployment planning is often considered even out of solution development perspective, and the complexity of this process is only realized when the deployment sits on the hot seat. In any professional IT products and services providing organization, you would find regular environments like Development, Staging (UAT), APT (Application Performance Testing) and Production. Each of these environments can have different corporate policies that implies a set of security and access methodologies in these environments. So if you have not planned your deployment strategy, each environment can compel you to make changes to your development.

A typical MS BI solution would consist of technologies like Microsoft .NET, SQL Server - SSIS, SSAS, SSRS, MOSS with/without enterprise services, Microsoft System Center, Team Foundation Server, Microsoft Office among the major application and database components. Below are some of the points to keep in view, which can have a huge impact on the overall solution development, and ignoring these points generally would emerge as a show-stopper to develop a generic build and deployment package.

1) Server and Instance names: Generally to make speedy progress development in development systems, often developers use hard-coded database names / instance names / server names. Different environments can have different server and instance names.

2) Service Accounts: Initially when development machines are setup for the team, developers tend to use whatever account has the best privileges or their own account. This would immediately fail in environments where a separate team would be deploying your build and there would be a separate dedicated service account created for an application as per the corporate policies. Also in many secure production environments, network admins keep on changing service account password at regular intervals. So if you are using user id as well as password to access web services and you have not programmed your services to pick up credentials from a configuration store, you just hit another show stopper.

3) Software Configuration: MOSS and .NET development can require creating many proxies and web / service references, which are mostly sourced or build in the form of DLLs. These can make your solution dependent on certain libraries, which are hard to detect as the development would federated across various development machines. This creates a discrepancy in software configuration. Again certain developers might be using different IDEs with add-ons that would dump external DLLs, for example AJAX Toolkit with VS 2008 and Sharepoint Designer. This needs to be carefully planned, as your server environment might not contain all these libraries.

4) Security Configuration: This is one of the most complex bit. Different technologies talking to each other would be communicating mostly through Windows Integrated Authentication or Active Directory Federation Services ( Windows Identity Foundation / Single Sign-On) configured with a claims / token based mechanism. It is quite possible that development environments can have different security configuration than other environments. During development this goes easily out of focus and the prize to pay for this is very heavy afterwards.

Site Collections / Web applications created on MOSS would be using HTTP / HTTPS with certificates which would need to be installed and configured on the site. Corporate policy might not allow you to copy certificates across servers, and this may come to light only when you might want to deploy your solution and infrastructure teams would raise a red flag for certificates.

I have been personally evident of a scenario where different developers developed their SSIS packages with ProtectionLevel property of the package set to EncryptSensitiveWithUserKey. Developers keep joining and leaving the team and they log on to systems using their Active Directory IDs. When the solution was deployed on a different environment, the solution started failing. Now these packages can be fixed only using the credentials of the users who developed them, and this iceberg almost sank the ship.

5) Automation: Many things are done manually like executing DB Scripts, deploying SSRS reports using BIDS, deploying MOSS site using Sharepoint Designer. You would find yourself with a new development cycle, when infrastructure teams responsible for staging and production environments would refrain you from access to these environments.

6) Shared environments: Solutions that are equipped with exclusive servers are quite lucky and also are generally first of their breed or mission critical. In any organization that is having an infrastructure setup in data centres for their IT Services would have shared environment for different application services like SSRS Reporting Servers, MOSS Application Servers etc. One may overlook this detail considering that fact that the same version of the service is used. But at certain levels overlooking key piece of information can be very heavy, for example, one may change web.config file which is the configuration file for entire server. Shared servers would be catering an array of applications, so when you deploy your solution you would not have the luxury of changing these files. At this point again your solution would fail.

Planning your infrastructure followed by deployment can provide insight into many restrictions and dependencies that you may discover very late. And such discoveries can either break your solution or invite a huge effort to develop your deployment setup. Remember one thing, "Failing to Plan means Planning to Fail" !!

Infrastructure design of a Microsoft Business Intelligence solution

In the job description of a Technical Architect, one often finds few items that architects might want to avoid getting involved. This is about fitting nuts, bolts and wires to rig different servers and components. Yes, you guessed right, it's designing the infrastructure and connecting all server and components at the physical layer. Personally I do not enjoy this task a lot, but still it drops on me as a part of my job. The major challenge in this task remains to keep a track of what ports are supported by corporate policies on which servers. Again different server products talk on certain default and other supported ports. It is very easy to get lost in this spider web of connections. Again on the top of these port configurations, there are protocols like HTTP, TCP etc, firewalls and federation servers for single-signon. It always helps to have a handy reference checklist of these port and protocols that make different servers and server technologies talk to each other.

In any typical Technical Architecture document, at a minimum, you specify details on how developers would connect to your application server which would in-turn connect to DB server, and these details without fail would need ports and protocols related details. This can grow as complex as the number of servers and components you have in your solution design. Hardware estimation and sizing is one other such area, but you might find sizing tools for the same which can provide an estimate based on the projections you provide for your target environment. Below are two such diagrams from a Sharepoint perspective which gives an overview of what kind of details you would require for an infra design perspective to rig your solution.

Image Courtsey: MSMVPS.com

Dependency analysis for estimating BI solution development efforts

Creating a WBS (Work Breakdown Structure) is the starting point to start listing your tasks for any development efforts. After a list is created, a complexity factor is introduced for each task, and based on that a generalized amount of effort for each category of complexity is allocated and the sum of the same becomes the total tentative effort. Categorization of tasks from simple to most complex is generally classified on the basis of deep understanding of the dependency analysis of these tasks.

Dependency can be of different types like dependency on tools, dependency to use a component, dependency on infra, dependency on platform, dependency on operational processes involving approval cycles, dependency on release environment (Dev/QA/APT/UAT/prod) constraints etc.

This needs to be accounted while assigning a complexity factor to even the most modular level of work item. It might sound a very exaggerated theory from the way it looks. The best way to realize this is by the way of a real life example. One of the examples from my experience that suits this flavor is "Activating excel services on MOSS 2007 and ensuring it's available on the site". It sounds a very simple item and one might not even account more than an hour for the same, and the same was the case on a project on which I was working. The issue chain started as below:

1) Firstly one needs to activate excel services on the farm, which is pretty simple (clicking just an action button).

2) You need to have a site on which you need at least a web part page.

3) You need to have Excel Service enabled for the site and site collections, so that you can use the service on the site and get Excel Web Access web part on your page.

4) When you configure excel web access web part with a workbook uploaded to some document library on your site, you need to ensure that the same should be identified by excel services as secured location. For this you need to configure trusted locations for excel services.

5) To configure excel web services related setting you need to have shared services administration site installed in moss 2007 environment, which is an explicit process done only on demand by application engineering i.e. infrastructure and application management teams.

6) To install this site, you need to have Indexer service enabled. Also you need a web application created that you can use for this shared service provider, and this web application should not be configured to use "Network Service" for the security configuration.

7) You might need excel installed on this environment, as Excel 2003 workbooks many a times creates issues and you get an error on the web part when you try to configure your web part with this workbook. Most organizations would consider installing office only on client machines, and installing the same on server would be considered as a security exception.

From the above example, it's very easy to tell that how much experience goes into dependency analysis and how important is this factor to consider during estimation. If you have similar stories, feel free to share it with me.

BI Capacity Planning Tools for Sharepoint 2010 Business Intelligence

Sharepoint 2010 is a fairly new product even as of date, and I keep hearing different stories from my professional network, where organizations are still not having enough maturity on sizing sharepoint environments even for development purposes. In thoroughly process oriented organizations, generally you would find regular server induction teams/groups and exercises to incorporate new/upgrade versions of server technologies/products. Once these teams craft the induction process which includes installation, maintenance and support SOP (standard operating procedures), the same is floated as the technology standard throughout the organization. As per this theory, everything sounds very procedural and under control.

But many a times architects often comes under fire, when they are asked to submit different documents describing application architecture, development environment configuration, capacity and sizing estimates for a technology that has not got inducted within the organization through SOP. Architecture has significantly changed from Sharepoint 2007 to Sharepoint 2010, the primary one being the change from Shared Service Provider (SSP) in 2007 to Service Applications in 2010. Services that can be used/categorized for BI related requirements can be managed in the form of service applications. Examples of such services are Access Database Services, Secure Store Service, Visio Graphics Service, Business Data Connectivity, Excel Calculation Service, and PerformancePoint Service.

This was just the tip of the iceberg, and when you have Sharepoint 2010 as a part of your BI solution, the journey of an architect just starts with selecting services of use followed by capacity planning and related documents for infrastructure and storage teams. In such cases, there are some tools, reference topologies, and architectures that can make your life a bit easier as an architect. Below are link to such resources:

1) HP Sizer for MOSS 2007
2) HP Sizer for MOSS 2010
3) Reference architecture and topologies diagram
4) Reference architecture and topologies reference material

Considerations for selecting a technology stack for your BI Solution

Once you have your functional requirements ready, and your BA's would head to you with some powerpoint slides having some fancy graphics of some complex graphs and visualizations, you can consider it as a sign that you would be hit with the question of which technology are we going to use to develop such visually appealing solution. Selecting the right technology stack is a part of every solution development, and it is almost given that SQL Server 2008 R2 and Sharepoint 2010 are NOT always the right answer. There are certain factors that one needs to consider, especially if you are acting as the technical architect for the project. Cost is one of the most important parameter, and till it gets figured out, there is no point in even selecting a technology stack. Apart from this, a list of few such considerations in my viewpoint as listed below:

1) Analytical Visualizations: List down all the type of visualizations required by your solution. Many a times, Business Analysts just get carried away by visualizations, though what they need to analyze is very simple. So get this sorted out first. A very basic example is "Tree Map". PPS 2010 and SSRS 2008 R2 still does not have this visualization. If the limitations of visualizations are too much with PPS and SSRS for your requirements, you might want to use some another reporting solution and continue with SSIS, SSAS and DB Engine with SQL Server 2008 R2.

2) Technology Integration: Mostly whatever you develop out of any technology, it needs to be deployed on a collaborative platform like Sharepoint. If the technology / component cannot integrate on your collaboration platform, it is useless. Most organizations like security configuration using ADFS 2.0 for single sign-on and say that some third-party tool that you selected is not known to be good with claims based authentication. If you select it, you would sign a contract to get yourself roasted.

3) Time to Market: PPS 2010 might be able to cater all your requirements. But ramping up a team having trained skills in development using PPS 2010 can be a challenge. And if you plan to go to market with your solution in a very aggressive timeline, skills like MDX, PPS, etc can be hard to find. And provided you already have skilled resources, it would definitely need time to design - develop - test and - deploy the solution that you would develop from scratch. You might want to use off-the-shelf ready tools / components for a part of your solution.

4) Ease of deployment: Most IT enables services providers and consumers have their own infrastructure where they host their IT solutions and services. This infra can be as huge as few datacenters in different cities, and hardware like SAN, RAID, Load balancers and Clusters. The technology you select should be able to fit in this topology as well as have the potential to exploit the capacity of the existing infra.

5) Performance: Almost every solution provider or consumer would have got burnt little or more with performance challenges. This factor is on the cards for almost every technology stack, before it even gets nominated for evaluation. So the technology that you plan to consider should already have some benchmarked performance results or you should consider creating some POCs to extract some form of performance results to compare against standard benchmarks.

6) User Training: You might want to select a tool that is too rich in feature set and visually appealing, for ex Tableau. But your user base are power users who just wants ready dashboards without any efforts, and they would discuss the results of this dashboards on a collaborative medium like Sharepoint. Though you have the best tool and it might pass all of the above criteria, it might not pass this parameter.

In summary, while selecting the technology stack for your solution, ensure that you go through a rigorous analysis from all aspects, not just feature set.

Visio Services Data Authentication

Visio Services is one of the least attended MS BI service, but is one of the most effective service in terms of presentation. Visio services in Sharepoint 2010 adds a new level of capability to Data bound visio diagrams i.e. Strategy Maps. Whenever any external service / interface has to access data, authentication is of the the most primary and necessary step.

Answers to all the probable queries related to data authentication in visio services can be found from this whitepaper. You might not feel like reading this right away and I would agree to it, but it's not a bad idea to keep this whitepaper in your repository of technical reference documents.

Image Coutesy: MSDN BOL

Microsoft Silverlight 4 and Sharepoint 2010 Integration

Integration is one of the inevitable aspect in software development paradigm as well as in the scope of my regular work. A .NET application needs integration with OLTP, which in turn needs integration with relational data warehouse. SSAS or your cube development tool / technology needs integration with relational data warehouse, Dashboards needs integration with the Cube. Dashboards are deployed in Sharepoint and 2010 is the latest version of the same.

Now the question comes that where does Silverlight 4 fit in the picture and what does it have to do with MS BI. The answer is that Silverlight 4 does not significantly fit in the MS BI picture, but still it does graze the boundary of it. Sharepoint 2010 has got rich support of Silverlight, like Silverlight web part for example. Visio Services also uses Silverlight to show visio diagrams more interactively.

Now this is basically about a book that I have got, and I have presently the task of technically reviewing this book. I would be sharing this review very soon in a couple of days. I have gone thru the TOC, it has got 6 chapters which looks quite interesting to me. My .NET background basically helps me a lot to make sense of each and every chapter. If your job role is just of a developer and your responsibilities involves designing a report or developing a cube or writing stored procs or creating a package, this book is probably not for you. But in case if you intend to architect an end to end solution, silverlight is like garnishing and decorating the dish you are going to serve to your client. And we know that presentation is as important as is preparation, and as Sharepoint houses services like Performancepoint Services, Access Services, Excel Services, and Visio Services, I am sure that with all these dishes in the house, garnishing of Silverlight is a must in sooner or later future.

You can download a sample chapter of this book from here for free. In case if you want to know more about this book, you can check out the details of this book or buy this book, check it out here (from the packt website) where you have a greater probability of hunting some discount deals.

Update

Below is the review of this book:

1) This book would not be much helpful for Sharepoint and Silverlight beginners, but still Silverlight beginners would be comfortable using this book.

2) Book covers most the new ehancements introduced in Sharepoint 2010, where silverlight can play a role.

3) The best use of this book, would be to use it as a cookbook for developing RIA using Silverlight 4 on the Sharepoint 2010 platform.

4) If you have been developing RIAs for quite some time and want to add the Silverlight flavor to it, this book should be the first step in my views.

5) How to use this integration between Silverlight 4 and Sharepoint 2010 at various phases of SDLC is not explained in an elaborate manner.

Overall, in terms of Silverlight 4 and Sharepoint 2010 integration, I rate this book 6 / 10.

Sharepoint 2010 with limited features on the cloud without BI support = Sharepoint 2010 Online

Microsoft Business Productivity Online Suite (BPOS) is a collection of products that are available on the cloud. These products are Exchange Online, Sharepoint Online, Office Livemeeting and Office Communications Online. These products / services are available in Standard and Dedicated edition. Standard version is a shared services platform and Dedicated edition provides an isolated and dedicated server space in the cloud. Microsoft has made office web apps available for free usage on sky drive, and during the later part of this year, office web apps is also expected to be available on the cloud. This is a complete SaaS offering from Microsoft.

Being a MS BI professional, my interest goes specially in Sharepoint online. Presently it's based on Sharepoint 2007 platform, so it can be easily interpreted that it won't have features like Access Services and Performancepoint Services. During the later half of this year, Sharepoint online is going to be upgraded on Sharepoint 2010 platform. This immediately raises a curiosity in my mind, whether Performancepoint Services and Access Services would be made available in this version. Microsoft has though not officially confirmed anything (to the best of my knowledge), but several Microsoft as well as unofficial sources have confirmed that performancepoint services is definitely not going to be a part of Sharepoint 2010 online. This means an end to the scope of dashboard development and hosting on the cloud.

Access Services may or may not be made available on Sharepoint 2010 online. One interesting feature of Access Services is Web databases. If Access services and effectively creation of web databases on the cloud is made available in sharepoint 2010 online, this can be seen as one of the competition / alternative to SQL Azure. My mind raises a question that when I have capabilities of sharepoint 2010 (though limited) available in the cloud, with web databases facilitated through access services, how many chances are there that I would opt Windows Azure storage and SQL Azure against Sharepoint 2010 online ? In fact, news are that Sharepoint 2010 online might be made available on Azure platform.

In short, Sharepoint 2010 online diminishes the hope and scope of BI by eliminating performancepoint services. Those who are curious to learn more about Sharepoint online can download Microsoft Sharepoint Online Standard Developer Guide. Though it's quite old and there can be changes in the present offerings, but still it would provide a deep insight into Sharepoint Online features.

Panorama Novaview with Powerpivot for NON managed self-service BI

Recently, someone made me aware about an interesting article titled "Powerpivot & Analysis Services - The value of both" authored by Panorama Software product manager. This article explains how Analysis Services can be useful for enterprise to model the analytical solution for their known requirements and how Powerpivot becomes helpful to extend the analytical solution by self-service. It also states how Novaview can help business users to build similar to the capabilities and potential of Performancepoint Services i.e. build KPIs, charts, etc.

If I think of a poor man's analytical BI solution, I would think about the cost. Cost can be gauged in terms of expensive licenses of feature rich editions, and IT staff required to facilitate and maintain the BI solution for volatile needs of the business users. PPS is a part of Enterprise Edition of Sharepoint 2010. Also this does not fall in the category of self-service BI, as a business user cannot be expected to develop a dashboard using PPS. A economic recipe of a poor man's analytical solution can be to use Powerpivot as a data source ( by building cubes using powerpivot ) as well as analysis engine too for front-end tools like Novaview. This can eliminate the need for Sharepoint 2010 Enterprise Edition and one can use Sharepoint Foundation Edition too for collaboration of deliverables developed using tools like Novaview. This is the first part of the savings.

Second part of the savings can come from the fact that these tools are claimed to be easy to learn and targeted to be used by business users than the IT services providers. So the dependency on IT Staff to manage and extend the solution becomes less, which effectively translates to savings.

Of course, these tools cannot be a complete replacement for Sharepoint 2010. But if you need a middle path where you want the BI services to build just few Dashboards / KPIs / Charts by business users, at the same time you do not want to invest into Sharepoint 2010 as you might not be sure that you would exploit the full potential of services like PPS, Excel Services, Visio Services and others, Powerpivot with Novaview can be one of the options to try out. Powerpivot is managed self-service BI tool, but by adding a layer with tools like Novaview and using Powerpivot as a data-source, the managed quotient can be reduced to a fair extent. I would not stress on which kind of enterprises would benefit from this recipe of solution, but I am sure that there are enterprises who would want to start slowly and steadily before they hire a full fledged IT Staff or a Solution providing vendor to design a huge enterprise class analytical solution using tools like SSAS and Sharepoint 2010 Enterprise Edition. Those enterprises should give a thought on this recipe.

Configuring Kerberos with Sharepoint 2010 for Business Intelligence related features

One characteristic that I have different from many other famous bloggers is that I am very miser in appreciating something, and I do not appreciate anything on just the face value. SQL Server is not behind any other competitive products, but still there's a lot to take away and learn from other products too. I find many bloggers posting on their blogs how they are in love with SQL Server and each and every aspect of it. Even my bread and butter depends on MS BI and Sharepoint BI stack of technologies, but when you really love a product, you focus your energies to make it a world class product. Though you may not be on the marketing / sales / development team of the product, but you start feeling like one when your goals are aligned. In my views, though you may not be a famous blogger who would be getting millions of hits, hundreds or thousands of followers, still if you are making even little contribution to nurture the product that earns your bread and butter, I feel that you or I would rather say WE are as valuable as others. In summary, limit singing songs of praise brainlessly and treat your bread winning product as a businessman would treat his business.

Coming to the subject of this post, Microsoft has released a whitepaper titled "Configuring Kerberos Authentication for Microsoft Sharepoint 2010 Products". Sharepoint 2010 has changed the face of Business Intelligence by the addition of Performancepoint Services and Visio Services, along with Excel Services. Identity delegation and/or Impersonation is one of the issues that can block the entire access and execution mechanism of BI solution, if not configured properly.

With Microsoft, keeping up the pace is really hard whether it's with the ongoing releases of newer version like .NET or keeping oneself updated with tons of new information that gets released in the form of whitepapers. This whitepaper contains lot of How To topics related to Sharepoint and Kerberos, which also directly impacts BI solution design. I always fear that how would I deal with a project where entire set of BI products needs to be configured with Kerberos and for times like that, reference material like this is extremely useful.

Below mentioned are some of the general and BI scenarios covered in this whitepaper:

Scenario 1: Core Configuration
Scenario 2: Kerberos Authentication for SQL OLTP
Scenario 3: Identity Delegation for SQL Analysis Services
Scenario 4: Identity Delegation for SQL Reporting Services
Scenario 5: Identity Delegation for Excel Services
Scenario 6: Identity Delegation for Power Pivot for SharePoint
Scenario 7: Identity Delegation for Visio Services
Scenario 8: Identity Delegation for PerformancePoint Services
Scenario 9: Identity Delegation for Business Connectivity Services

You can download this whitepaper from here.

Performancepoint Services 2010 architecture diagram

A picture is worth a thousand words, or in another words you can say that I am a lazy person. Instead of stretching ten paragraphs to describe PPS architecture, I thought of sharing this very informative diagram from BOL which describes Performancepoint Services 2010 architecture in a much better way than I can describe in words. Less words and more information, just perfect for my taste !!