Business Intelligence 2

[Mongr1l]

Ok, I'm going need some major big thinking on this one.

Here's the problem: I've on the cusp on convincing my employer to move into Data Warehousing. We currently use MS SQL SERVER 2000.

I know that SQL 2k5 comes with Business Intelligence Platform, but I don't think my boss will is going to want to also spend close to 20k in upgradding our licencses to SQL 2k5.

So two questions:
1.) What does SQL 2000 offer specifically, in terms of Data Warehousing, with respect to the Data Transformation layer and the Reporting Layer?
2.) Can I purchase the same Business Intelligence platform that comes with SQL 2k5 independently?



I am prepared to give *BIG* stars to savy answers.


mongr1l

 

[SQLDenis]

>>What does SQL 2000 offer specifically, in terms of Data Warehousing, with respect to the Data Transformation layer and the Reporting Layer?


Analysis services 2000 (although not as powerful as AS 2005)
It's free with a license of 2000

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

and reporting services 2000 of course (also free with a 2000 license)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Mongr1l]

I am aware that Analysis Services 2000 module comes with SQL 2k.

However, can you expand on Reporting Services?

I don't rememeber that being a selection on the SQL 2000 installation disk.

What is it, exactly? Crystal Reports?

mongr1l

 

[SQLDenis]

It is a separate install

http://www.microsoft.com/sql/prodinfo/previousversions/rs/demo.mspx

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Mongr1l]

Thanks, Denis!


mongr1l

 

[Catadmin]

The reason Reporting Services isn't on the install disk of SQL 2000 is that it came out in 2003, well after SQL 2000. It usually has its own separate disk.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Mongr1l]

Thanks, Cat.

Ok, so my question it this:

Since SQL 2000 has the essential modules to facilitate Data Warehousing, what does the BI platform offer that SQL 2000 doesn't have?

Is it worth me trying to get it?

 

[Catadmin]

BI is a generic term. There are lots and lots of platforms out there that will offer you variations of BI tools. SQL Server Analysis Services offers you the back end tools to put your data together into cubes, stars, warehouses, etc. And you can report on it. What SQL doesn't offer, which is also considered a BI tool is a Dashboard product.

Dashboards are the front end presenation layer gizmos that have gauges, graphs, charts, etc. to make your managers happy. There are good tools and bad tools out there that slice & dice the data almost any way you want it. The caveat is that a lot of these tools don't integrate with SQL Server too well. Take Sybase for instance. It'll take data from SQL Server, but the Sybase people force you to buy THEIR database as part of their Dashboard/BI product and you export the SQL data to their database before you can do the happy fun warehousing/reporting stuff on it.

You can design your own presentation layer and call it a dashboard or call it by a different name. It'll take some effort, which is why a lot of people will be trying to sell you their tools instead. Be very careful, though, in what you do buy. You'll want to know what they charge for support, how difficult it is to integrate with your existing system, how available they are, cost not only of the licenses and new hardware but of the on-going maintenance plans... All that is crucial in planning your BI implementation.

We've just started going into this at my work place. It's driving me nuts the number of people who keep trying to push products on us that we might use 10% of but will cost us a fortune anyway...



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Mongr1l]

Ok... now, for the million dollar question.

Just now, I have been asked to prepare a qualification / requirements document for the person we are looking to hire to convert us to Data Warehouse.


We are thinking of a 3 month contract... perhaps longer, to do this.


What should I be looking for as far as a Data Warehouse specialist?


Obviously, just knowing SQL isn't going to cut it.


mongr1l

 

[SQLDenis]

What should I be looking for as far as a Data Warehouse specialist?

difference between star and snowflake schemas
what is a fact table
what are dimesions
what is a slowly changing dimension
what is MDX
ability to write mdx queries
how would you update Analysis Services every night
what are cubes

google for more of them, this is just on top of my head


however most datawarehouse projects take a lot longer than 3 months

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Mongr1l]

Thanks, Denis.

Those question do cover the Analysis Services part, more specifically the OLAP Cubes portion. Furtunately, I do have some knowledge in that area.

However, what I would be interested in finding out is how a conventional database is converted to a data warehouse.

Aside from the blatanly obvious, "well, I'd start by normalizing!!", I guess what I'm looking for is some definitions of Data Modeling and such..


I'm googling as we speak, but I have some readilly availabe links....



and as for: "however most datawarehouse projects take a lot longer than 3 months"


Fortunately, This part of the comapany (Financial Services) is still relatively small. However, since we are re-orienting on providing analysis services to our clients (something we didn't really do before), we are about to expand in a very short amount of time.


Hence the need for a Data Warehouse.

So I don't really think it should take longer than 3 months... but that's obviously negotiable.

 

[SQLDenis]

>>Aside from the blatanly obvious, "well, I'd start by normalizing!!",

actually you denormalize, since your data warehouse will be optimized for retrieval not updates inserts(OLTP)
You can throw many indexes on it

the less joins the better, few lookups

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Mongr1l]

Ok, now I'm confused.

Denormalized?

I realize that for the Analysis Services Cube, you need a fact tables that basically has every single bit of data required for the cube.

However, I was under the impression that a Data Warehouse normalized everything up to 5th and 6th normal form.... hence the need for Meta Data?

 

[SQLDenis]

No, with millions/billions of rows you can't have 8 join queries this would take tooooooo long to retrieve

You have 1 fact table and a couple of dimesnsion tables

that's why star schema's are preferred over snowflake schema's...they are faster...however sometines you don't have a choice but to go with a snowflake design

here is some info

http://www.dwinfocenter.org

check out

http://www.dwinfocenter.org/fstquery.html

You might want to get Ralph Kimball or Bill Inmon's books for further reading



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Mongr1l]

I think I will need to purchase Dr. Kimbals book before I make any more assumptions.

However, somehow I thought normalized data bases played a major role in all of this...

 

[Catadmin]

Also, you might consider giving said person a test. There are lots of people out there who know theory but know diddly-squat about actual implementation.

Come up with 4 or 5 tables and some basic structure, print off the document and hand it to your interviewee, asking her/him how they would take this information and make a datawarehouse out of it.

It'll be interesting to see if they can actually come up with something other than talking in generic terms. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"