DWBI IT companies' review for Laterals

You are a 7+ years dwbi professional and you are looking out for your next switch. Many industry pundits would argue like product based companies and consultancies have better jobs , both in terms of the CTC and the kind of work, In this article I would like to emphasize why an IT professional shall stick to essentially IT companies while switching and which IT companies excel in the DWBI space and why an IT professional shall consider switching\being into these IT companies.

Please note this is just an opinion and the information below is based on what we hear in cafe discussions. This is not legal advise :)

It's always better to be in the IT service industry as it offers what is most important -

1. Job security.
2. A wide range of projects to move into from time to time.
3. Ability to work overseas and in India accordingly.
4. Though product based companies do offer high packages, most of their projects are internal, the exposure of which is nothing when compared to that of traditional billable projects.

Mid sized IT companies generally hire 7-8 years experience IT professionals as Project manager and leads for once in a blue moon client projects which they are able to bag.The upside is that there is tremendous opportunity for you in terms of CTC and client interaction. You can own the entire project. The only downside is job security as it is a project specific hiring.

Some of the top players in dwbi domain globally are as follows:

1. Cognzant - Every dwbi professional should definitely consider this company to work for. The work experience earned here is globally recognized in the dwbi space.

2. Wpro - Many wpro alumni have moved in dwbi product leaders like Informatica and are running the show there.Great company to work in new technologies.

3. IBbM - Is a market leader in analytics and every other thing. There is no dearth of projects but again that could change as per the geography.

4. Acenture- Another very nice to company to work for. Takes care of its employees and provides good CTC. Good number of projects across all domains.

5. Infy - It's great to see infy in the new avtaar. This company has managed to put back leadership issues and is now going great guns. A good option to consider.

H1B visa interview experience

Date\Venue – 11^th December 2015 – US Embassy, New Delhi

You may consider reaching 45 minutes before your time slot. It would be a good idea, if someone would accompany you to the embassy and wait outside, so that you may handover your mobile phones to him.

The place is bit crowded but the crowd is extremely well managed and things at the embassy go smooth.

Check 1 – Outside the embassy-  I was carrying my original documents along with the DS-160,Application confirmation and Passport from the VAC.I was told to produce my I-797 and it was stapled along with my passport.

Check 2 – Security Check. Its similar to that of the airport. I had to take off my jacket and wallet, and kept all my stuff in the tray which underwent a scanner. Simultaneously the guard scanned me with the machine.

We were made to wait in a waiting area and the entire line was told to move to the next stage inside the embassy.

Check 3-The gates of the main section opened up and I could see the portraits of American leaders .Nervous energy occupied the air and we were made to submit our finger prints. An officer was validating the same in the system and after a long pause confirmed my fingerprints.

Now we entered the main area, I could see around 7 to 8 counters with visa officials interviewing the candidates. The description of the visa officers were as follows:

1.       The sweet guy – Wearing a bow tie and sweetly asked the questions.

2.       The rude guy – Was asking questions in an aggressive way.

3.       The shrewd silent guy – This person meant business and was silent but extremely observant.

4.       The normal guy – Was asking questions in a normal tone and pretended to be confused at times.

5.       A young lady

6.       An old lady

There were some candidates that had chosen to be interviewed in Hindi like athletes and the American visa officials were asking questions to them in Hindi. I was quite impressed seeing this.

There was an attendant that was assisting the crowd to go the counters as per availability was directed to the Normal guy’s counter but was asked to shift to the ‘shrewd guy’s counter as it had a vacant space.

I greeted the Visa Officer and handed over the Passport and the I-797 to him.

VO – Whom do you work for?

Me- said my Employer’s name (only)

VO-Who is the client you shall work for?

Me – There is no client, the petitioner is XX.

VO – Where shall you be going in the US

Me – City and State

VO – Can you tell me something about the internal project that you shall be working on?

Me – I shall be participating in all phases of the SDLC starting from requirements analysis,design….

VO – Interrupted me ….No please specifically tell me about the product you shall be working on.

Me – I got extremely nervous here , I guess the VOs tone made me more nervous.I told him” We are building a product in the informatica product suite, that shall read data from an application databse and load the data in the cloud” (just said this line , although I could have carried on explaining but I decided to stop at this point)

There was a pause of 2 seconds.As though the VO wanted to hear more, but I decided not to explain    un-necessarily unless required.

VO – Your visa is approved sir.

Me – I stood there for some white receipt, I had read about in journals on internet.But he did;t give me any.

VO – Your Visa is approved sir, have a nice day.

Me-Thank you.

I came out of the embassy and didn’t stop for the courier services. Was too excited to stop.

Questions regarding existing BI reports to Business Users.

• How do you access these existing reports?
• Whats the existing frequency of report updates and data updates?
• What reports go together to make them relevant?
• What other tools do you use for analyzing the data out of these reports?
• What are the report delivery mechanisms existing right now?
• Are these refreshed on a batch process or based on demand from the users?
• Any specific features in the existing report that you would like most and does not see loosing it which may cause potential business implications?
• What are the existing bottlenecks with each of these reports?
• Are the goals in existing reports still relevant?
• What are the different parameters used in these reports? Whats the frequency of each parameter being used?
• What reports do you cross check to make sure your reports are upto date and are valid?
• What reporting metrics/KPI are being created in these reports? Are there special formulae being set in these reports as we bring data from the data warehouse?
• How do you perform your routine analysis? How does the existing reports aid in getting your job done faster? Do you see any trends in what kinds of routine analysis are you being tasked with?
• How complicated can these ‘one off ‘ reports being created? How long does it take to create these reports? How often do you think the data is already available in data warehouse but not in reports?
• Who are the power users for each of these reports? Organizing these users by department, by task can give you a very good insight of what reports can be grouped together in an OBIEE dashboard.
• Is there report usage tracking mechanism already available? Do we like to see this moving further into OBIEE?
• Open up your c-level executive’s high level requirements and see whether they are being already covered by these existing reports.
• Is there a backlog of users requests for enhancing the existing reports, create a new flavor of report based on the existing report?
• Can all reports be viewed by every one? How are the users segmented so that groups can be created to access these reports?
• Does the report format change based on the user? Is there any row level security involved? Is there any column level security involved in these reports? Simply ask ‘Who can see what’ for each of these reports.
• Do you analyze information or conduct analysis quarter over quarter or year over year? How far back do you go to support your analysis?
• Ask for access to the recent support ticket logs and see whether there is any trend of problem areas with the reports.
• Organizing the reports by business importance i.e. mission critical every day, end of month report to be ready by 2nd fiscal week of next month etc. etc..
• What is the existing support structure for these BI reports? Ex: When a user has problem, he goes to his assigned power user, if power user cant resolve it, create a ticket for BI team.
• Identify the existing drill down capabilities in the existing reports. Is this still valid? How often does these hierarchies change? Ex: A Product Hierarchy is created every month, we create a new IT request to include this new product hierarchy and it gets added.
• Identify the common dimensions and hierarchies used in most of these reports. Ex: Time, Product, Customer
• Identify the common set of filters that are being built across all these dimensions.
• What are the known gotchas with the existing data in the data warehouse. Do we have reports where multiple versions of truth is always a problem just because the way these reports are set up?
• Are there any decodes, if this is ‘x’ then ‘y’ kind of expressions developed as part of the reports?
• Are we happy with the existing refresh schedules?
• What output formats are being supported by these reports? Ex: Excel, PDF, web-based always.
• Identify any enhancements that would really delight the customer based on an existing report.
• Identify opportunities where previous attempts failed and the it did not make any financial sense at that time. Review these with your users and see whether that it is still the same.
• Document on how each of these reports fits strategically to the organization’s goals and performance monitoring. What are the success factors for your organization? Are these factors quantifiable? How do you know you are on track to achieve your results? How often does the department’s/organization’s goals change?
• Does any of the existing reports help you predict problems? Are there any alerting mechanisms built into these reports? Who gets these alerts?
• Check the reports for the visualization techniques used in the existing reports. Does it make sense to replicate them as is or see whether you can add new web 2.0 widgets for data visualizations. Ex: There was never a goal against this KPI before. Now, we have one. We like a dial chart on the first page of our report to show how we are performing weekly, quarterly, yearly.
• Identify users and see what level of familiarity do they have with this data. Segment these users based on criteria like influence, authority, participation levels. You always need high influencial, high authoritative and high participative users. Deal slowly and patiently with low influential and highly pessimistic users.

Identify the BI technology trends in their organization. Ex: We used to have these nice and fancy all excel based reports. Then the IT has changed our strategy 5 years ago and asked us to start using Micro Strategy tool. Now, we are going to go OBIEE. Check the pulse of your users to see how excited they are. As the technology changed, did something worked better? Did something get lost as part of the transition. Look for those transition pain points and make sure you have/develop some strategies on how to deal with them if you encounter one.

Batch,Real Time and CDC -Data Extraction Techniques

1. Batch - {On-Demand, User initiated,Loss of transactional Lineage,Can be scheduled}
2. Real Time-{Maintains transactional Lineage based on architechture,Implemented to meet tighter SLAs}
3. CDC-Change Data Capture-Continuous extraction.Transactrional Lineage is maintained,Delete data is also captured in form of soft deletes.

-For CDC implementation we need to capture CreatedDate and LastModifiedDate of records

Difference between Kimball and Inmon approach

The Inmon approach is said to have the Hub and Spoke architecture. The primary feature of the same is the Enterprise Data Warehouse (EDW) which is maintained as a 3NF database. It is slightly different from the Operational database as it is:

1. Subject Oriented
2. Non-volatile - Data doesn't change . Soft delete is present by the use of timestamping.
3. Time variant - Maintains historical data
4. Integrated.

It does not have fact or dimensions, and has a set of functional data marts pulling data out of it.

OLTP  > EDW (3N) >Datamart (3N) >OLAP Cube

The Kimball approach is said to have the Bus Architecture. It contains set of stars i.e de-normalized groups of fact and dimension tables connected via conformed dimension (dimensions common to two facts)

OLTP  > Star Schema (Dimensional Model) >OLAP Cube

Definition of a DWH

A DWH is a

1. Subject Oriented i.e. Has the power to mould the transactional data around a particular subject.If we want to answer questions around the sales of a particular product, the DWH would be around the Subject-sales.etc.
2. Integrated i.e. Integrates data from various sources and keeps in form of dimensional model.
3. Non-volatile i.e. -Data once entered in DWH does not change.
4. Time variant - DWH maintains historical data.

collection of data.

Why is a DWH required in an organization?

1.       Data is an asset and has to be retained as per organizational needs.DWH helps us in maintaining          large volumes of data.

2.       To reduce IT involvement in report creation and helps the business to custom build the reports.

3.       MDM is used to maintain the single version of the truth.

Date Dimension

Why do we need a separate date dimension, if we can insert the date from the transnational data directly,as is?

A date dimension has 365 rows ,each row representing a particular date and a Date ID. This Date ID is maintained in the fact table rather than the date from the transnational table.The date dimension would have the following attributes:

1. Day of the week

2. Week of the month

3. Month

4. Year

5. Holiday indicator

6. Season {Thanksgiving,Christmas etc.}

7. Fiscal year (Financial year of US)

8. Fiscal month

9. Fiscal Week etc.

All these attributes are very relevant at report level, and could give a whole lot of information combined with the fact, as compared to a single date value coming from the transnational system.

Hierarchy for Date Dimension

Year > Month >Week>Day>Hour>Minute>Second.

Difference between Delta Load and Incremental Load

Scenario:

We have completed the first load into the target table, and wish to implement the Delta load and the Incremental load.

• Delta Data is the parent of Incremental Data.
• What is Delta Data?
• Assuming that in our scenario, the first load happened on 1-Jan-2015. The data present in the source on 2-Jan -2015 is the delta data. 
• How to read delta data?
• Add the date filter in the source query i.e. WHERE load_date>1-Jan-2015
• What is incremental data?
• The data that we have read , as delta data, needs to be compared with the target data for key columns. This is generally done through SCD(Slowly Changing Dimensions) algorithms. Incremental data is the changed or new data found while the delta and target data are compared via SCD algorithms.

MSBI - SSIS,SSRS,SSAS - Interview experience-HCL

DWH and SSAS

1. How is Surrogate key different from Primary key? - It is independent of business data.
2. What is a conformed dimension? - Dimension common to more than one fact table
3. Whats is a degenerate dimension? - Dimension too small to be made into a table, hence included in fact. eg: Attendance, Boolean values.
4.Junk Dimension?- Grouping meaningful flags and texts in a single dimensional table.
5. Aggregate fact table use? fact table containing pre-calculated value,that feeds the report for faster results.
6. SCD? -
Slowly changing dimension 01 - update
Slowly changing dimension 02 - update and insert - History maintained
Slowly changing dimension 03 - First and Latest record only.
7. In a bottom up approach we always have a datamart at the bottom then the DWH and then OLAP at topmost.
8. What is a cube? - Data modelling technique to do multidimensional analysis.
9. What is a Hypercube?-To analyse more than 3 dimensions using a cube,the dimensions are merged representing single edge of the cube.Such cubes are called hyper cubes.
10.What is faster ROLAP or MOLAP? - MOLAP it is faster and is more complex as well.Supports Slice and Dice features which are not supported in ROLAP.
11.What is Slice and Dice feature of cube? - If each edge represents a single dimension of the cube, the page displayed is a slice or 2-dimensional plane of the cube.With each rotation the dimensions on the edges of the cube changes and so does the plane giving user the liberty to view and analyse the data from different angles.
12. Difference between relational model and dimensional model?

SSIS

1. What is the difference between Merge,Merge Join and Union all?
2. How can we trigger a SSRS report once a job in SSIS is completed? --sp_start_job
3. Name some native connections in SSIS? - ADO.NET,OLEDB,flatfiles,excel
4.Difference between Fuzzy lookup and lookup?
5. Container Types ={For each loop container,For loop container,Sequence container,task host}

SSRS
1. Difference between SSRS and Crystal reports.
2. How caching works in SSRS reports?
3. Deployment of SSRS in Native and SharePoint modes?
4. Is it safe to cache the report in report manager if we are required to save the credentials? - Yes as the credentials are starred.

DataWarehouse and Data Marts

Data Warehouse (Top – down approach)

• -          Union of all the data mart
• -          Data Received from staging area
• -          Corporate enterprise wide data
• -          Data Received from staging area

Data Marts (Bottom up approach)

• -          Departmental view of data
• -          Star Join (Facts and Dimension)
• -          A single business process

New Informatica Interview Questions

1.       Name some Informatica Application Services

·         Repository Service

·         Integration Service

·         Data Integration Service

·         Metadata repository service.

·         Analyst Service.

2.       What is a Service Manager?

·         The service manager is the main service of Informatica which is responsible for running of other application services. When we start the Informatica services on the node, we are actually starting the Service manager. Default port 6005.

3.       From where can we call SQL functions in Informatica.

·         Pre/Post session level SQL

·         Source Qualifier override.

4.       What is the minimum number of ports required to Install Informatica.

·         6000 to 6113.

5.       Name the environment variables required to be set at Informatica level.

·         TNS_ADMIN

·         ORACLE_HOME

·         ORACLE_BASE

·         LD_LIBRARY_PATH

·         NLS_LANG

6.       Can you implement High Availability Architecture without a grid?

·         Yes, by adding nodes as primary and backup nodes under the same domain.License should have HA property.

7.       How do you configure a WSDL connection at session level?

·         As an application connectionà WSDL consumer-àAuthentication type Basic.

8.       Should the client and server for Informatica , be of the same version?

·         Yes

9.       What is Target Load Plan?

·         Please study from the guide and understand that the load can be prioritized even if the pipes are originating from the same source.

10.   How to switch off/start Informatica services?

·         Informatica/9.5.1/tomcat/bin infaservice.sh startup/shutdown.

11.   Give an example of usage of shell scripts in pre-post session.

·         Used to FTP the flat file on a particular folder in the unix box from where informatica could read it.

·         Do a validation check, pre-post count check of the feed being processed.

Informatica Opportunities in Delhi NCR

Healthcare
1. Stryker-Gurgaon -Dev and Prod support projects
2. United Health Group-Gurgaon/Noida-Dev and Prod support projects

Capital Markets
1. Headstrong-Morgan Stanley clients-Noida-Prod support projects
2. American Express-Gurgaon-Dev and Prod support projects
3. Black Rock-Gurgaon-Prod support projects
4. Fidelity

Miscellaneous
1. CentureyLink-Noida (Telecomm)
2. Deloitte
3. Expedia
4. ZS Associates

IT Services
1. HCL
2.Accenture
3.TCS
4.Sapient
5.IBM

Headstrong- Company Review

Pros - Had once been the destination of elite brains from top engineering and management colleges from India.Some of which have decided to stay back.Good development projects in healthcare and capital markets domain.A culture for learning of domain.Notice period 2 months.

Cons- The company has been exponentially degrading after Genpact acquisition. Most of the old staff have resigned . The company is loosing its brand name and vision to that of Genpact which has a very conservative approach.Will not suggest people to join the company.

iGATE Global Solution - Company Review

Pros - Excellent onsite opportunities at junior level.

Make sure you are clear on the account for which you are being hired. It is okay to ask for the account eventif you are a trainee and are being interviewed for Project. Make sure you land up in the Royal Bank of Canada account.It has maximum long term onsite opportunities provided you hang around for 2 years.Learn to lick some arse for quick results.Do not expect a change of account , once you join a project in a specific account you'll be there for eternity.

Friendly environment.- iGATE has a very friendly environment. The igate Rubber band rocks!!

You will have an opportunity to look into end to end implementation of systems here. Something which you might not get to see in big companies. Good technical learning.

Cons

Cheap corporate politics-exists everywhere.
Below industry standard compensation.

The key to thrive in igate is not only work, but be assertive. Managers are non-technical and depend on resources to fulfill the job. Go ahead and bully the managers, they are meek.If you are good at your job they will not mind pleasing you :)

Informatica Interview Experience : Accenture

Experience Level- 5 years - Team Lead/Technical lead.
Location- Bangalore
Date- Dec 2013


Please make sure to make a note of the number that the HR uses to communicate to you.

The HR scheduled a telephonic interview which lasted for 30 minutes.

Round -01- Technical Skills round

The person called from Accenture Hyderabad and asked questions around my experience.

1. Informatica Administrator Grid implementation
2. The kind of services to be configured  required for IDQ - Model RS,Content Management RS and Data Integration Service.
3. Implementation of SCD-2. Full explanation on the call
4. How to use MD5 function in the expression while using dynamic lookup.
5. How does a lookup outperforms joiner- The dynamic lookup functionalty cannot be implemented by a Joiner.
6.Difference between PC 8.x and PC 9.x
7. Informatica connectivity with sales force. - Property of the license,Designer>Import Source >Salesforce object.

He questioned me for 30 minutes, did not waste a single second. You can ask for a feedback at the end of the interview.

Round 02- HR round

After my telephonic HR round I got a mail against my candidature ID saying I have cleared the technical round and shall soon be contacted for the HR round.

The HR called me up after 3 days for a quick HR discussion.He will capture basic details about your job , make sure ti answer the questions correctly.Tell the kind of role expectations you have from accenture.Do some research work about the kind of profile and package you should ask for.He told me that the HR team shall contact me shortly.

Still waiting to hear from the HR :D

Informatica interview Experience - Headstrong Bangalore

Experience Level- 2 years
Location- Bangalore
Date- Jan 2011


The interview call was prompt. There were two technical rounds and one Managerial round.

Technical Round 01- Conceptual and Scenario based.

Difference between Group by and Having
Unix shell scripting asked. ow to fetch the topmost and last row.
Informatica Scenarios asked.

Technical Round 02- Theorotical Informatica Concepts-Easy

Managerial  Round- You need to sow that you are eager to join the company. Ask questions about the client and project. Headstrong Banagalore has some very good i-bank clients like Morgan Stanley,Wells Fargo,Gold Sachs (client location),Scotia and they are trying to grow their Healthcare vertical too.

Ask for 50% hike in package.

Informatica Interview Experience- J.P. Morgan

Experience Level- 2 years
Location- Bangalore
Date- Jan 2011

Round 1- Technical Telephonic interview.

1. Details of the project.
2. The person asked about the shell scripts that I used in the project.
3. I had to go on to the extent of explaining the algorithm of the shell scripts.
4. Why are they used? Their relevance to my project.
5. Functional aspect of the project.
6. I was asked if I will be comfortable with a junior designation since in J.P. people with 5 year exp are software engineers.

Round 2

The HR had invited me for the 2nd round F2F after 10 days, by that time I had joined headstrong.

Informatica Interview Experience- Fidelity Gurgaon

Location: Fidelity-Gurgaon
Event: Walk-In
Exp level 3-8 years.
Date: Sep 2012


Please read about the Fidelity-FLS(Gurgaon) it is not the same as Fidelity FMR (Bangalore)
The HR would expect you to know the difference.
Good procedure.The will contact you only through the placement agency.However , there was an instance where candidates were invited for a different technology and it was a bad experience for them.
Make sure that you are invited for your technology.

Round 1- Written round. 40 Questions spread across Informatica Powercenter development and transformations,Unix shell scripting and sql. Mainly transformation.Level was good , but not very tough. Straight forward questions.

Round 2- Takes some time - an HR and a Senior manager discussion

To get selected, you can say that you live in gurgaon or close by. Indicate that you dont have problem traveling to Gurgaon. They give cab facility so you can quote the same.
Be sharply dressed in formals, preferably wear a tie.
The HR will grill you, they are just testing your temperament.
They will like to know you on the personal front. Do not indicate if someone in your family is ill.

Psychological Check- How will you define the performance of 5 horses running in a race course.
They expect you to be analytical. Factors like the horses will tire after 1 lap.The  horse at the back might overtake due to lesser wind resilience in 1st round.The climate and race-course conditions will be a determining factor.

What is the toughest situation you have faced in your career till now.?

Round 3- If you survive the above round a technical round will be conducted.Scenario based questionsIf you prepare from the scenarios available on the net, you can easily crack it.Only Informatica asked.The job-role will be more on the production support side.

They will not make the offer there and then and will get back to you. 

Informatica Interview Experience- TCS

Location: TCS-Noida
Event: Walk-In
Exp level 3-8 years.
Date: Nov 2013

1. You need to register to the career site of TCS through the portal.
2.  Take a printout and be at the mentioned venue.
3. You will be given a form to be filled up by the HR.
4.Quote expected salary to be 30% of you are getting. TCS is not a very good pay master.


Interview panel

The venue was quite mismanaged.There were in all three evaluators.
Two technical and 1 for soft skills and HR based questions.
Some questions were as follows:

1. OLTP vs OLAP ?
2. SCD 1,2,3
3.Difference between Surrogate key and Primary key (Answer-Surrogate key is independent of the data)
4.Scenario based questions (rows to columns conversion)
5.Explanation of your projects. (This is the main part where you can guide the interview)