Text 849, 179 rader
Skriven 2004-09-26 14:59:42 av Gary Britt (1:379/45)
    Kommentar till text 683 av Ellen K. (1:379/45)
Ärende: Re: need algorithm
==========================
From: "Gary Britt" <garyb@nospamforme.com>

OK thanks for the additional information.  Do you use a formula of some kind to
determine the key value for any particular row of info?  (I understand that the
formula or whatever would have to be designed with the particular kind of info
in mind)  Or is the key value basically a reference to a spreadsheet within the
data cube?

Thanks,

Gary

"Ellen K." <72322.enno.esspeeayem.1016@compuserve.com> wrote in message
news:beocl05t5o3rj1d57rmja7ijo0a23o255o@4ax.com...
> Let's stick with the time dimension.   You are scrolling through the
> month's sales and decide you want to see them by day of week, the
> database already knows how to show you that without you having to
> formulate a query to get there.   Or you are looking at a report of
> sales by store by month and November looks crummy and you want to know
> why so you drill down to sales by week -- again, the database already
> knows this, you don't need a separate query.
>
> How the numeric key value helps is twofold -- first of all that one key
> value already "knows" the day, week, month, year, etc.   Secondly,
> numeric values are processed much faster than textual ones.
>
>
> On Fri, 24 Sep 2004 20:13:04 -0400, "Gary Britt" <garyb@nospamforme.com>
> wrote in message <4154b610@w3.nls.net>:
>
> >Glad you got it figured out.
> >
> >??
> >> When the
> >> transactional data are transformed the numeric key value for each
> >> dimension applicable to each measure is substituted for the actual
value
> >> or values it represents.  The point of all this is to make online
> >> analysis really fast.
> >??
> >
> >It seems like you are saying you are building tables that make
consolidation
> >of data in various ways a bit easier; like pulling up sales of a
particular
> >product by week, month, year to date. etc.  Is that correct?
> >
> >I'm not clear on how what you've described as the dimension value
applicable
> >to each measure point and is substituted for the actual value/values
helps
> >in this process?
> >
> >Just curious,
> >
> >Gary
> >
> >
> >"Ellen K." <72322.enno.esspeeayem.1016@compuserve.com> wrote in message
> >news:tp19l0dt8vi73kfq3cj27s370gf5hku92k@4ax.com...
> >> This is for a data warehouse, not a transactional database.   The
> >> structure is completely different.
> >>
> >> A data warehouse holds two kinds of information, measures and
> >> dimensions.   Measures are the information we want to know about, and
> >> dimensions are what we want to know about it.   So for example, sales
is
> >> a measure, product is a dimension.
> >>
> >> The dimension tables have one row per possible atomic level of
> >> information.   So a time dimension (the easiest to understand) has one
> >> row per calendar date, and columns for month and year (date-month-year
> >> would be the granularity hierarchy) and also day-of-week and maybe
> >> week-of-year and maybe a "holiday" flag (non-hierarchical attributes).
> >> Each row in the dimension has a numeric key.
> >>
> >> Every however-often (mine will be done nightly), data are extracted
from
> >> the transactional databases, transformed to the OLAP (online analytical
> >> processing) format, and loaded into the data warehouse tables.  When
the
> >> transactional data are transformed the numeric key value for each
> >> dimension applicable to each measure is substituted for the actual
value
> >> or values it represents.  The point of all this is to make online
> >> analysis really fast.
> >>
> >> In the data warehouse database usually "cubes" are created.  Under the
> >> hood these are predefined multi-dimensional arrays.  Then the front end
> >> allows the user to automatically drill down and/or roll up
> >> interactively.   If you build the data warehouse in SQL Server, users
> >> can point their Excel at a cube directly and automatically be able to
> >> create their own pivot tables off it.
> >>
> >> On Fri, 24 Sep 2004 11:42:38 -0400, "Gary Britt"
<garyb@nospamforme.com>
> >> wrote in message <41543e68$1@w3.nls.net>:
> >>
> >> >Ellen, can you tell me the purpose of the table you are trying to
build.
> >> >Are you trying to just have a lookup table that validates if
information
> >> >entered is within an acceptable range?  In other words can you
describe
> >what
> >> >is the basic transaction for which this table you are constructing
will
> >be
> >> >used?
> >> >
> >> >Thanks,
> >> >
> >> >Gary
> >> >
> >> >"Ellen K" <Ellen.K@harborwebs.com> wrote in message
> >> >news:908654.36bcab@harborwebs.com...
> >> >> I need a mathematical algorithm as follows:
> >> >>
> >> >> For the risk dimension of my data warehouse I need one row for every
> >> >possible
> >> >> combination of down payment amount, down payment percentage, and
term
> >in
> >> >> months.   I created a working table by means of a Cartesian product.
> >> >Then I
> >> >> deleted all the rows where down payment was 0 but down payment
> >percentage
> >> >was
> >> >> not 0, and also deleted all the rows where down payment percentage
was
> >0
> >> >but
> >> >> down payment dollars was not 0.   I still have close to 13 million
rows
> >> >and
> >> >> would just as soon reduce the size further by removing rows
> >representing
> >> >other
> >> >> impossible combinations... so I need an algorithm for defining the
> >> >impossible
> >> >> combinations.  For example, if $5000 is the highest sales price for
a
> >> >single
> >> >> transaction, then $5000 can't be, say, a 1% down payment.   Note
that
> >> >there is
> >> >> an additional complication in that down payment percentage is
> >calculated
> >> >> against the sales price, not against the total transaction, which is
> >> >comprised
> >> >> of sales price + shipping + interest + sometimes sales tax, so that
a
> >> >$1000
> >> >> sales price might be an $1100 total transaction... in which case a
$200
> >> >down
> >> >> payment would be considered to be 20%, and if they pay cash, their
> >"down
> >> >> payment" is 110%.
> >> >>
> >> >> So far I think I see that "impossible" only applies to the higher
> >dollar
> >> >down
> >> >> payments, IOW a $100 "down payment" could theoretically be 100% of a
> >$100
> >> >sale.
> >> >>  It seems like the algorithm must be some kind of percentage
> >calculation
> >> >of
> >> >> down payment amount vs the maximum sales price...  ?
> >> >>
> >> >> Any light anyone can shed will be greatly appreciated.   :)
> >> >
> >>
> >
>

--- BBBS/NT v4.01 Flag-5
 * Origin: Barktopia BBS Site http://HarborWebs.com:8081 (1:379/45)