(Simplified) Pivot Table Integration

 

(Simplified) Pivot Table Integration

A business user wishing to integrate Excel pivot tables with an existing database implementation, such as Microsoft CRM, will face a number of challenges that may frustrate and intimidate our end users:

• Connection points to the data sources are mysterious and so numerous in choice, that a user often times abandons trying to discover the proper combinations necessary to connect an application to a data source. SQL machine names and network paths are not (and should not be) in the forefront of our users knowledge.
• Discoverability of database table connection points are obfuscated and documentation is not intended for an end user audience. The intent of database design is for data flow optimization not for end user understanding.
• SQL, for all it’s strength and power, is a mystery to our end users and requires a very technical orientation to exploit all of it’s potential. Even those limited users in an organization well versed in SQL will find the mystery of joins in a sophisticated database daunting and unique from one database schema to another.
• A strong business orientation and understanding the significance of underlying data should be the main emphasis required to evaluate and derive good business decisions. Bipolar understanding of business models and database schemas should never be a requirement of our end users.
• Database field names are in English and their meanings hidden behind a short terse word. Our international base and often “single” language set of users will be lost in a database if not insulted by the rather brash assumption that everyone in the world needs to understand English to navigate their data.

(S)PTI bridges the gap between obfuscated databases/servers and Excel pivot tables. (S)PTI can also be used as a model to integrate other application features (such as Word Mail Merge or PowerPoint Tables) with database based applications. The technical connection points and database schemas are automatically created by an (S)PTI compatible application, the only thing our users should be asking is: “What If…”. As an extension, (S)PTI can be used as a dynamic UDM (Unified Dimension Model) generator; dynamic because the user poses business question ahead of generating the views and different in that (S)PTI attempts to reduce the number of available data points to those selected or currently viewed by the user.

Full article linked below!

Microsoft Dynamics CRM Team Blog : (Simplified) Pivot Table Integration