I faced one interview on Saturday and got stuck in 2 questions…
Suppose we have a measure object called Amount and If I open that object and go to Edit, there the projection is Sum and also I am writing query like -
SELECT [b]SUM[/b](FINANCE.AMOUNT)
So what will be effect? Means does it make any sense or how it will behave?
Suppose I have two tables one is Dimension and another one is Fact. Now Dimension table is Product and Fact table is Sales table. Now If some products without prices are added to the database, and I need to find all those products for which Sales Prices is Null, how I will achieve this?
I answered like simply the Sales price for new products will be null, so directly take two objects product name and sales price in the report, and put a Null filter on Sales Price. But I am not sure whether this is wrong or write , could some one please help me in understanding the write answers.
Aniket, It does make sense, the SUM will be applied to the SQL, at run time.
The Sum projection “tells” the report engine, whether Deski or Webi, how to calculate the projection, when placed in a cell or footer, for example.
You have to be careful here :).
If the DW design is poor and Sales price exists in the fact, you may require any outer join, between your dim and sales fact table to give you your Null sales prices. I would also test for the Null in my actual query ratherthan rely on a filter.
When I suggested the design would be poor, I have always had the opinion that Nulls have no place, whatsoever, in a DW.
If there is no price set this could be resolved during the ETL code, to still give a you a dim>fact join.
In the ETL world, you shouldn’t set something that is NULL to 0 because it will change averages (I’ve now got an extra row to divide by…)
First answer, spot on.
Second answer - I am not sure what they are after. You may need to consider that they are hinting at a Type 2 table load or human error. Did they say which table price was held on - is price per deal (i.e. negotiated each time based on volume, loyalty, etc.) or is it per product (i.e. a can of Coke is always 50 pence). You will find interview questions crop up like this where the interviewee is expected to show some analysis skills because the second question cannot be answered with any degree of certainty without further questions.
You are completely correct, I wasn’t suggesting a zero, in this instance.
What would I suggest, that is a difficult question to answer.
Probably having a price should be part of the process / pre-requisite for a new product to be loaded in the first place?
What would be your take on this, particular, scenario, Mark?
, very important, in the view of this particular question, My thoughts were it would be the per deal scenario…
If you don’t know the price and you’re calculating value based on price * quantity, then you’ve got to do something. Correcting it after the event isn’t Kimballesque, so from a purist ETL point, if I cannot find a valid price for a product I’d write it to a suspense table which would be reported on in exception processing/reporting routines in the morning. It could be that this is a new product and its price hasn’t been added to the system yet. I’ve seen that before
If there is no price existing anywhere then you can’t push it in. However, again, we don’t know enough - depends on the deal struck to an extent.
One of the easiest barcodes you could ever remember is 5449000000996. Coke. Full fat, 15 tons of sugar Coke. Scan that and the price is the same. No discounts.
What if this particular deal had these products as a deal break - throw these in and we’ll sign up?
If price is deal-specific, it goes on the fact table because the price varies at that level of granularity. If it’s per product across the piece then it’s place is on the product table. If there’s a null price, there’s a null sales amount. If there’s a sales amount and a quantity, then unit price = sales amount/quantity. Plenty of ways to tackle it, depending upon the business rules of the organisation you’re working in.
I sometimes wonder whether we should have a few more, heading specific, sticky topics.
This one, of yours, above, is just one example, another would calendar tables / current date calcs.
I know we have the FAQs and candidates would have to be chosen carefully, but, maybe, worth a consideration?
(BOB member since 2007-10-05)
(BOB member since 2003-11-27)
, very important, in the view of this particular question, My thoughts were it would be the per deal scenario…
!
(BOB member since 2002-06-20)