Fundamental Database problem: query alot? or store plenty?

Hexxx

I am doing a moderate datawarehouse implemention with mysql and php.

It is for telelvsion audience measurment research which means every night basically it would have a lot of processing and sorting out information to do for each of the hours the previous day.

During the day, the info sent consists of a viewer_id, station and time information. The viewer_id of course can lead to alot of information in the viewer database which is going to be used for calculating demographic information, in addition to the hour shares and rating for each channel.

The problem I am having is should I use the information and get all the viewer information and save it into my array in a php script and use it from there.
Or should I get information in groups, eg, viewer_id and viewer_sex, and do calculations with that, and then go back and get viewer_id and viewer_occupation, and do calculations with that. Because in ordinary programming we were taught that we shouldn't be doing the same kind of stuff repeatedly, but if I grab all the data I need, I would basically be copying a large portion of the DB into a array in a php page... and would what be the point of that?

What do you all experienced in php and databases recommend?

Roger_Ramjet

Ok, the first thing you need to understand is that a database and a datawarehouse are two very different things. In a database you should be using data-abstraction: storing data independent of it's future use. A datawarehouse, on the other hand, is very dependent on its usage.

ETL (extract/transform/load) is the process by which a datawarehouse is built:

data is extracted from a database (or databases), transformed through aggregation/combination/analysis and then loaded into the warehouse for efficient presentation on demand.

As you can see, the most important part of this is the transformation. It is very dependent on the purpose of the warehouse and is where all the heavy processing will take place. Once the data is loaded into the DW, very little further processing should be required for presentation at run-time. The rational is precisely what you say, we should not be doing the same stuff repeatedly - especially when it is heavy statistical analysis that is going to require a lot of aggregate queries and the like.

So, your design should be to store the incoming data in a fully normalised DB. You will then periodically extract the new data, summarise it for your reporting and then update the DW with the new aggregate data.

One point to remember is that SQL is 10-20 times faster than any programming language, and this goes up if you start using classes and objects as they carry additional processing overheads.

Point two is not to try to do all the transformation in one query. Use queries that perform one or two steps at a time and store the results in intermediate tables: this will be far more efficient in the long run.

Hexxx

where you say

store the incoming data in a fully normalised DB

My project (so far) does not really deal with any user interactions such as users updating records. My database has records on the viewer(viewer_id, dob, sex, occcupation, location) the viewer table as attributes, and the viewer hobbies in a separate table (with viewer_id, hobby_id) as these can be multivalues attributes.

The information that would 'come in' periodically would be in the observations table and would consist of

obs_no(PK), viewer_id (FK to the viewer table), viewer_start_time, viewer_stop_time, viewer_channel_id.

As the first task is to find the hourly share for each station, I had to group all the observations by hours, and then separate the observations for each hour into stations and then into viewer_ids.

I (think ? ) what I am doing is the extract and transform phases, where I am grouping the data into what I would need, and then transforming them using the needed (repetivtive) calculations on the small invidiuals groups.

I have not decided on a proper star schema, as I am not sure how the actual info I calculated would store physically into the fact table, I know there would be a dimension for each thing I am calculating for.

For eg, two of the facts I know I would have:
Shares. Ratings.

Dimensions
Station
ViewerSex (m,f)
Time (hourly, daily)
Household
Occupation

I am currenty calculating the share for each of the dimensions, but haven't actually stored it anywhere cause I don't understand where it should go.

I know their would be another DB which would really be the DW with two fact tables(but what would be their attributes?), would there be five dimension tables, one for each dimension? I haven't seen any indept examples on the star schema, only introductory ones.
Since I have calculated so far 1) the hourly for each station, and 2) the hourly share in terms of males and 3) hourly share females for each of these stations, where exactly in the star schema would these calculated values go?

Roger_Ramjet

Start with what you are going to present in your final analysis - presumably some sort of tabulated data that can be graphed in various ways for presentation. That will determine what you need to store. Then work back to see what intermediate data it would be most efficient to store and design tables to store that. It should design itself as you do your analysis.

tomhath

I would store two fact tables: number of viewers in the audience (i.e. total population), and number viewing. Don't calculate share or rating yet, just the counts.

For dimensions you can have Hour, Station, and Gender. Obviously the first fact (Population) is not related to Station, but each row has a foreign key to Hour and Gender. Viewing is broken out by all three dimensions. Now it's easy to calculate market share and rating by Station, Hour, and/or Gender.

Adding more dimensions means breaking out the counts and adding more foreign keys from the facts tables to the new dimensions.

Roger_Ramjet

Tom, you completely misunderstand the nature and function of a data warehouse. It is not a dynamic database where entries will be updated, it is a repository of historic facts and summary data that does not change.

Once the hour has passed, the audience is the audience and this will never change. We want to know what a viewer's occupation was at that time not what it may have changed to in the future. The same applies to other viewer attributes like family size, marital status, etc. So we do not store FKs in the DW tables, we store the full demographic data as it exists at the specific time in question.

Do all calculations that you possibly can and store them in tables geared to easy display and reporting. That way you process once and read many. After all, the audience share cannot change after the fact so there is no reason to keep on recalculating it.

Think of a DW this way: if it were deliver notes we were storing we would want to know the address that was actually delivered to, not the customer's new address. When it is demographic data we want to know peoples age at the time not their age today.

tomhath

It depends on how you intend to use the data. I don't think he wants an archive of the data or a few canned reports, but rather a way to view it according to many different criteria.

Precalculate what market share? Do you want to see the share for females under the age of 30 for the first week in November between 7pm and 11pm? Maybe I don't care about gender or time of day, but want to know the share for all people employed in retail in November. I don't see how you can precalculate all possible ways an end user might want to slice and dice the data.

That's why (per my understanding of OLAP cubes) you store the facts with links to each dimension the fact is related to. An individual's demographics are not important, only how many viewers there were across each dimension (gender, occupation, time, station, etc). The foreign keys for a fact points to a value within each dimension. E.g. fact: 3 viewers who were [female, 20-30 yrs old, 7pm, retail, first week in November], not the viewers themselves. Now I can find the number of viewers who meet the criteria I'm interested in.

Hexxx

Ok that could be my problem... trying to precalculate every possible combination. I did it for invididuals, households, m/f and still have to do it for occupation, age group and hobbies.

Roger_Ramjet

Ah well now, if you are using OLAP and CUBE technology that is another thing. Which 3rd party tools are you going to use?

It is still the case that DWs run like pigs if the data is not denormalised to a great extent. It is also the case that many of the features of 3rd normal form are also contra-indicated: you want to know the demographics at the time the observation was made, not what the currently are. That was my point about replacing the FK with the columns it would otherwise link to.

Hexxx

I'm not using MOLAP, I'm pretty much trying to code everything myself and store it in facts and dimensions. I was doing the calculations, but just echoing to the screen, as I didn't figure out how to do the facts and dimensions as it.

Are there any OPLAP tools that work with mysql and php? When I did a search all that came up with the word ROLAP was Visual SQL Designer which was a demo.

All I am using is PHP, MySQL, and DreamWeaver. No addidional OLAP applications.

tomhath

You can do it with SQL, but it gets kind of convoluted. The only option I can suggest is putting Mondrian between PHP and the database. You're getting into the Java world though.

http://mondrian.pentaho.org/

Hexxx

I looked at the mondrian site. Saw that they model the multidimensional structure using XML (I am guessing this is just the 'visual part' since XML doesn't really do anything) but I didn't try downloading it as yet, I believed that I would still have to do all the calculations before hand, because the information that needs to be extracted (not something simple like sales figures) needs to be computed over the range of attributes of the viewer and the specific date/time. Have you used Mondrian before? Another thing I read in Kimballs book, is that since you wouldn't be able to calculate every possible thing the user could ask, you should allow them a query tool, but as I am doing this for a website (and don't want them to see the information on an actual viewer, for privacy purposes. I'm sure sure about that end user query part... )

tomhath

I've never used Mondrian, can't offer any advice on how useful it would be to you. About all I can say is that a business intelligence system is typically very, very expensive. I'm not saying it can't be done in php and MySQL, just that you'll probably need to keep things as simple as possible.

Hexxx

Thanks, I'll check it out more when I get a litle further.

Roger_Ramjet

Excel does OLAP Cubes and can be fed from mysql via ODBC: MySQL and ODBC . The same probably goes for Open Office if you don't have ms office. In fact, given that Excel is such a pig, if I had to face the learning curve I'd favour open office myself.

Now there are tools around that will let you embed spreadsheets in web pages so see what is available on source forge for doing so with open office calc. That will give you the full olap functionality and user query tools that you want.