Recent content by Hans63

Yes, Access can very well be used for a metadata database with its own set of reports. I have done it in the past as well.

Does anyone out there have experience (good or bad) in building a datawarehouse on AS400 and DB2? Any relevant links will be appreciated as well. We have to make a decision for the infrastructure of our datawarehouse. Choices are AS400 + DB2 or Microsoft SQL server. We are using Informatice...

Yes, many people feel unfomrotable with values for 'Unknown'. They probably don't know how to handle this :-) , what it means in business terminology. I think that you should indeed load all facts.

If I read your post right you will create a piece of code that will in essence perform a full outer join, to identify which products are the same and which products do not have a match. Is it possible that one of you products will match several products from your acquisition of vice versa? If...

If there are lots of types of prices, I would opt for a new dimension price_type. And add another dimension key to the fact. Your business is the one that decides how often a Unitprice changes. It is up to the designer to handle it as a (slowly) changing dimension or as a fact. It is all in the...

From what you describe I would opt for a fact table. But it is depended on what the business requirements are. How long do you need to access the history of the prices. How often do they change. Are the prices free (they are not restricted to a certain price range), etc.

I would not discard the idea of putting the facts together in 1 fact table. As they represent movements of patienst relative to the hospital. In that sense arrivals, departures and transfers ARE the same and should be collected in a single fact table. And yes, that means that the non-transfers...

I say that it is dependent on the specific case. If you have many long stay patients, my suggestion works very fine. If you manly have short stay patients, the solution from riverguy and MDX-er works best. There is not a solution that is the best for all situations. Look at all given approaches...

I say your fact table is fine. why can't you join it to a time dimension? As long as you fill (or convert) the empty departures with a special date (far in the future), you can join by specifying: arrival_date <= last day of month and departure_date >= first day of month These last and first...

When reporting with SQL I would look into the CASE statement. The precise syntax for MySQL i don't know. this is the Oracle syntax: select credit_code , sum(case when date='jan2008' then value else 0 end) as jan2008, , sum(case when date='jan2009' then value...

Functionally they are identical. Depending on the implementation (the RDBMS and version) the one or the other may be a trfile quicker.

That is one way. Another way is to store all descriptions in an other table with a composite key: (text_ID, language). All text fields are stored as an ID. It creates a form of snowflake but you are very flexible in storing all different languages.

Isn't this fact table actually a way to split of the changing aspect of your product dimension? And normalized at that. Why do you want it this way? Is it due to the size and the change-rate of the dimension? Or do you specifically want to check the changes? The last can be done by adding an...

The fact table you describe will probably get way bigger then the loan dimension, unless you somehow manage to reduce the number of records. e.g. by holding only 1 year of history. But that will give you 12 * 20 million = 240 million records in the fact table. (this is the space part of the...

I wonder hwo you can run a report on just a fact table. You don't use a date dimension? Are you using surrogate keys in teh fact table, or are you using the keys from teh source system? Design wise I would say, yes, it is a bad idea to duplicate these fields. Consider the effects of a change of...