Microsoft Business Intelligence Primer and Tools

The purpose of business intelligence (BI) is to monitor and ultimately control business productivity and health. You can use BI to optimize your organization’s efficiency as well as enhance organizational growth. A classic and popular BI application is to monitor financial health. BI financial applications also let you publish quarterly reports, thereby satisfying financial reporting obligations.

BI provides for more than monitoring, though—it also should enable diagnostics and solutions for problems. Dynamic data discovery (i.e., ad hoc reporting) is the exploration of data to isolate a problem area. Further monitoring will determine the effectiveness of any applied fixes. In this way BI is an experimental science, but repeating this monitor-diagnose-fix cycle should provide an effective solution.

Microsoft Office PerformancePoint Server (PPS) is a high-end platform for BI. PPS is divided into three modules to mirror the lifecycle of BI: monitoring, analytics, and planning.

When the monitoring module sends an alert, the analytical component (recently acquired by Microsoft with its purchase of Proclarity) lets you drill down and delve into the specific cause of the problem. The planning module then attempts to proactively remedy the problem to prevent future incidents.

A major technical architecture of PPS is hierarchical enterprise scorecards capability. This is the capability to drill-down the organization for progressively specified key-performance-indicators to enable full BI capability.

Microsoft BI Tools

Let's look at some of Microsoft's other BI tools.

SQL Server Reporting Services(SSRS)


• licensed with SQL Server 2005 • current platform baseline Disadvantages • no true dynamic reporting; the wonderful cube browser Microsoft Office Web Components (OWC) doesn't enable drag-and-drop pivot tables out of the box; only handles flat two-dimensional reports; a very limited version of cube browsing is available with the Matrix object • no automated drill-downs for dynamic data-discovery, although manual drill-downs can be implemented with hyperlinks • limited analytics (analysis capability)

Interesting Capabilities

• data driven subscriptions: dynamic parameters and recipients
-reports are delivered by email or to a file share on a scheduled basis or on demand -Active Directory can be queried to supply the recipient list dynamically -a list of parameter is configurable for each recipient
• static reports; report snapshots • Report Manager (needed for development intelligence) • ad-hoc reports; not the same as full dynamic reports but can provide a useful simulation • good charting and graphing capabilities

OLAP – Analysis Services

Required for advanced report capability: • the wonderful cube-browser for Office Web Components (OWC); includes drag-and-drop pivot table capability and drill-down into dimensional hierarchies. • support of analytical tools and data mining • enables historical view of data out-of-the box for year-over-year, quarter-by-quarter, or other comparisons • required for high-performance aggregation of very large data sets

Office Applications: Excel, Word, Outlook

• best analytics with Excel • workflow capabilities, for example, each item in a list of compliance breaches could have attached a list of Outlook users required to sign off on a Word document to remediate the breach • existing user-base for power users • full-power for .NET for customization

Windows SharePoint Services 3.0

• license built in to Windows Server 2003 and 2008 • entry to world of Web Parts and third-party components • workflow capability; availability of Windows Workflow Services • portal capability for dashboard • scorecarding with KPIs • foundation of MOSS for upgradability

Microsoft Office SharePoint Server (MOSS)

Advantages • Business Data Catalog (BDC) for ERP integration via XML • Excel Services enable Excel on the Server for one single view shared across the enterprise Disadvantage • too expensive to license

Jay Santry is happily employed by Full Visibility, LLC.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.