Using Excel as a front end to SQL2005 Analysis Services (part 4)
Why use Excel….
In January 2006 I ordered SQL 2005 and set off on my first real “business intelligence/ data warehouse†project. One customer, that I have a good relationship with, wanted to understand their sales data better and was willing to contribute to development costs as long as I came up with something fast. I promised I would have a system by April and ploughed in. February and March were tough months and I worked most weekends and often 14 to 16 hour days to get there but we went live in April.
Well I guess the big question is why Excel and then why SQL Server 2005 ?
The simple answer is, that is what I knew. I have used Excel for many years and had recently learnt VBA at the back end of 2005. Looking at the data of the company I realised I need to handle between 5 and 10 million sales transactions and, after reading around, SQL 2005 seem to fit. It looked like it would be the most compatible with Excel at the front end and give me plenty of scope for further development.
It is few months on now so, would I still use Excel ? I will let you decide but here are some of the pros and cons from my point of view
Pros
Speed of implementation – you can link a few pivot tables and charts to an AS cube in minutes
Experience – you do not need a programmer to write a spreadsheet
All Microsoft – Excel links directly with SQL2005
Direction – Excel 2007 will be even better and more integrated with SQL 2005. MS is putting their money behind Excel as a BI tool
Excel’s pivot tables still appear the most flexible way to display data
Cons
Security – Excel is very weak but you do not need to store any data in the Excel “clientâ€. Using Windows
Authentification – SQL 2005 takes over the security side.
Image – many think Excel is not up to the job.
Direction – I am not sure if ASP.NET would be a better platform
It is still a client even though it is on every desk. I would prefer a browser but find it difficult to beat the Excel Pivot Table.
What do you think ?
Carl
Carl,
In my opinion there is not a choice between Excel and other ‘presentation layers’.
We can choose between Excel, Web, Report Services etc depending on
- target audience
- amount of info and other important info aspects.
- if a value added process is necessary before viewing it for the target audience.
- the degree of confidential.
In this context I don’t make any difference between the client version of Excel and the Server based upcoming version of Excel.
I believe we need to be more focused on what the task we want to achieve. This should be based of an understanding of what different platforms offers, knowledge of the above aspects, time table and how much money customers want to spend.
One of the most challenging scenarios is when we work with companies that use *one* tool for everyting. A ‘hammer’ tend to be used for other tasks then just ‘hammering’.
Kind regards,
Dennis
Dennis,
I think your correct. We need to use the right tools for the job. As you say that means we need to understand what all the platforms offer. I have quite a bit of work to go on that one.
Regards
Carl
I think the questions goes to back end and front ends?
There are lots and lots of things to put in the pot, but one of the most often quoted things with office developments is the users familiarity with the interface. I think this is particularly good for BI, when maybe a lot of users will be doing further analysis of the data.
Web is great for viewing, but maybe(?) less powerful
I think you where very brave to take on this sort of project only have just lent VBA! well done!