While working on analysis services found certain good Excel add-ins available freely. Though some of the add-ins are not useful to us as we already have long established smart programs to accomplish much more than these add-ins do , I indicate the same here for those who may wish to use such approach for their excel integration with their database ( whatever it be ).
Add-in for SQL server 2005 Analysis services /cubes
Using a wizard-driven interface, users can select data from Oracle OLAP simply by choosing from a list of values or by creating advanced selections, such as those based on exceptions, top/bottom analysis, or hierarchies. In addition, users can create Oracle OLAP-based calculations using a wizard. The built-in Query Wizard and Calculation Wizard are similar to the wizards used in other OracleBI products, such as Discoverer, thus ensuring a consistent interface.
- Easy-to-use front end in which users create queries using business terms.
- User interface that is shared with other Oracle Business Intelligence products.
- User access to the latest data and to a shared data model.
- Scalability because queries run in the Oracle database.
DownloadLive DemoWhitepaper on Excel Add-in
- Freeware Excel Add-In - Simplify data retrieval from SQL Server, Oracle, Sybase, MY SQL,DB2 and any other ODBC compliant database
- Works with Excel XP, 2003 and 2007
Add-ins from Oracle and SQLExcel have almost similar capability of query constructer for RDBMS, grouping , ordering to that of MS Excel’s native data import capability. But the interfaces are quite different and at times quite better in certain circumstances. One can use whatever one finds suitable to one .
Relevance of Excel add-in in Overall schema
Important part of these add-ins is that users can build a repository of SQL queries on a secure network share which can be re-usable among the legitimate authorized users .
While the simple-source-based-ETL-reports-like-these-addins are important and very much useful, this is not everything to enhance system vigilance and overall organization agility. One needs many more things like exception triggering , report scheduling , complex-source-ETL-requiring lot of scripting , knowledge and importance of one’s business KPIs / KRAs (ie dimensions / measures) . Complex-source-ETL like Fund-Flow statement OR Calender period wise Trial balance with period change OR customer-supplier financial analysis require:
- Good knowledge of some scripting language like Baan 3GL, .Net , Java , VBA or combination thereof to combine their individual strengths . Empirical wisdom suggest that combination scripting languages with complementary capabilities and with no further requirement to integrate other loose ends gives best results rather than just relying on just one scripting language.
- Good knowledge of semantic layer (data dictionary) prevalent in OLTP like Baan
- Functional relevance of such reports to users ie knowledge and importance of one’s business KPIs / KRAs (ie dimensions / measures).
If one along with one’s IT department and key users does not possess these and are unlikely to possess in near future, one should look out for various vendors who deliver ready reports. If they(ready reports) are easy to use , install and learn and one does not miss any critical reports and one is not charged for all reports one does not need and one is not charged exorbitantly for teaching one’s own KPIs and KRAs (and in some cases existing OLTP semantic layer also) to vendors and one is comfortable with added complexity of a new semantic layer of the reporting tool in addition to above 3 key requirements for new ongoing requirement and the solution is very cost effective, one must definitely go for such ready reports . Before going through any BI evaluation exercise one must not ignore the most important for BI solution ie key requirements and key challenges of key people. BI evaluation and negotiations must concentrate more on factors like functionality of the BI tool, its relevance to business as a whole in current setup rather than few miniscule individual problems being faced by the organization currently, effort &skill required to create new reports, possibility and ability to extend the same to large operational people at least time & money and last but not the least good fit to existing IT setup along with least capex and opex. Normally large number operational people who really require good analytics on daily bases are really ignored.
Happy mining the data in spreadsheet and your choice of reporting tool.