How Microsoft Excel Changed The World

Microsoft discontinued PerformancePoint Planning, and the roadmap looks a bit blurry for Analysis Services.  Data Mining is going to the cloud, and Reporting Services will most likely be integrated permanently into Sharepoint.  Sharepoint could go to the cloud, leaving Excel on the desktop (or in the cloud) to take over the full suite of BI responsibilities at Microsoft.

An interesting article about the humble beginnings of Excel, the world’s most ubiquitous BI tool, on its 25th Anniversary.

As for Excel, Klunder still considers it a powerful tool. But he harbors no illusions about how it can be abused. “It’s the same thing with PowerPoint—Excel lets things look professional, and people assume there’s substance behind it.”

How Microsoft Excel Changed The World

And my favourite uses of Excel:

http://www.eeggs.com/items/8240.html

http://www.calculatedriskblog.com/2008/11/happy-thanksgiving.html

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

For those of you using float expecting precise decimal values from SQL Server when performing aggregate functions or multiplication/division, don’t. 

http://en.wikipedia.org/wiki/Fixed-point_arithmetic

http://en.wikipedia.org/wiki/Computer_numbering_formats

One recommendation for storing monetary values is to use the Money data type. 

http://sqlcat.com/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx

Apparently this helps with performance during Analysis Services data loads, if currency type is selected in Analysis Services to match the data types.  (getting rid of implicit conversion to double)

However, if you need to do any kind of basic math or rounding, money is not a good fit.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a544f732b84257dd/8926f7fa31fa4832?hl=en&lnk=st&q&pli=1

Use decimal with the precision you need to get a correct answer, or use float and always round your results.

http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx

Note that PerformancePoint Planning and cube writeback uses float data type, which can cause rounding and comparison issues. 

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

Configuring extranet access for PerformancePoint Services 2010 - To the SharePoint - Site Home - TechNet Blogs

Configuring Internet access (and mobile/PDA) to PerformancePoint whitepaper is available.

A new white paper, Configuring extranet access for PerformancePoint Services 2010, describes architectural considerations for deploying PerformancePoint Services in an extranet environment. The paper provides a detailed walkthrough of the recommended architecture that uses Forefront Threat Management Gateway (TMG) server as a reverse-proxy server, the Kerberos protocol for authentication of service and user accounts, and Microsoft SQL Server and SQL Server Analysis Services as back-end data systems.

Configuring extranet access for PerformancePoint Services 2010 - To the SharePoint - Site Home - TechNet Blogs

IBM Press room - 2010-10-21 IBM Acquires Clarity Systems - United States

Coming close to a single source of total BI tools, IBM takes over a mid-sized financial planning and reporting technology firm in Toronto.

Clarity Systems delivers financial governance software that enables organizations to automate the process of collecting, preparing, certifying and controlling financial statements for electronic filing, in support of mandates by the SEC and other financial regulatory agencies. To significantly reduce the risks of potential error and the lengthy times required to create and file financial documents, Clarity Systems software allows finance professionals to seamlessly integrate information for more efficient planning, consolidation and financial reporting.

IBM Press room - 2010-10-21 IBM Acquires Clarity Systems - United States

Forced Parameterization: A Turbo Button?

 

Forced Parameterization: A Turbo Button?

I never had the need to turn on the PARAMETERIZATION FORCED database option until this week. We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused. This practice of using parameterized stored procedure calls, together with attention to detail in query and index tuning, allows us to comfortably handle several thousand requests per second on commodity hardware without taking special measures.

Forced Parameterization: A Turbo Button?

AMO Analysis Services Create KPI

How to create a KPI from command-line/Powershell.

AMO Analysis Services Create KPI

First PerformancePoint Services 2010 Project Recap « Alan Whitehouse’s Ramblings

 
Great article on some of the trials and tribulations of installing and designing PPS 2010 dashboard solutions.

Project Background
We recently completed Phase 1 of a PerformancePoint 2010 deployment for one of the largest insurance companies in Canada. In 2009, TGO Consulting engaged with them to deploy Dynamics GP as their new ERP system. Towards the middle of November, we heard through the grapevine that the client was also looking for a business intelligence solution and had already narrowed the list down to two well-known players in the industry – neither of which was Microsoft.

First PerformancePoint Services 2010 Project Recap « Alan Whitehouse’s Ramblings

Data Mining, Predictive Modeling, Techniques

Though not specific to PerformancePoint or even SQL 2008 data mining, Statistica has an excellent and comprehensive site about Data Mining.

Data Mining is an analytic process designed to explore data (usually large amounts of data - typically business or market related) in search of consistent patterns and/or systematic relationships between variables, and then to validate the findings by applying the detected patterns to new subsets of data. The ultimate goal of data mining is prediction - and predictive data mining is the most common type of data mining and one that has the most direct business applications. The process of data mining consists of three stages: (1) the initial exploration, (2) model building or pattern identification with validation/verification, and (3) deployment (i.e., the application of the model to new data in order to generate predictions).

Data Mining, Predictive Modeling, Techniques

Dinesh's Blog :::: Being Compiled ::::

Great how-to for PPS 2010 Sharepoint dashboards

We have been creating reports or dashboards with different types of technologies including PerformancePoint 2007 for seeing the insight of the business. Those who have used PerformancePoint 2007 know how it supports creating dashboards with various components like scorecards and reports. Now it has been further enhanced and fully integrated with SharePoint 2010. The PerformancePoint 2010 allows us to create rich, context-driven dashboards that show the business in every angle.

This post discusses the way of configuring the PeformancePoint 2010. Let’s try to discuss the way in step by step with relevant images but limiting, as it makes the post lengthy. This post does not discuss about configuring SharePoint 2010. If need, have a look on below posts;
http://dinesql.blogspot.com/2010/03/configuring-windows-2008-r2-for.html
http://dinesql.blogspot.com/2010/04/complete-farm-sharepoint-2010.html

Dinesh's Blog :::: Being Compiled ::::

XML/A Thin Miner

XML/A Thin Miner is a tool that generates and executes DMX and XML/A for data mining queries.

XML/A Thin Miner

Excel 2007 – CubeValue Reporting and Excel Services

Within PerformancePoint, and for cubes in general, Excel is usually the reporting tool of choice, or the tool being replaced by Monitoring scorecards.  The pivot table in Excel can only get you so far in terms of set-based reporting.  Sometimes free-form reports are required.

These functions provide this ability in Excel.  

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

Cube functions

CUBEKPIMEMBER function

CUBEMEMBER function

CUBEMEMBERPROPERTY function

CUBERANKEDMEMBER function

CUBESET function

CUBESETCOUNT function

CUBEVALUE function

Plan Excel reports against OLAP cubes

Updated: 2009-02-26

In this article:

• About building Excel reports against OLAP cubes

• OLAP functions

• OLAP formulas

http://technet.microsoft.com/en-us/library/cc263194(office.12).aspx

Once the reports are built, they need to be consumed.  Other than email distribution or publishing to a document library, Excel Services would be the preferred display method for companies using Sharepoint 2007 Portal Server Enterprise. (MOSS)

There are some whitepapers for Configuring Excel Services to support this functionality.

http://technet.microsoft.com/en-us/library/cc263430(office.12).aspx

White paper: Excel Services step-by-step guides

Updated: 2007-03-08

These guides help you learn about Excel Services in Microsoft Office SharePoint Server 2007 functionality and understand the relationships among various components of Microsoft Office SharePoint Server 2007. The procedures in these guides take you step by step through the process of configuring and using Excel Services to build fully functional PivotTable reports, charts, and a variety of other reports. You can also learn how to restrict access to spreadsheet data and connect to an external data source.

Download the following guides as Microsoft Word documents (.doc).

• Setting up the Server - Step 1: Creating a Trusted Location with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84833&clcid=0x409)

• Setting up the Server - Step 2: Configuring Single Sign-On for Excel Services (http://go.microsoft.com/fwlink/?LinkId=84834&clcid=0x409)

• Setting up the Server - Step 3: Creating a Trusted Data Connection Library with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84836&clcid=0x409)

• Building a Report Based on Cube Functions for Excel Web Access (http://go.microsoft.com/fwlink/?LinkId=84828&clcid=0x409)

• Protecting Workbook Intellectual Property (IP) with View Item Rights (http://go.microsoft.com/fwlink/?LinkId=84832&clcid=0x409)

• Using OLAP Formulas, Conditional Formatting, and Charts (http://go.microsoft.com/fwlink/?LinkId=84838&clcid=0x409)

• Building an OLAP PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84830&clcid=0x409)

• Building a Relational PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84827&clcid=0x409)

• Building a Report with a Chart for Excel Web Access Based on Cube Functions (http://go.microsoft.com/fwlink/?LinkId=84829&clcid=0x409)

• Creating a Multi-Report Summary Page of Reseller Sales Data (http://go.microsoft.com/fwlink/?LinkId=84831&clcid=0x409)

Excel 2007 - Cube

These Excel Services reports can be consumed as PerformancePoint Monitoring data sources.

http://blogs.msdn.com/b/performancepoint/archive/2007/07/12/how-to-use-excel-services-as-a-data-source.aspx

They can be referenced directly within PerformancePoint Dashboard Designer.

http://office.microsoft.com/en-gb/performancepoint/HA102524991033.aspx

SSIS Package Manager - PacMan

A series of tools for ETL projects, including one that performs mass updates called PacMan.

http://ssisctc.codeplex.com/

http://bixpress.codeplex.com/

http://sqlservermddestudio.codeplex.com/

http://pacman.codeplex.com/

http://powershellscripttask.codeplex.com/

http://ssisunit.codeplex.com/

And the best of them all, for ETL and adds the missing components for BIDS.

http://bidshelper.codeplex.com/

Todd's World: Why Isn’t Predictive Analytics a Big Thing?

Answer?  Because it only works until it doesn’t.

Why Isn’t Predictive Analytics a Big Thing?

I’m baffled as to why Predictive Analytics still has the status of a fringe, niche, and advanced realm of the Microsoft Business Intelligence world. Business Intelligence without Predictive Analytics is like a bus taking you most of the way home from work, but dropping you off on the main drag leaving you with a two mile walk. Those two miles are doable and it is great exercise, but what if you’re short on time, ill or injured, it’s pouring rain, or it’s full of dangerous drivers? The bus may have taken you 90% of the way home in terms of miles, but that last 10% of miles is really half the journey in terms of time and effort.

Todd's World: Why Isn’t Predictive Analytics a Big Thing?

InformIT: Safari Books Online: Microsoft Office PerformancePoint Services 2010 Unleashed

Have not posted here in awhile.  Here is an upcoming book on PerformancePoint Services 2010 along with a “rough cuts” version that’s available now, for those early adopters.

The full version.

http://www.amazon.com/Microsoft-SharePoint-PerformancePoint-Services-Unleashed/dp/0672330946/ref=sr_1_1?ie=UTF8&s=books&qid=1272306230&sr=1-1

There is a rough cuts version of the 2010 book that is available, partially online.

http://www.informit.com/store/product.aspx?isbn=0789740753

http://my.safaribooksonline.com/9780789740755?portal=informit

InformIT: Safari Books Online: Microsoft Office PerformancePoint Services 2010 Unleashed

Download details: 2010 IW Demo VM (Beta)

Considering an upgrade?  If you’re planning on using PerformancePoint 2010 or Sharepoint 2010 in a local development environment, you may want to consider beefing up your system and getting into the Solid State Drive world.

• 2 or more internal SSD drives.
• 8+ GB of RAM

Sounds like an HP Envy to me…

System Requirements

• Supported Operating Systems: Windows Server 2008 R2; Windows Server 2008 R2 Enterprise; Windows Server 2008 R2 Standard

In order to run this demo you will need the following: - Windows Server 2008 R2 with the Hyper-V role enabled. - Drive Formatting: NTFS - Processor: Intel VT or AMD-V capable - RAM: 8 GB or more recommended - Hard disk space required for install: 50 GB

Download details: 2010 IW Demo VM (Beta)