Technology Trends

I see the following as the next emerging technologies; I will be seriously watching:

Cloud computing

Business Intelligence/Analytics

Mobile Application Development Trends (including Android & Iphone)

Software as Service Models.

Apart from these, I am personally interested in learning/developing Silverlight interface and MVC dependent web applications.

Gartner’s Top 10 Strategic Technologies


Large Scale EDW Best Practices

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Extracting Data from SSAS in SSIS

Scenario: I have a requirement to design a SSRS report merging both data from a SSAS cube and from a relational datamart.

Instead of talking about the step by step solution for the above scenario, I just write about the challenges I faced. (Applicable only to SQL Server 2005)

1) Setting up the cube OLAP Server as a linked server in relational database posed lots of problems.
Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “MSOLAP” for linked server.
Recommended solution:
   1. Start –> Run –> Dcomcnfg
   2. Component services –> My computer –> DCOM config –> MSDAINITIALIZE
   3. Right click on MSDAINITIALIZE –> properties –>security
   4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under   “launch and activation permission”, “Access permission” and “Configuration permission”.
   5. Give full rights.

2) When designing a SSIS package to extract the data from the cube, the DataFlowTask with OLE  DB -Analysis Services Provider, always failed. Further investigation (spent lots of time) revealed the following fix from Microsoft:
Change the Provider to Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0, – Click the Connection Properties tab:
You need to tell the Connection Manager to return the query in tabular format, so in the Extended Properties text box enter Format=Tabular

 3) Then, the SSAS datatype and the SSIS datatype didnot match – failed to convert from unicode to non-unicode. The following article addresses the problem.

4) Finally the MDX query had to finetuned to retrived only the relevant result (Caption, Name, All Members) – Good Understanding of MDX will be very beneficial when trying to use ssas data inside a SSIS package.

Four ways to generate a report off of SSAS:!CD3E77E793DF6178!332.entry

SSIS Deployment Utility

Just created an utility SSIS Solution to export all the SSIS packages from SQL Server Integration Services msdb to file based location and to import all the file based packages to msdb.

  • It can be used to port packages from one server to another.
  • It includes the packages organized in folder structures.

Is Microsoft coming up with its own utility to deploy multiple packages? Anything available on Microsoft SQL Server 2008?

Business Intelligence & Performance Management

Business Intelligence delivers dashboards

Performance Management delivers scorecards.

Business Case for Business Intelligence

Business Intelligence is mostly considered as a luxury to have and not a necessity.  BI is not just one another IT Project.  Business Intelligence System enables analytical thinking and better decision making.  How do you know that you need Business Intelligence? What is the business case for BI? As a company, what should a decision maker look for?

The following check list should help:
1) Do you have any periodical reporting system?
2) How do you make a decision?
3) Where do you get your numbers?
4) Is there one integrated view of all the reports from the departments?
5) Have you provided the decision makers all the data and the tools necessary?
6) Were you able to pin point exactly the cause for any particular event noteworthy like a big order, huge loss.?
7) Is there a consistent system representing the facts, delivering one version of the truth?

BI Tools

1. Oracle Enterprise BI Server
2. Business Objects Enterprise
3. SAP NetWeaver BI
4. SAS Enterprise BI Server – SAS Institute
5. TM/1 & Executive Viewer – (now IBM)
6. BizzScore Suite – EFM Software
7. WebFocus –  Information Builders
8. Excel, Performance Point, Analysis Server – Version 2007/2005 – Microsoft
9. QlikView – QlikTech
10. Microstrategy  – Microstrategy
11. Hyperion System –  Hyperion (now Oracle)
12. Actuate
13. Cognos  (IBM)