Pages

Data Warehousing Lifecycle

Can you describe data warehousing lifecycle?
Tell us what you know about data warehousing lifecycle?
What are the processes and phases in data warehousing lifecycle?

These are fundamental questions to test your knowledge of data warehousing. To answer the questions, just briefly explain each phase of the lifecycle:

1. Data acquisition processes—extract, transform, load (ETL)

The processes include sourcing, cleansing, transforming, and aggregating data using parallel technology tools to build industrial-strength ETL processes that accommodate high data volumes from disparate sources. We identify the best sources for data elements, reconstructing data when required and deploying the most appropriate tools to retrieve the data from its primary sources. Through the cleansing process, we enhance data quality by ensuring data accuracy, type, and consistency, as well as eliminating duplicate records.

2. Data repositories

In the phase you need to build a variety of data repositories, including operational data stores, data marts, data warehouses, web warehouses, and data hubs. You start by implementing and properly documenting a physical data model, ensuring data from all functional areas is sufficiently integrated to support cross-functional analyses. Then you perform database tuning, model denormalization, and aggregation as necessary to support information delivery requirements. When scalability requirements call for it, you partition and distribute data into a parallel architecture.

3. Information delivery applications

You implement information delivery applications that allow corporate users to access the data in the warehouse. These include decision support tools, data mining and analytic tools, and applications that optimize supply chain, campaign management, billing, and industry-specific processes. In this phase you also ensure near- and long-term reporting and access requirements are met as well. These may include fixed-frequency static reports; ad-hoc reports; dynamic, multidimensional queries; Internet/intranet application interfaces; and data mining.

4. Data warehouse administration

As the data warehouse grows, administration (or management) of the repository is a crucial step in optimizing results and return on investment. You provide data warehousing administration services such as performance analysis, user analysis, benchmarking, auditing, and tuning to help clients measure the ongoing success of their data strategies.

Oracle Redo Log Switching and Archiving

What is the difference between redo log switching and redo log archiving in Oracle database?

This question is to test your understanding in Oracle instance recovery via online redo logs and archive logs. It is a part of the core knowledge of Oracle database. To answer this question, first briefly describe what is redo log and how it works; then explain different functions and purposes of log switching and archiving. If you are not familiar with redo logs and archive logs, the key to remember is that, When one log group is full, log writing switches to the next group; the filled log group is archived at the time of log switch. Thus log switching and arching happen at the same time; switching is to overwriting (the next log), and archiving is to preserving (the current log).

"Oracle uses redo log files to record change to the database when data transactions or internal Oracle server actions occur. They are also called online logs. There are usually multiple groups of redo logs. A group consists of a redo log file and its multiplexed copies. All logs within a group are identical. An Oracle background process called LogWriter (LGWR) writes redo records from the redo log buffer to all members of a redo log group. When an online redo log group gets filled, the Oracle instance begins writing to the next online redo log group. The process of switching from one online redo log group to another is called a log switch. Log switch also occurs when a log switch operation is requested. Redo log groups are used in a circular fashion, thus logs in each group will be overwritten when the switch to this log group happens."

"Online redo log archiving is an optional mechanism to further protect the data from the loss of integrity, such as the recovery of a database after the loss of a disk. Archiving works when the database is set to the archiving mode. The Archiver (ARCn) process initiates archiving of the filled log group at every log switch. It automatically archives the online redo log group before the log group can be overridden/reused so that all the changes made to the database are preserved. This enables recovery of the database to the point of failure even if a disk drive is damaged."

What is the Difference of CLOB and BLOB in Oracle?

Would you explain the difference of data types between CLOB and BLOB in Oracle? This is a quite common question in Oracle DBA/developer interview. There is usually at least one question regarding data types in a database position interview. To answer this question, you need to explain what is LOB, the difference between CLOB and BLOB, and the similarities as well.

Sample answer:

"In newer version (since 9i) Oracle introduced large object (LOB) data types to handle complex structured and unstructured data. CLOB is the the character LOB and BLOB is the binary LOB. The CLOB datatype stores character set data in the database. A specific CLOB, called NCLOB, stores Unicode national character set data. Storing varying-width LOB data in a fixed-width Unicode character set internally enables Oracle database to provide efficient character-based random access on CLOBs and NCLOBs. The BLOB datatype stores unstructured binary data in the database. BLOB primarily is used to hold non-traditional data, such as images, documents (pdf, doc, txt, etc.), and media files like audio and video. Both CLOB and BLOB participate fully in transactions. Changes made to a CLOB or BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and BLOB locators cannot span transactions or sessions. Both CLOB and BLOB can store huge data, up to 128 terabytes."

What Not to Say in Job Interview

Without a doubt, landing your dream job takes a lot of effort. From researching and applying for jobs to resume updating and finally landing an interview, a lot of hard work goes into being ready for your big moment. You've put in the time to rehearse and know what you're going to say, now it's time for your interview. But what if you've rehearsed too much? Even if your resume is a star, if you come off sounding like a robot or lack real feeling in your answers, you could end up missing out on a great opportunity. In this article, Glassdoor has identified eight lines you should avoid using in your next job interview, as well as what you should say instead.

Shell Variables vs. Environment Variables in Linux

What is the difference between Linux Shell Variables and Environment Variables?

 This is a common question often asked in Linux administration job interviews.  The key to a clear answer is to describe the scope of the two types of variables. Basically, shell variables are only available to the current invocation of the shell; environment variables become properties of the system and transcend shell invocation.  That means, a shell variable defined in one shell will not be seen if you switch to another shell, but you can see the environment variables defined in other shells.

How To Get More Than A Beginner's Salary

The other day, I received this question on our Expert forum.

“I am in a situation where I have studied communication, got a distinction for my final year … its been 3 yrs and I have landed in so many unrelated jobs that I beginning to think that I will never work in communications … how can I get there and not be paid a beginners salary??”

This was my answer:

If you don’t want to get a beginner’s salary, don’t act like a beginner. Let’s cover the unrelated work experiences… Now the reason you’ve been working in unrelated fields is to get some LIFE experience right? (if it isn’t, it is now). You wanted to see how other industries worked and discover if communications was the RIGHT thing for YOU and… here’s the key… whether it was RIGHT FOR YOUR EMPLOYER TOO!!!.

You don’t want to be some “wet behind the ears” university or college graduate begging for a job in communications… You realize that communications is an ASSET to business and you feel NOW that you are ready to be an asset to not only your employer but your employer’s customers too. Do you see where I’m coming from?

Now, the money thing can be handled in two ways…

Negotiate in the interview for a pay increase when you reach certain targets or time period. For example, you could ask for a wage review in 6 months to revise your pay because you don’t want to work for $X – you’d ideally want $Y… This means you’d work at a lower rate to reduce some of the risk for the employer in exchange for them taking a chance on employing you. If at the end of X months they don’t give you the raise find out why and if the reason’s not good enough, you can quit because you have experience now… Or,

Establish the money UP FRONT in your application. “I am looking for a starting salary of $X… a fun work environment with lots of trees and ample parking and some crazy people to creatively grow your business. If you can offer this I would love to talk.” For anyone who has read my book they will know exactly where I am coming from with this and how to use it… you may be thinking: “I’m not saying anything like that, I’ll never get a job!” But there IS a way to say it and still not blow your chances of getting a job.

But I digress… There is only upside here… Let me explain:

Someone can meet your requirements and you get an interview or job… SUCCESS!
You are not desperate… you have supported yourself for 3 years outside of the communications industry.

Every rejection you get will be coming from businesses that you probably don’t want to work in… assuming that you want lots of trees, crazy people and ample parking, etc.

Face it… there are so many bored employees working in jobs they hate, do you really want to be one of those or do you want a creative, fun work environment? I know what I would choose.

by Acland Brierty

SHAMELESS PLUG: Job Secrets Revealed will teach you how to read between the lines of any ‘situations vacant’ ad to work out how to best pitch your application, regardless of whether you meet their up-front skills or experience requirements.

DBA and Developer Questions for Oracle Views

When hiring Oracle DBAs and developers, a lot of questions are asked about Oracle view. Although view is only one of Oracle objects and it is not that significant in my opinion, for some reason managers and business users love views, therefore in interview they usually like to ask more questions on Oracle views. The following are some of questions and answers for you to prepare:

1. What is a view and how to create one?

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if you frequently use a query SELECT empid FROM emp, you may create a view as
CREATE VIEW view_emp
AS
SELECT empid FROM emp;

Then every time you query this view, Oracle will go out and execute the SQL behind the view to query the database data.

2. Can Oracle view improve performance? Why or why not?

In most cases view does improve performance because of commonality of code being used. Since a view is based on one common set of SQL, when it is called it’s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. A query without parsing is faster. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse.

3. Explain how you may use view to enhance security and what are view’s limitations for security practice?

It is a two-question in one but people often miss the second one. In any database, view’s limitation for security is the issue of scalability.

Answer:

Views are commonly used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

4. In what situation Oracle views can affect performance, and what is Oracle’s solution to this issue?

When you create complex views with many table joins to represent large subsets of query and allow many developers and end users to access these views, you may experience poor performance. In this case, you may consider Oracle’s materialized view.

5. What is materialized view and where is it used?

A materialized view is a snapshot of data that can be refreshed based on a schedule. It is a replica of a target master from a single point in time. Oracle uses materialized views to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.

6. Can view be indexed in Oracle?

Don’t think Oracle can do everything and quickly answer ‘yes’. SQL Server offers view index, but Oracle does not do it.

Answer:

Regular Oracle view cannot be indexed, but you can utilize table level indexed to tune underlying query that generates the view.

7. Can you insert and update records in a view?

This question asks if you can execute insert or update statement directly on a view instead of on a table.

Answer:

You can only perform data manipulation language (DML) operations with simple views. Complex views don’t support DML. view is mainly used for presenting and data and simplifying query. Data insert and update should be done on table level.

8. What’s the different between GRANT CREATE VIEW and CREATE ANY VIEW?

It is not a question about view. This question checks your knowledge of controlling user privileges in Oracle database. Grant Create view gives user’s right to create new views in grantee’s schema only. GRANT CREATE ANY VIEW allow users to create views in any schema.

Please Tell Us about Yourself

You cannot miss this one, as one of the most common interview questions, it is usually the first one.  In fact, this is the most critical question because what you answer will set the tone for the rest of the interview. Think it’s easy question?  It is easy to talk about yourself, but many interviewees fail to provide an impressive answer.  You can do better though, if you spend enough time to prepare.  It worth the time to prepare a killer answer.

It is such a good opportunity to sell yourself, and it may be the only chance that you can control the approach to make a sales presentation.  So I’d better prepare a impressive speech.  If you do it well on this one, you are half way to win the job.

What could be the best answer?  The best answer is what interviewers want to hear.  So that you should craft your answer around the interviewer’s perspective.  In general, your answer should focus on expected skills and required qualifications.  You don’t have to provide information outside of the job.

Alternatively interviewers may ask the same question like:

“Tell us about you education and experience?”

“Tell us how your skills and education that you think qualifies you for this position?”

Usually you should start your answer with your current or latest position.  You may also start with your education background if it is required or you have highly relevant and impressive degree.  You must give a concise summary of your relevant experience and accomplishments.  Before interview you should have prepared a list of your strongest selling points that are relevant to this position, — specific skills required for this job, successful projects similar to what the new employer plans to do, hard-working, team leader, etc.  Then through the answer you can present these selling points.  In the end you should conclude with your current situation.  You can craft a good answer and deliver it in interview within 2 – 3 minutes.  Here’s a sample answer:

Sample answer:


“With a Master’s degree in Computer Science from U.S. Berkley, I have been in IT industry for 4 years. I’ve gained extensive experience in all aspects of software engineering, including software design, systems architecture, application programming, and QA testing.  Currently I’m a software engineer with ADMSoft and have worked on several software development projects for our clients.  As the lead developer, I designed and developed a web-based food processing management system for RedFields Producers  to help the company increase productivity by 30%.  I’m skilled in the latest techniques such as web services and SOA, proficient with the new languages like C# and Rudy on Rail.  I’m always focused on building robust software systems to meet our customer’s needs.  One of my major strengths is that I work very hard and continually look for ways to provide the highest quality products and services while reducing the time and costs to complete the project.  I am self-motivated and enjoy working in a team environment. I’m looking for an opportunity to work for a growing company where I can contribute my hands-on experience and grow my career with the company.  By some research I found that yours is the type of companies I really want to work with.”

Describe a situation where you used your own initiative to solve a problem

This question is to test your decision-making abilities and your willingness to go extra miles when you face a problem.  You may prepare an example for describing how you solve a unexpected problem emphasizing initiative.

Sample Answer:


“I once received a last-minute request from the executive director to prepare a Crystal Report with the latest sales data that he needed to present on the committee meeting on that night.  I was facing two problems, one, the report was designed to extract data from the data mart but the data replication was done only every other night, thus we didn’t have the latest data in the data mart; second, my boss was on vacation, but she had asked that I run full offline backup that same afternoon. I have to make my own decisions to solve the conflict. I decided, of course, that the executive director’s request took priority.  In order to run the report with the latest data, I had to modify the report design a little bit to retrieve data from our production database.  At the some time I wouldn’t be able to take the database offline for full backup.  It took me 3 and half hours to put the report on the executive director’s hand.  He was very satisfied.  Then I just went out to grab something to eat and came back to the office around 7 pm to do the full backup.  It was a long day for me but I was very happy to get all the problems solved.”

How Do You Respond To Criticism?

A simple answer could be “I respond well to criticism because I want to learn from my mistakes.”  A better answer should go beyond that typical response and give it a more personal flair. Remember? tell a story.

Sample Answer:


“Well, to me there’s a difference between criticism and constructive criticism. No one responds well to criticism that is mean-spirited or unjustified. If I feel I am the target of such criticism, I try to find out the underlying cause.  Constructive criticism, on the other hand, is something I greatly value. I’m always eager to accept advice from colleagues; I feel it’s one of the best ways to learn and I am grateful to receive it.  In my current job, I designed a data validation engine on .NET platform.  In the first review meeting, I heard a lot of cristicsm.  Of course I didn’t take it personally but rather tried to find out what was behind it.  By talking to them individually, I found that they don’t have much opinion about my design, but doubt about choosing the .NET platform since it was a new technology to them.  Therefore I called second meeting to give them a detailed overview of the .NET platform and explained the advantages of using .NET for this project.  After they understood my technical approaches, my design was well accepted.”