MSBI REAL TIME INTERVIEW QUESTIONS AND ANSWERS : SSAS REAL TIME INTERVIEW QUESTIONS AND ANSWERS

SSAS

1. What is new with the Analysis Services (SSAS) in SQL Server 2005?

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.

• Supports up to 16 instances of Analysis Services Service.

• As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.

2. How to schedule SSAS Database backup.

Steps to schedule SSAS database backup:

• Open SSMS

• Right click the Job folder of the SQL Agent node and choose New job.

• Enter a job name.

• Go to steps page and click New button.

• Enter a step name and select SSAS command as job type.

• Enter the Server name and the following command

•

<DatabaseID>DatabaseName</DatabaseID>

</Object>

<File> DatabaseName.abf</File>

<Password>password</Password> </Backup>

3. What is SQL Server 2005 Analysis Services (SSAS)?

SSAS gives the business data an integrated view. This integrated view is provided by combining online analytical processing (OLAP) and data mining functionality................

4. What are the new features with SQL Server 2005 Analysis Services (SSAS)?

It offers interoperability with Microsoft office 2007. It eases data mining by offering better data mining algorithms and enables better predictive analysis...................

5. Explain the purpose of synchronization feature provided in Analysis Services 2005.

Synchronization feature is used to copy database from one source server to a destination server. While the synchronization is in progress, users can still browse the cubes.................

6. What is slicing and dicing in business objects?

Slicing and dicing of business objects is used for a detailed analysis of the data. It allows changing the position of data by interchanging rows and columns.............

7. Data Sources

Data source is where the data comes from in data warehousing. The data collected from various sources and is cleaned.............

8. Fact Tables

Data in a warehouse comes from the transactions. Fact table in a data warehouse consists of facts and/or measures. The nature of data in a fact table is usually numerical. e.g. If I want to know the number..............

9. Database roles

Database level roles are used to manage the security of the database. The role can be either fixed or flexible...............

10. What are fact tables?

A fact table is a table that contains summarized numerical (facts) and historical data.

This fact table has a foreign key-primary key relation with a dimension table. the fact table maintains the information in 3rd normal form.

11. Types of facts?

1. Additive:-able to add the facts along with all the dimensions

-discrete numerical measures.

-Ex:-retail sales in $

2. Semi additive:-snapshot taken at a point in time

- Measure of intensity

-not additive along time dimensions

ex:-account balance, inventory balance

3.non-addItive:-numerical measures that can't be added across any dimensions.

-Intensity measure arranged across all dimension

ex:-room temperatures, averages.

12. Data warehouse?

A data ware house is a collection of data marts representing historical data from diff operational data sources (OLTP).

The data from these OLTP are structured and optimized for querying and data analysis in a data warehouse.

13. Data mart?

A data mart is a subset of a data warehouse that can provide data for reporting and analysis on a section, unit or a department like sales dept, hr dept.

14. Diff b/w star schema and snowflake?

STAR SCHEMA SNOWFLAKE

_________________________________________________________________________

1.centrally located fact table 1.centraly located fact table

surrounded by de normalized surrounded by the normalized

Dimensions. Dimension table.

2. All dimensions will be link 2.all dim link with each other (or)

directly with fact table. 1- N relationship with other table.

3. It is easy to understand by 3.It is diff to understand.

End user or tech people

4.We can easily retrieve data parsing 4.It is diff to retrieve the data while

the query against the facts n dim

By passing the simple queries.

5. Increase the query 5.more joins.

performance because It involve less

Joins.

15. What are Star Schema and Snowflake schema? Which is better ?

Star Schema:

The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.

Example of a star schema would be Sales Fact Table with foreign key id’s such as Product id and Location id. Product Table would be the dimension table with the attributes of the product in that table. Location Master would be the other dimension table .

Snowflake Schema:

The snowflake schema is a variation of the star schema used in a data warehouse.

The snowflake schema (sometimes called snowflake join schema) is a more complex schema than the star schema because the tables which describe the dimensions are normalized.

Example of snow flake Schema: Using the above example mentioned for star schema if product master is further normalized to two tables. For example product master would be one table contain product category id .The product category master would have the product id as the foreign key and product category id as the primary key.

Star schema is better for data warehousing and snowflake schema is better for transaction systems as in star schema the data retrieval is faster due to lesser joins and snowflake reduces redundancy and better suited for Transactions.

16. Staging area?

It is a temporary data storage location, where various data t/r activities take place.

17. Fact grain(granularity)?

The grain of fact is defined as the level at which the fact information is stored in a fact table.

18. What is a fact less fact table?

The fact table which does not contain facts is called as fact table.

Generally when we need to combine two data marts, then one data mart will have a fact less fact table and other one with common fact table.

19. Which port no ssas uses?

20. What is the difference between SQL Server 2005 and 2008?

21. Features and concepts of Analysis Services

22. What is Analysis service repository?

23. What is Analysis Services? List out the features?

24. What is UDM? Its significance in SSAS?

25. What is the need for SSAS component?

26. Explain the TWO-Tier Architecture of SSAS?

27. What are the components of SSAS?

28. What is FASMI ?

29. What languages are used in SSAS ?

30. What are Data Sources and Data Source Views?

31. What is a named Calculation?

32. What is a named Query?

33. What is a metadata?

34. What is Many-Many relationship in SSAS?

35. What is the difference between SSAS 2005 and SSAS2008?

36. What is star, snowflake and star flake schema?

37. What is SSAS processing task ?

38. What is Performance Tuning ?

39. Difference b/w SSAS and AS 2000.

40. What is the difference between Star schema and Snowflake schema?

41. What is star schema explain in real time which u done in your project?

42. What languages are used in SSAS?

43. Tell me some scenarios in your project where u use star schema and how u implemented it?

44. Tell me scenarios how u used snowflake schema in your project?

43. How Primary keys and foreign keys are linked amongst tables in Star schema consider employee table and explain?

OLAP & OLTP

1. OLAP database objects

The following are the OLAP database objects:

Cubes: Data in cubes are persisted in a summarized version that helps to analyze data quickly. The data is persisted, through which reporting can be done easily.................

2. Explain the concepts and capabilities of OLAP.

Online analytical processing performs analysis of business data and provides the ability to perform complex calculations on usually low volumes of data. OLAP helps the user gain an insight on the data.............

3. Explain the functionality of OLAP.

Multidimensional analysis:- OLAP helps the user gain an insight on the data coming from different sources. OLAP helps faster execution of complex analytical and ad-hoc queries...............

4. What are MOLAP and ROLAP?

Multidimensional Online Analytical Processing and Relational Online Analytical Processing are tools used in analysis of data which is multidimensional..................

5. What is Hybrid OLAP?

In a Hybrid OLAP, the database gets divided into relational and specialized storage. Specialized data storage is for data with fewer details while relational storage can be used for large amount of data..............

6. Explain the shared features of OLAP.

OLAP product by default is read only. If multiple access rights are required, admin needs to make necessary changes..................

7. Compare Data Warehouse database and OLTP database.

Data Warehouse is used for business measures cannot be used to cater real time business needs of the organization and is optimized for lot of data, unpredictable queries. On the other hand, OLTP database is for.................

8. Diff b/w OLTP AND OLAP?

OLTP OLAP

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1.transactional processing 1.query processing

2.time sensitive 2.history oriented

3. Operator & clerks view 3.Managers, CEOs, PM’s views

4. Organized by transaction 4.organized by subjects

(Order, input, inventory) (product, customer)

5.relatively smaller DB 5.large DB size

6.volatile data 6.non-volatile

7.stores all data 7.stores relevant data

8. Not flexible 8.flexible

9. What is the difference between ETL tool and OLAP tool?

ETL is the process of Extracting, loading and transforming data into meaningful form. This data can be used by the OLAP tool for to visualize data in different forms. ETL tools also perform some cleaning of data..................

10. What is the difference between OLAP and DSS?

Data driven Decision support system is used to access and manipulate data. Data Driven DSS in conjunction with On line Analytical Processing.....

11. What is OLAP?

OLAP stands for online analytical processing. It uses databases tables (fact and dimension table) to enable multi dimensional viewing, analysis and querying of large amount of data.

12. What is OLTP?

OLTP stands for online transactional processing. Except data warehouse databases the other databases are OLTP.

These OLTP uses normalized schema structure. These OLTP databases are designed for recording the daily operations and transactions of a business.

13. What are the fixed measure and calculated measure?

Normally we used fixed measures in SSIS mainly for calculating measures.

Where as calculated measures uses in SSAS, while creating cube we can mention this cmeasure in the OLAP.

14. What are measures?

a) Measures are numeric data based on columns in a fact table.

15. How do you improve performance of a SSAS OLAP DB?

16. What is the diff b/w OLAP AND OLTP?

17. What are various storage modes of OLAP?

Cubes:

Cubes A data cube stores data in a summarized version which helps in a faster analysis of data. The data is stored in such a way that it allows reporting easily................

1. What are cubes?

Cubes are data processing units composed of fact tables and dimensions from the data warehouse. They provided multidimensional analysis.

2. What are virtual cubes?

These are combination of one or more real cubes and require no disk space to store them. They store only definition and not the data.

3. After process the cube, if I want to see the data in iexplore then what can I do?

4. How Cubes are implemented in SSAS?

5. While creating a new calculated member in a cube what is the use of property called non-empty behavior?

1. What is your approach when you need to do changes in the cube on customer request?

7. How many cubes your product has?

8. Explain your cube in detail.

9. What are the types of cube processing?

10. What are the basic things that you consider before designing a cube?

11. What are various tabs in cube?

12. What is Perspective Cube?

13. What is CUBE and why we implement it and how it uses in your project explain in brief with real-time example u performed in your project?

14. How can identify, how many measures are in cubes through AMO?

KPIS:

1. What are perspectives?

2. Explain about calculated members and drill through actions?

Partitions:

1. What is a partition?

2. Why we are creating partitions? Is it improves performance?

Dimensions:

1. What are dimensions?

Dimensions are categories by which summarized data can be viewed. For example a profIt summary fact table can be viewed by a time dimension.

2. What are conformed dimension?

The dimensions which are reusable and fixed in nature. Example: customer, time, geography dimensions.

3. Confirmed Dimension?

4. What is Degenerated dimensional table?

5. What are role-playing dimensions?

6. What are the different relationships used in dimension usage?

7. How do you process a single partition without processing other dimensions and measures in production?

8. What is fact-less-fact dimension? When do you use such dimension?

9. How can I setup default dimension member in Calculation script?

MSBI REAL TIME INTERVIEW QUESTIONS AND ANSWERS

Pages

Tuesday, 12 January 2016

SSAS REAL TIME INTERVIEW QUESTIONS AND ANSWERS

2 comments: