Skip to Content
author's profile photo Former Member
Former Member

Modelling for Report: Hints on this scenario

Hello Experts,

can you help me with this scenario on a data warehouse I am working on with the following report in mind: Here, we use the 1 or 0 as a flag for whether a bid for a product is less the $100, between $100 and $300; and Over $300. Then count the number of bids.

-


Bid-- Less Than 100 -100-300 -over 300

ProductA1 -$200


0
1
--


1

ProductA2 -$60


1
0
--


0

ProductB1 -$450


0
0
--


0

ProductB2 -$42


1
0
--


0

ProductB3 -$650 --


1

ProductB4 -$550 --


1

===================================================

Total <100----


2

Total >100----


1

Total >300----


3

There will be 2 Dimensions Dim_ProductsA and Dim_ProductsB.

My issue is with how to handle the flags 1 or 0. Note that we take totals of

these flags to COUNT the number of bids.

Any help in modelling this with the intent for better performance in reporting.

I believe Products A go to the DIM_ProductA while Products B go to Dim_ProductsB.

Will COUNT be a key figure?

Will "Less Than 100", "100-300" and "over 300 " be key figures?

How should the 1 and 0 be handled?

Will "Total < 100", "Total >100" and "Total >300" be key figures?

Thanks in Advance.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Dec 01, 2005 at 01:38 PM

    Hi Amanda,

    By aggregation I meant that ProductA1 might be bidden several times and in that row we could see a sum of these bids.

    But it looks like ProductA1 it’s the 1st bid for ProductA, ProductA2 – the 2nd one. Actually, in rows you have some kind of bid ID. Is it correct?

    If yes, then Anil’s solution is good. Since these 1s in columns are produced by formulas, they might be treated somewhat as CKF. Total counts are just total records for these columns.

    Best regards,

    Eugene

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2005 at 02:36 AM

    Hi,

    If both A and b are products , it is enough to take them in one dimension. Now come to reporting side, you will take 'Product' characterstic in to rows. And 'Bid' Kf in to columns, then you will define 3 formulsa.

    1) Formulla 1: (BID < 100)

    2) Formulla 2: (BID >= 100 and BID <= 300 )

    3) Formulla 3: (BID < 300)

    With rgds,

    Anil Kumar Sharma .P

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 01, 2005 at 04:55 AM

    Hi Amanda,

    I didn't quite understand: do you want to show invidual bids, each on a separate row, or rows may contain aggregated for a product data (several bids)?

    And look in your example at the row with product A1 - it has two 1s. Is it correct?

    Best regards,

    Eugene

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Eugene Khusainov

      Thanks Experts,

      First, Eugene you are right, there should not be any 1 for product A1 under "over 300", which makes

      the COUNT "Total >300" equal to 2.(see revised sample report below)

      So what happens to the COUNT? if COUNT will not be a key figure, then do we keep "Bid" as a Key Figure and

      then how do we count the 1's for rach column? That, what is the COUNT for "Total <100", "Total 100-300"

      and for "Total >300"?

      So I gather from the comments that these "Less Than 100", "100-300" and "over 300 " will NOT key figures? Then what are they? Are we to include them as characteristics? If not, then how do we bring them in the report as columns?

      Eugene, I don't get your point "If data in rows may be aggregated, I would think about counting bids on infoprovider level.

      " but I know for now that we need the COUNT on the bids for each range "Less Than 100", "100-300" and "over 300 "

      By "data in rows may be aggregated" did you mean if we want toi take totals of the bids in eacn row? (If so, then nO)

      Correct sample report:

      -


      Bid-- Less Than 100 -100-300 -over 300

      ProductA1 -$200


      0
      1
      --


      0

      ProductA2 -$60


      1
      0
      --


      0

      ProductB1 -$450


      0
      0
      --


      0

      ProductB2 -$42


      1
      0
      --


      0

      ProductB3 -$650 --


      1

      ProductB4 -$550 --


      1

      ===================================================

      Total <100----


      2

      Total >100----


      1

      Total >300----


      2

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.