BI on SharePoint

Getting reports onto SharePoint is an important step in centralizing knowledge and increasing awareness of a company's available Business Intelligence. Unfortunately, people shy away from taking this step because it seems like something that would require a lot of time, effort and expense. This two part series will illustrate the process of putting BI on SharePoint, remove some of the mystery around it and hopefully encourage further investigation.  Part one will cover the setup and part two will illustrate working with SSRS reports within SharePoint.

On Saturday, November 5, 2011, SQL Saturday #96 will take place in Chevy Chase, MD. SQL Saturdays are free, all-day training events geared for professionals who use Microsoft’s SQL Server. SQL Saturdays occur all over the world. Each event is split into many sessions with topics ranging from technical tricks to professional tips, best practices to upcoming features. The most amazing part of any SQL Saturday is the organizers and presenters are all volunteers.

Earlier this week I participated in a discussion thread on LinkedIn regarding the usage of the WCF SQL Adapter in BizTalk Server and I wanted to summarize some of my thoughts and the recommended best practices regarding this particular scenario.

Basic Requirement

Using the WCF SQL Adapter in BizTalk, we need to issue a select statement against multiple tables in SQL Server through the use of INNER JOINs.

I was recently tasked with diagnosing a slow performing report in Microsoft’s SQL Server Reporting Services (SSRS).  The report was intended as a dashboard with multiple charts and tables showing various trending business performance metrics.  Conceptually, the report was sound.  The charts and tables showed exactly what the business users required.  The problem was the dashboard rendered itself slower than the business desired.

My investigation showed the single dashboard viewed by users was actually multiple subreports rendered together as a single SSRS report.

SSRS supports the inclusion of subreports in any SSRS report; a subreport is simply an independent SSRS report embedded into another SSRS report.  In Visual Studio BIDS, you simply drag the Subreport component from the SSRS Toolbox to the Design tab and complete the Subreport Properties.  Using a subreport is easy.

Just one of the reasons I like working with CapTech is our agnostic approach to technologies.  Our Data Management and Business Intelligence Practice Area includes many consultants certified with their favorite technologies.  At our internal meetings, we hear some variation of “my BI technology is better than your BI technology.”  Whether the topic is database platforms, data integration tools or data presentation capabilities, well-informed suggestions are freely shared only to be countered by alternative viewpoints.  That sort of friendly competition keeps us on our toes.

Over the years, I personally have had the good fortune to work with many database, ETL, and presentation tools.  Each of those tools impressed me in some fashion and disappointed me in some other way.  No tool was perfect.  Most importantly, each tool was at the mercy of the design attempting to leverage the tool.

Lately I have focused on Microsoft's Busines

A colleague introduced the term “convenience view” to me and that term resonated with me ever since.  A convenience view is one of those database objects intended to make life easier for people to access data without actually understanding the nuances and relationships of those data.  Convenience views frequently join multiple tables together so data users will not need to code or optimize those joins.  Convenience views may also include business logic which transforms data so end users will not need to code or argue those transformations.  The concept seems noble enough.  Who opposes simple data access, optimized joins and centralized business rules?  Just like that store that sells everything right off the interstate, convenience comes at a cost.

One obvious cost is all of those optimized joins.  Sure each individual join may be optimized, but the database engine has to collectively consider all available join options before an execution

When it comes to metaprogramming, even the most technical folks can find their eyes rolling back into their heads.  I’ll admit, with all of the fun and new technologies out there, a discussion about metaprogramming is, in the name of brutal honesty, just outright boring…  but, so is being addicted to brute force.

During a recent ETL implementation using T-SQL, changes in architecture and scope began to threaten the project’s delivery dates. I desperately wanted to begin development but feared throw away work resulting from requirement changes coming out of ongoing design and analysis discussions. How do you develop code without a finalized set of requirements and prevent re-writing each piece of code when requirements change?

In an effort to maintain control over the proliferation of application servers and data, many organizations have undertaken projects to consolidate and integrate systems.  One of the first hurdles in undertaking an integration project is determining the most efficient integration technology.  Unfortunately, just within the Microsoft stack, there are technologies that consistently cause confusion and are often misused.  For example, the lines are continuing to blur when comparing BizTalk Server to Windows Workflow especially now that hosting Workflows using Windows Server AppFabric can provide functionality that appears similar to BizTalk.  However, there are still quite a few giveaways when you need one versus the other.  Underneath most applications, we commonly find a data platform and for many organizations, their platform of choice is SQL Server.  The intersection of just these 3 technologies leads many to ask the question, "if I want to integrate System A with System B, which technology should I use?"  Many times there are no hard and fast rules, but with some careful thought and consideration of organization demands and technological capabilities, the fog will lift and a clear answer will emerge.  This post will by no means provide the definitive answer, but I will provide a few common scenarios that might help illuminate the path.

This write up contains a high level investigation of the Business Intelligence solution offering from Microsoft (SQL Server Reporting Services or SSRS) and the offering from SAP, the Business Objects base reporting package (BOBJ).  While BOBJ does have more options for reporting and presentation, from a basic report feature standpoint both of the tools offer similar functionality and offer the user a great deal of flexibility in the presentation of their data.  The other difference between the two solutions that needs to be considered is the expense associated with the Total Cost of Ownership.  While you will have similar costs in the requirements gathering, design, development, testing, and ongoing administration, there is a significant difference in the licensing cost of these products.  While BOBJ charges by either named user or CPU, SSRS comes with SQL Server so there are no additional costs with adding a BI tool set.

SQL Server and Sharepoint offer a quality BI solution, which meets basic architectural principles and business requirements.  Because of Microsoft’s desire to establish itself in the BI space, it offers the BI components with a license to SQL Server.  The lack of flashy, AJAX style reporting features (which are often shown in demos of BOBJ) may limit the business’s interest in SQL Server.  Additionally, BOBJ’s reporting, ad hoc queries, dashboard, data visualization capabilities are key strengths of the SAP BOBJ product suite and are among top rated BI tools.

When considering the total cost of ownership, a company must consider the individual components that make up this expense.  Total Cost of Ownership (TCO) comes from the High Level Business Requirements, Software Selection Process, Software Installation, Detailed Requirements, Design, Development, System and User Acceptance Testing, Production Software Licenses, the ongoing Maintenance of the solution.   While many of these costs would be similar across the two platforms, a company needs to assess the differences in development time, and ongoing maintenance and understand which tool their personnel and IT infrastructure can support.  Specific costs and return on investments are highly dependent on company’s specific situations and deployment choices.  From our specific client exposure, mid-market companies do not opt for BOBJ, and we find that SQL Server is more prevalent.  An SSRS solution will often be lower cost from a licensing perspective as all components are included with a SQL Server license.  However, SSRS requires a developer to build their reports, where BOBJ supports an end business user self-service model.  So long term technical support and development costs could actually be lower with BOBJ.

Because many companies already own SQL Server licenses within their infrastructure, the ease and low cost benefits of implementation may be too good to pass up.  However, companies either without SQL Server in house or requiring heavily visual reports accessible to business users or self-service access to information with minimal IT support may want to implement BOBJ as their BI stack.

Searching a SQL Server database’s sys.comments table has long been a quick way to find stored procedure usage of another database, table, or column.  Likewise, clicking “view dependencies” on a table is a quick way to find the opposite: which stored procedures (on the local db) refer to that table.  I recently came across a scenario where neither met my needs, so I had to expand the capabilities of the former.  The limitation of the first approach is parsing the text field to find multiple items.  CHARINDEX and SUBSTRING functions only get you to the first, and looping can be incorrect as line breaks may occur haphazardly mid-text.