Hi,
I need some advise. We are in he process of designing our first data
mart. This Datamart will consists of sales orders activity. Sales
orders have lines created, deleted and inserted on a daily basis as
long as the sales order is open.
It has been suggested that we do triggers the these tables and then
populate the Datamart. Which the DBA is not thrilled with. is there
another way to do this wihtout triggers.?
The data needs to get updated daily and the volume is about 500K rows.
The data is being sourced from an ERP and the DB is oracle 9i. We also
have the option of using SQL Server if need be. Our BI tool is Cognos
and ETL tool is not an option since our data tranformations are very
simple.
Please share your experience.
Regards
Ather
Ather
I only put complete / closed / posted items into my DWH unless the business
can justify why they want incomplete / incorrect data in the DWH, so i would
only transfer closed orders
Use MS- SQL as your DWH server as you get DTS (Data Transformation
Services) with it, which can be used as a lightweight ETL tool
Create some DTS packages to update your DWH at the end of each day ( or any
other viable interval) with the new closed orders
Unless you have paid for / are forced to use COGNOS look carefully at
reporting services and analysis services as your BI tools
Regards
Reg Besseling
"Ather Mian" <ather@.nowwhere.com> wrote in message
news:eIdrqSV4EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
|||Identify all the OPEN orders (order status or some other combination of
field(s) ) which will narrow the list of orders that will get affected in
your datamart. For these open orders:
(1) Delete the orders from your datamart.
(2) Re-insert the data about the open orders.
I am making an assumption that you do not have track the order status
history itself. I am also making an assumption that data about "closed"
orders does not change and/or you do not want to change them in the datamart
in any way.
Hope this helps.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Ather Mian" wrote:
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
>
|||What Will happen to orders that change from open to closed ? it seems to me
that they will get "Lost" until the next full refresh
Regards
Reg Besseling
"Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...[vbcol=seagreen]
> Identify all the OPEN orders (order status or some other combination of
> field(s) ) which will narrow the list of orders that will get affected in
> your datamart. For these open orders:
> (1) Delete the orders from your datamart.
> (2) Re-insert the data about the open orders.
> I am making an assumption that you do not have track the order status
> history itself. I am also making an assumption that data about "closed"
> orders does not change and/or you do not want to change them in the
> datamart
> in any way.
> Hope this helps.
> Rangarajan Suresh
> www.picarossolutions.com
> *********************
>
> "Ather Mian" wrote:
|||I guess I should have also addressed the issue of "closed orders" along with
"open orders" -- The closed orders are to be loaded as inserts into the
datamart. They do not necessarily fall under the issue of "replacing"
existing open orders that may have changed in some fashion. Since we are
deleting existing open orders from the datamart, the same order will not go
into the datamart as it is no longer open.
Sorry for the confusion.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Reg Besseling" wrote:
> What Will happen to orders that change from open to closed ? it seems to me
> that they will get "Lost" until the next full refresh
> Regards
> Reg Besseling
> "Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
> message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...
>
