Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}      PL/SQL (Procedural Language/Structured Query Language)PL/SQL is a language that enables developers to create procedures, functions that combines sql with procedural statements.PL/SQL block has three parts in it.1. Declaration    All objects of the block are declared.2. Execution       The objects are defined to manipulate data.3. Exception       Error handling logic.==>Declaration part is optional when we do not declare any objects==>Exception part is also optional if we are not handling any exceptions.==>But the Execution part is must in a PL/SQL Program and it starts with BEGIN and ends with END. Execution:========To Execute the PL/SQL Program follow the syntax as@<file_name>orstart<file_name>==>To Display the Result, give the command as followsset serveroutput...

Read more »

Data warehousing Interview Quiestions

DATA WAREHOUSING BASICS INTERVIEW QUESTIONS & ANSWERS 1.     What is hybrid slowly changing dimension? Ans:- Hybrid SCDs are combination of both SCD 1 and SCD 2. It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care. For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2. You can add that it is not an intelligent key but similar to a sequence number and tied to a timestamp typically! 2.     can a dimension table contains numeric values? Ans:- Yes.But those datatype will be char (only the values can numeric/char). yes,diemesions even contain numerical because these are disscriptive elements of ur business 3.   How to Create Surrogate Key using Ab Initio? Ans:- There are many ways to create Surrogatekey but it depends on your business    logic. Here you can try these ways. 1. use next_in_sequence() function in your transform. 2.use Assign key values component (if your gde is higher than 1.10) 3.write a stored proc to this and call this stor proc wherever you need. Yes, dimension table contains numerics but not contain measures and facts 4.       Differences between star and snowflake schemas ? Ans:- Star schema A single fact table with N number of Dimension Snowflake schema Any dimensions with extended dimensions are know as snowflake schema STAR MODEL :Radical arrangement of the organisation with one fact table and more dimension tables SNOWFLAKE MODEL: The dimension table subdivide itself forming new dimension table and start acting as a fact table. a datamart is subject oriented data base through which we can analyse the business in each department in an organisation. data marts are called high query performance structures 5.   What is a CUBE in datawarehousing concept? Ans:- Cubes are logical representation of multidimensional data.The edge of the cube    contains dimension members and the body of the cube contains data values. 6.  Difference between Snow flake and Star Schema. What are situations where Snow   flake Schema is better than Star Schema to use and when the opposite is true? Ans:- Star schema contains the dimesion tables mapped around one or more fact tables. It is a denormalised model. No need to use complicated joins. Queries results fastly. Snowflake schema It is the normalized form of Star schema. Contains indepth joins ,bcas the tbales r splitted in to many pieces.We can easily do modification directly in the tables. We hav to use comlicated joins ,since we hav more tables . There will be some delay in processing the Query . 7. What is ER Diagram ?    Ans:- The Entity-Relationship (ER) model was originally proposed by Peter in 1976 [Chen76] as a way to unify the network and relational database views. Simply stated the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. Since Chen wrote his paper the model has been extended and today it is commonly used for database design For the database designer, the utility of the ER model is: it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. it is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user. In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software. 8. What is degenerate dimension table? Ans:- Degenerate Dimensions : If a table contains the values, which r neither dimesion nor measures is called degenerate dimensions.Ex : invoice id,empno 9. What is VLDB?? Ans:- The perception of what constitutes a VLDB continues to grow. A one terabyte database would normally be considered to be a VLDB. degenerate dimension:it doesn't have any link with dimensions and i wont have any attribute. 10. What is meant by metadata in context of a Datawarehouse and how it is important? Ans:- Meta data is the data about data; Business Analyst or data modeler usually capture information about data - the source (where and how the data is originated), nature of data (char, varchar, nullable, existance, valid values etc) and behavior of data (how it is modified / derived and the life cycle ) in data dictionary a.k.a metadata. Metadata is also presented at the Datamart level, subsets, fact and dimensions, ODS etc. For a DW user, metadata provides vital information for analysis / DSS. 11. what is incremintal loading?2.what is batch processing?3.what is crass reference table?4.what is aggregate fact table?? Ans:- Incremental loading means loading the ongoing changes in the OLTP. Aggregate table contains the [measure] values ,aggregated /grouped/summed up to some level of hirarchy. 12. What are the possible data marts in Retail sales.? Ans:- Product information,sales information 13. What is the main differnce between schema in RDBMS and schemas in Data Warehouse....? Ans:- RDBMS Schema * Used for OLTP systems,* Traditional and old schema,* Normalized * Difficult to understand and navigate.,* Cannot solve extract and complex problems * Poorly modelled DWH Schema * Used for OLAP systems.,* New generation schema.,* De Normalized * Easy to understand and navigate., * Extract and complex problems can be easily solved .,* Very good model 14. What are the vaious ETL tools in the Market? Ans:- Various ETL tools used in market are: 1)Informatica.2)Data Stage 3) Oracle Warehouse Bulider 4)Ab Initio.,Data Junction Apart from the above Humming Bird Genio, Business Objects Data Integrator 15. What is Dimensional Modelling? Ans:- Dimensional Modelling is a design concept used by many data warehouse desginers to build thier datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measuremnets ie, the dimensions on which the facts are calculated. 16. What is Data warehosuing Hierarchy? Ans:- HierarchiesHierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure. Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies--one for product categories and one for product suppliers. Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse. When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization. Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly. LevelsA level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels....

Read more »