Design help appreciated 
Author Message
 Design help appreciated

Design considerations.

let me set up this example, I have 3 tables in our OLTP data schema

Table 1 - Trades
TradeId
RepId
TradeDate
Shares
Payout

Table 2 - Reps
RepId
Name

Table 3 - RepReporting
RepId
BranchName
HierarchyName
StartDate
EndDate

What I'm trying to figure out!

For reporting purposes, the RepReporting table will contain a Hierarchy
Name, Branch Name and a RepId with a start and end date.  A Rep can be in
multiple Hierarchies during at any given time.  For example some sample data
might look like the following:

RepId,  BranchName,  HierarchyName, StartDate,  EndDate

AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
AAA,    San Francisco, HierarchyOne,  5/14/1999,    1/1/2040
BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      1/1/2040
AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

Now, I need to create a Datawarehouse schema.  My current one has 2 tables:

Table 1 - Fact
TradeId
RepId
Shares
Gross
Payout

Table 2 - dim_Reps
RepId
RepName
BranchName
HierarchyName
StartDate
EndDate

Output:

I need to get back only the aggregated trade information for a
rep/branch/hierarchy combination where the Trades.TradeDate is between the
RepReporting.StartDate and RepReporting.EndDate.

My questions are three fold;

1) For the data warehouse, do i create a single row for each Trades record
that is associate to a rep in each branch/hierarchy combination, where the
Trades.TradeDate is between the RepReporting.StartDate and
RepReporting.EndDate.  I see this as easing the Cube/Dimension design, but
dramatically increasing both Cube processing and the ETL package.

2) Or would I create the Fact table based on the Trades table, a dimension
table like dim_Reps (above), and use a complex MDX query (if even possible)
to get the desired Output (as above).  I see this as minimizing processing
but increasing query complexity.

3) Or can i create two cubes joined into a Virtual Cube that might be able
to handle my desired output.

Any comments are appreciated!!

.chris.



Tue, 27 Jul 2004 23:29:23 GMT
 Design help appreciated

First I need to ask you this based on your sample:
if you had a trade occuring on 5/12/1999 with RepId of "AAA",
which RepReporting hierarchy gets credit?

Quote:
> AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

There's something missing (either in Table1 or Table 3 or both)


Quote:
> Design considerations.

> let me set up this example, I have 3 tables in our OLTP data schema

> Table 1 - Trades
> TradeId
> RepId
> TradeDate
> Shares
> Payout

> Table 2 - Reps
> RepId
> Name

> Table 3 - RepReporting
> RepId
> BranchName
> HierarchyName
> StartDate
> EndDate

> What I'm trying to figure out!

> For reporting purposes, the RepReporting table will contain a Hierarchy
> Name, Branch Name and a RepId with a start and end date.  A Rep can be in
> multiple Hierarchies during at any given time.  For example some sample
data
> might look like the following:

> RepId,  BranchName,  HierarchyName, StartDate,  EndDate

> AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> AAA,    San Francisco, HierarchyOne,  5/14/1999,    1/1/2040
> BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      1/1/2040
> AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> Now, I need to create a Datawarehouse schema.  My current one has 2
tables:

> Table 1 - Fact
> TradeId
> RepId
> Shares
> Gross
> Payout

> Table 2 - dim_Reps
> RepId
> RepName
> BranchName
> HierarchyName
> StartDate
> EndDate

> Output:

> I need to get back only the aggregated trade information for a
> rep/branch/hierarchy combination where the Trades.TradeDate is between the
> RepReporting.StartDate and RepReporting.EndDate.

> My questions are three fold;

> 1) For the data warehouse, do i create a single row for each Trades record
> that is associate to a rep in each branch/hierarchy combination, where the
> Trades.TradeDate is between the RepReporting.StartDate and
> RepReporting.EndDate.  I see this as easing the Cube/Dimension design, but
> dramatically increasing both Cube processing and the ETL package.

> 2) Or would I create the Fact table based on the Trades table, a dimension
> table like dim_Reps (above), and use a complex MDX query (if even
possible)
> to get the desired Output (as above).  I see this as minimizing processing
> but increasing query complexity.

> 3) Or can i create two cubes joined into a Virtual Cube that might be able
> to handle my desired output.

> Any comments are appreciated!!

> .chris.



Wed, 28 Jul 2004 02:05:34 GMT
 Design help appreciated
actually, both need to see it.  this is the route of my dilemna.



Quote:
> First I need to ask you this based on your sample:
> if you had a trade occuring on 5/12/1999 with RepId of "AAA",
> which RepReporting hierarchy gets credit?

> > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> There's something missing (either in Table1 or Table 3 or both)



> > Design considerations.

> > let me set up this example, I have 3 tables in our OLTP data schema

> > Table 1 - Trades
> > TradeId
> > RepId
> > TradeDate
> > Shares
> > Payout

> > Table 2 - Reps
> > RepId
> > Name

> > Table 3 - RepReporting
> > RepId
> > BranchName
> > HierarchyName
> > StartDate
> > EndDate

> > What I'm trying to figure out!

> > For reporting purposes, the RepReporting table will contain a Hierarchy
> > Name, Branch Name and a RepId with a start and end date.  A Rep can be
in
> > multiple Hierarchies during at any given time.  For example some sample
> data
> > might look like the following:

> > RepId,  BranchName,  HierarchyName, StartDate,  EndDate

> > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > AAA,    San Francisco, HierarchyOne,  5/14/1999,    1/1/2040
> > BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      1/1/2040
> > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> > Now, I need to create a Datawarehouse schema.  My current one has 2
> tables:

> > Table 1 - Fact
> > TradeId
> > RepId
> > Shares
> > Gross
> > Payout

> > Table 2 - dim_Reps
> > RepId
> > RepName
> > BranchName
> > HierarchyName
> > StartDate
> > EndDate

> > Output:

> > I need to get back only the aggregated trade information for a
> > rep/branch/hierarchy combination where the Trades.TradeDate is between
the
> > RepReporting.StartDate and RepReporting.EndDate.

> > My questions are three fold;

> > 1) For the data warehouse, do i create a single row for each Trades
record
> > that is associate to a rep in each branch/hierarchy combination, where
the
> > Trades.TradeDate is between the RepReporting.StartDate and
> > RepReporting.EndDate.  I see this as easing the Cube/Dimension design,
but
> > dramatically increasing both Cube processing and the ETL package.

> > 2) Or would I create the Fact table based on the Trades table, a
dimension
> > table like dim_Reps (above), and use a complex MDX query (if even
> possible)
> > to get the desired Output (as above).  I see this as minimizing
processing
> > but increasing query complexity.

> > 3) Or can i create two cubes joined into a Virtual Cube that might be
able
> > to handle my desired output.

> > Any comments are appreciated!!

> > .chris.



Wed, 28 Jul 2004 02:16:08 GMT
 Design help appreciated
ouch!

Quote:
> 1) For the data warehouse, do i create a single row for each Trades
> record that is associate to a rep in each branch/hierarchy combination,
where
> the Trades.TradeDate is between the RepReporting.StartDate and
> RepReporting.EndDate.  I see this as easing the Cube/Dimension design,
> but  dramatically increasing both Cube processing and the ETL package.

I wouldn't do this: it misrepresents the facts.  If there was 1 transaction,
I'd keep 1 transaction.  You can simulate this by creating a view if you
really want to see the repeated records.  I doubt if it'll affect AS very
much.

Quote:
> 2) Or would I create the Fact table based on the Trades table, a
> dimension table like dim_Reps (above), and use a complex MDX query (if
even
> possible) to get the desired Output (as above).  I see this as minimizing
> processing  but increasing query complexity.

I doubt if either scenario really minimizes processing or increases the
complexity.
Use your  "Table 1 - Trades" as your fact table (only because your
"Table 1 - Fact" lost the date and gained an undefined "Gross" column).
Use you "dim_Reps" as your dimension.

When designing your Reps dimension, mark "No" on "All Level" from
the Advanced tab.

When designing your cube, use "Source Table Filter" from the Advanced tab
to add your (WHERE fact.date between dim.startdate and dim.enddate) if
you use the table as is.  If you use a view as per 1) above and include the
Branch and Hierarchy columns in the view,  the cube designer may use
multiple columns to do the join and you won't need to worry about the
WHERE condition since it is already in the view.

Quote:
> 3) Or can i create two cubes joined into a Virtual Cube that might be
> able to handle my desired output.

nah.

Hope that helped.

Sonny


Quote:
> actually, both need to see it.  this is the route of my dilemna.



> > First I need to ask you this based on your sample:
> > if you had a trade occuring on 5/12/1999 with RepId of "AAA",
> > which RepReporting hierarchy gets credit?

> > > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> > There's something missing (either in Table1 or Table 3 or both)



> > > Design considerations.

> > > let me set up this example, I have 3 tables in our OLTP data schema

> > > Table 1 - Trades
> > > TradeId
> > > RepId
> > > TradeDate
> > > Shares
> > > Payout

> > > Table 2 - Reps
> > > RepId
> > > Name

> > > Table 3 - RepReporting
> > > RepId
> > > BranchName
> > > HierarchyName
> > > StartDate
> > > EndDate

> > > What I'm trying to figure out!

> > > For reporting purposes, the RepReporting table will contain a
Hierarchy
> > > Name, Branch Name and a RepId with a start and end date.  A Rep can be
> in
> > > multiple Hierarchies during at any given time.  For example some
sample
> > data
> > > might look like the following:

> > > RepId,  BranchName,  HierarchyName, StartDate,  EndDate

> > > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > > AAA,    San Francisco, HierarchyOne,  5/14/1999,    1/1/2040
> > > BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      1/1/2040
> > > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> > > Now, I need to create a Datawarehouse schema.  My current one has 2
> > tables:

> > > Table 1 - Fact
> > > TradeId
> > > RepId
> > > Shares
> > > Gross
> > > Payout

> > > Table 2 - dim_Reps
> > > RepId
> > > RepName
> > > BranchName
> > > HierarchyName
> > > StartDate
> > > EndDate

> > > Output:

> > > I need to get back only the aggregated trade information for a
> > > rep/branch/hierarchy combination where the Trades.TradeDate is between
> the
> > > RepReporting.StartDate and RepReporting.EndDate.

> > > My questions are three fold;

> > > 1) For the data warehouse, do i create a single row for each Trades
> record
> > > that is associate to a rep in each branch/hierarchy combination, where
> the
> > > Trades.TradeDate is between the RepReporting.StartDate and
> > > RepReporting.EndDate.  I see this as easing the Cube/Dimension design,
> but
> > > dramatically increasing both Cube processing and the ETL package.

> > > 2) Or would I create the Fact table based on the Trades table, a
> dimension
> > > table like dim_Reps (above), and use a complex MDX query (if even
> > possible)
> > > to get the desired Output (as above).  I see this as minimizing
> processing
> > > but increasing query complexity.

> > > 3) Or can i create two cubes joined into a Virtual Cube that might be
> able
> > > to handle my desired output.

> > > Any comments are appreciated!!

> > > .chris.



Wed, 28 Jul 2004 03:40:06 GMT
 Design help appreciated
Oops, the Fact Table should look like

Table 1 - Fact
TradeId
RepId
TradeDate
Shares
Payout



Quote:
> ouch!

> > 1) For the data warehouse, do i create a single row for each Trades
> > record that is associate to a rep in each branch/hierarchy combination,
> where
> > the Trades.TradeDate is between the RepReporting.StartDate and
> > RepReporting.EndDate.  I see this as easing the Cube/Dimension design,
> > but  dramatically increasing both Cube processing and the ETL package.

> I wouldn't do this: it misrepresents the facts.  If there was 1
transaction,
> I'd keep 1 transaction.  You can simulate this by creating a view if you
> really want to see the repeated records.  I doubt if it'll affect AS very
> much.

> > 2) Or would I create the Fact table based on the Trades table, a
> > dimension table like dim_Reps (above), and use a complex MDX query (if
> even
> > possible) to get the desired Output (as above).  I see this as
minimizing
> > processing  but increasing query complexity.
> I doubt if either scenario really minimizes processing or increases the
> complexity.
> Use your  "Table 1 - Trades" as your fact table (only because your
> "Table 1 - Fact" lost the date and gained an undefined "Gross" column).
> Use you "dim_Reps" as your dimension.

> When designing your Reps dimension, mark "No" on "All Level" from
> the Advanced tab.

> When designing your cube, use "Source Table Filter" from the Advanced tab
> to add your (WHERE fact.date between dim.startdate and dim.enddate) if
> you use the table as is.  If you use a view as per 1) above and include
the
> Branch and Hierarchy columns in the view,  the cube designer may use
> multiple columns to do the join and you won't need to worry about the
> WHERE condition since it is already in the view.

> > 3) Or can i create two cubes joined into a Virtual Cube that might be
> > able to handle my desired output.
> nah.

> Hope that helped.

> Sonny



> > actually, both need to see it.  this is the route of my dilemna.



> > > First I need to ask you this based on your sample:
> > > if you had a trade occuring on 5/12/1999 with RepId of "AAA",
> > > which RepReporting hierarchy gets credit?

> > > > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > > > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> > > There's something missing (either in Table1 or Table 3 or both)



> > > > Design considerations.

> > > > let me set up this example, I have 3 tables in our OLTP data schema

> > > > Table 1 - Trades
> > > > TradeId
> > > > RepId
> > > > TradeDate
> > > > Shares
> > > > Payout

> > > > Table 2 - Reps
> > > > RepId
> > > > Name

> > > > Table 3 - RepReporting
> > > > RepId
> > > > BranchName
> > > > HierarchyName
> > > > StartDate
> > > > EndDate

> > > > What I'm trying to figure out!

> > > > For reporting purposes, the RepReporting table will contain a
> Hierarchy
> > > > Name, Branch Name and a RepId with a start and end date.  A Rep can
be
> > in
> > > > multiple Hierarchies during at any given time.  For example some
> sample
> > > data
> > > > might look like the following:

> > > > RepId,  BranchName,  HierarchyName, StartDate,  EndDate

> > > > AAA,    New York,      HierarchyOne,  1/1/1980,      5/13/1999
> > > > AAA,    San Francisco, HierarchyOne,  5/14/1999,    1/1/2040
> > > > BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      1/1/2040
> > > > AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,       1/1/2040

> > > > Now, I need to create a Datawarehouse schema.  My current one has 2
> > > tables:

> > > > Table 1 - Fact
> > > > TradeId
> > > > RepId
> > > > Shares
> > > > Gross
> > > > Payout

> > > > Table 2 - dim_Reps
> > > > RepId
> > > > RepName
> > > > BranchName
> > > > HierarchyName
> > > > StartDate
> > > > EndDate

> > > > Output:

> > > > I need to get back only the aggregated trade information for a
> > > > rep/branch/hierarchy combination where the Trades.TradeDate is
between
> > the
> > > > RepReporting.StartDate and RepReporting.EndDate.

> > > > My questions are three fold;

> > > > 1) For the data warehouse, do i create a single row for each Trades
> > record
> > > > that is associate to a rep in each branch/hierarchy combination,
where
> > the
> > > > Trades.TradeDate is between the RepReporting.StartDate and
> > > > RepReporting.EndDate.  I see this as easing the Cube/Dimension
design,
> > but
> > > > dramatically increasing both Cube processing and the ETL package.

> > > > 2) Or would I create the Fact table based on the Trades table, a
> > dimension
> > > > table like dim_Reps (above), and use a complex MDX query (if even
> > > possible)
> > > > to get the desired Output (as above).  I see this as minimizing
> > processing
> > > > but increasing query complexity.

> > > > 3) Or can i create two cubes joined into a Virtual Cube that might
be
> > able
> > > > to handle my desired output.

> > > > Any comments are appreciated!!

> > > > .chris.



Wed, 28 Jul 2004 08:22:53 GMT
 Design help appreciated
It will be better if you do further research on star
schema. You missing, obviously, some dimensions i.e. time
dim.
Regards,
Najm
Quote:
>-----Original Message-----
>Design considerations.

>let me set up this example, I have 3 tables in our OLTP
data schema

>Table 1 - Trades
>TradeId
>RepId
>TradeDate
>Shares
>Payout

>Table 2 - Reps
>RepId
>Name

>Table 3 - RepReporting
>RepId
>BranchName
>HierarchyName
>StartDate
>EndDate

>What I'm trying to figure out!

>For reporting purposes, the RepReporting table will
contain a Hierarchy
>Name, Branch Name and a RepId with a start and end date.  
A Rep can be in
>multiple Hierarchies during at any given time.  For

example some sample data

- Show quoted text -

Quote:
>might look like the following:

>RepId,  BranchName,  HierarchyName, StartDate,  EndDate

>AAA,    New York,      HierarchyOne,  1/1/1980,      
5/13/1999
>AAA,    San Francisco, HierarchyOne,  5/14/1999,    
1/1/2040
>BBB,     Los Angeles,  HierarchyTwo,  1/1/1980,      
1/1/2040
>AAA,    Los Gatos,      HierarchyTwo,  1/1/1980,      
1/1/2040

>Now, I need to create a Datawarehouse schema.  My current
one has 2 tables:

>Table 1 - Fact
>TradeId
>RepId
>Shares
>Gross
>Payout

>Table 2 - dim_Reps
>RepId
>RepName
>BranchName
>HierarchyName
>StartDate
>EndDate

>Output:

>I need to get back only the aggregated trade information
for a
>rep/branch/hierarchy combination where the

Trades.TradeDate is between the
Quote:
>RepReporting.StartDate and RepReporting.EndDate.

>My questions are three fold;

>1) For the data warehouse, do i create a single row for
each Trades record
>that is associate to a rep in each branch/hierarchy

combination, where the
Quote:
>Trades.TradeDate is between the RepReporting.StartDate and
>RepReporting.EndDate.  I see this as easing the

Cube/Dimension design, but
Quote:
>dramatically increasing both Cube processing and the ETL
package.

>2) Or would I create the Fact table based on the Trades
table, a dimension
>table like dim_Reps (above), and use a complex MDX query
(if even possible)
>to get the desired Output (as above).  I see this as

minimizing processing

- Show quoted text -

Quote:
>but increasing query complexity.

>3) Or can i create two cubes joined into a Virtual Cube
that might be able
>to handle my desired output.

>Any comments are appreciated!!

>..chris.

>.



Sat, 31 Jul 2004 02:18:17 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Help for query interface design would be highly appreciated

2. Ado Help Please Help any help is appreciated

3. query help appreciated. max()/joins/subquery

4. IMMEDIATE help appreciated

5. ****ANY HELP APPRECIATED**********

6. Appreciate your help

7. Standalone installation of SQLserver 6.5 your help is appreciated

8. Good Reporting Tool for VB to SQL (Currently Use Crystal) or Crystal w/SQL Help Appreciated

9. Any help is appreciated!!

10. SQL 7 DTS export to VFP- any help appreciated

11. Problem with OLAP SP2 : Urgent help appreciated


 
Powered by phpBB® Forum Software