Design a script and integrate procedures, PL-SQL Programming

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the option to retrieve information based on a time period, meaning using start date and end date to define a search constraint. For example, if a user wants information from 1-MAR-12 to 8-MAR-12, he or she can plug those dates in as search parameters.

Queries: I designed the queries to answer the project questions.

Scripts: pop_fact_table.sql, which means populate fact table, receives 2 parameters type, DATE, uses the dateas conditions within a select statement to create a cursor.These parameters are used to set a time period boundary. Also the order_items idsare extracted because they are unique and I used them as a condition to get theoi.movie_id, dml.distributor_id, store_id, o.total_payment, o.tax, number_of_itemsdata from multiple tables. I used order_completed_date because I felt it was importantto extract information for orders completed instead of just using the ordering date.Ordering date does not mean the order was completed. Also I fed this date intoa function which could build my time dimension table and return the time_id to insert into the facts table. Finally, the information gathered from order_items were inserted in the facts table.

• Fill_time_dim.sql receives a parameter type, DATE, from pop_fact_table.sql, analyzes the date, then determines the month, quarter and year in which it belongs. Finally it uses this data and a generated time_id from sequence to populate the time dimension table.

• Star_schema_table.sl drops and creates the shell for the 4 dimensions tables and the fact table.

• Add_constraints.sql sets the 4 dimension primary keys as primary and foreign keys on the facts table.

• User_interface.sql is used to simulate a user's terminal requesting information. Initially, it builds the table shells and adds constraints. Once the user determines his/her search time period, data is etracted from the operational database and is placed in the facts table.

Posted Date: 3/12/2013 2:11:20 AM | Location : United States







Related Discussions:- Design a script and integrate procedures, Assignment Help, Ask Question on Design a script and integrate procedures, Get Answer, Expert's Help, Design a script and integrate procedures Discussions

Write discussion on Design a script and integrate procedures
Your posts are moderated
Related Questions
I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.

Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl