Opinions wanted on Proposed Platform for New DW

[pompeyjon]

Hi all,

I would welcome any and all suggestions on the following topic. I have been commissioned to design a Data Warehouse from scratch for our organisation.

The particulars of the project are as follows;

1) Annual amount of data approx. 2.5Gb
2) Potentially 50-100 end users
3) Requirement for customisable analysis for end-users with no SQL knowledge
4) Timescale: project must be ready for April 2005
5) Platform is to be decided (probably SQLServer with Access front-end).

Can anyone point me in a good direction for reference works, case-studies, etc.

Thanks in advance,

Jon

 

[blom0344]

My first question would be: What sort of organization are we talking about here?
Do you mean that you are going to use Access for reporting/analysis purposes? You better reconsider. Sure you can use GUI to create queries in Access, but it sure as hell ain't a good tool for queries other than the most straightforward ones. It also is low on other reporting facilities, being a jack-of-all-trades kind of tool.

Probably better to look into reporting tools that practically ship with SQL server.
With that small amounts of data you can of course use any of the big vendors'products with SQL server being more affordably priced.

I am missing the ETL part in your story. How are you going to populate the datawarehouse?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[pabloj]

I think that SQLServer is a good starting point, it delivers:
1. ETL (extraction, transformation and loading) tools (DTS)
2. Database (of course ...)
3. OLAP (MsAnalysis Services)
4. Excel as a frontend for Analysis Services cubes

You should check your requirements for reporting, to see if Analysis Services + Excel are enough, if you need real time reporting on the original OLTP system you'll prefer some more advanced frontend like Business Objects, which can connect to relational and multidimensional (OLAP) datasources.

Probably Oracle 9i is the best db for datawarehousing but it's a bit more difficult to manage.

Stick to your guns

 

[blom0344]

But do not forget that SQL server runs on just one platform, whereas for instance DB2 (I am using) can run on all platforms............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[pabloj]

Yes, that's right, with SQLServer you can't get the advantages of *NIX platfom, but this won't be a problem if your organization doesn't have skilled unix sysadmins.

Stick to your guns

 

[pompeyjon]

Thanks for the responses so far everyone. To add a little more to the discussion.

I work for an NHS (UK National Health Service) organisation. The data I will dealing with is records of patient episodes in hospital. The specifics of the data is not as important as the counts for various clinical specialties and the associated costs to the organisation.

Consequently, interrogation of the raw data happens rarely, and transactions occur infrequently (except in initial data loading).

The organisation only uses Windows-based machines, so no platform problems there.

I don't think I'm looking at hugely complex reporting requirements; the majority will be routine monthly reports. If OLAP allows me to use Excel as a front-end, I think this would satisfy my power-users. I can't foresee any other reporting requirements.

Jon

 

[johnherman]

SQL Server should be more than adequate for 2.5GB annual growth. I am working for a US health insurance company in the Blue Cross / Blue Shield family of companies. We have 2.5TB on SQL Server and grow by 300GB annually.

You mention a timeline of April 2005. If you don't have something up in 6 months, your project will fail. Do your project in an incremental manner. For us, we started with membership and providers, then moved to medical claims, pharmacy claims, other claims, authorizations, and case management. We are not done. We still have data for healthy lifestyles, appeals and denials, and quality incentive programs. By the time we finish them, they'll have more for us, I'm sure.

Brio is a good, user friendly front end. Power users may want something better. If you can contain your user community to one set of access tools, more power to you, but I have found that sometimes two different vendors are needed; also allows you to have them compete on pricing and renewal fees.

Excel 2000 or later, with its pivot table services, will undoubtedly be the lowest cost solution. In addition, if your users already know Excel, the decision could be already made. As I stated, some Power Users may want more than Excel, and you might need to buy a few copies of Business Objects, Microstrategy, Cognos, Brio, etc for those users.

If you want to start a subgroup on Health Services Data Warehousing, I would be glad to participate.

- John

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[kishleo]

Well if you dont have to build the complex reports, why dont you build your reports using ASP. i guess you have your own intranet so that it is very easy to handle reports using ASP since they are not complex. but if you have ad-hoc reporting you cant use ASP.

Kishore