Interview Questions.

Q1. What Is Attribute Relationships, Why We Need It?

Attribute relationships are the manner of telling the analysis carrier engine that how the attributes are associated with every different. It will help to relate  or extra  attributes to every other.Processing time might be reduced if proper relationships are given. This will increase the Cube Processing performance and MDX query overall performance too.

In Microsoft SQL Server Analysis Services, attributes within a measurement are continually related both at once or indirectly to the important thing characteristic. When you define a size based on a star schema, that is wherein all size attributes are derived from the same relational desk, an characteristic relationship is mechanically described between the important thing attribute and every non-key characteristic of the size.

When you outline a size based on a snowflake schema, that's where measurement attributes are derived from multiple related tables, an attribute relationship is robotically described as follows:

Between the important thing characteristic and every non-key attribute bound to columns inside the predominant measurement table.

Between the key attribute and the attribute bound to the foreign key inside the secondary table that links the underlying size tables.

Between the attribute sure to foreign key within the secondary desk and each non-key attribute certain to columns from the secondary desk.

Q2. What Is The Maximum Size Of A Dimension?

The most length of the size is four gb.

Q3. What Is Dimension Table?

A dimension desk carries hierarchical information by which you’d like to summarize. A size table consists of unique enterprise statistics, a dimension table that consists of the unique name of every member of the measurement. The name of the size member is referred to as an “characteristic”

The key characteristic inside the measurement must include a completely unique price for every member of the size. This key attribute is called “primary key column”

The number one key column of every size table similar to the one of the key column  in any related reality table.

Q4. What Is Measure Group, Measure?

Measure agencies : These degree companies can contain distinctive dimensions and be at specific granularity  however as long as you version your cube efficaciously, your users might be able to use measures from every of those measure groups of their queries without difficulty and without stressful about the underlying complexity.

Creating multiple degree organizations : To create a brand new measure organization inside the Cube Editor, visit the Cube Structure tab and proper-click on at the cube call within the Measures pane and pick ‘New Measure Group’. You’ll then want to select the truth desk to create the degree organization from after which the new degree group may be created; any columns that aren’t used as overseas key columns inside the DSV will robotically be created as measures, and you’ll also get a further measure of aggregation kind Count. It’s a great idea to delete any measures you aren't going to use at this level.

 Measures :  Measures are the numeric values that our customers want to mixture, slice, cube and otherwise examine, and as a result, it’s critical to make sure they behave the way we need them to. One of the fundamental motives for using Analysis Services is that, unlike a relational database it permits us to build into our dice layout enterprise regulations approximately measures: how they must be formatted, how they should combination up, how they have interaction with specific dimensions and so forth.

Q5. What Is Data Mart?

A facts mart is a subset of an organizational statistics save, generally orientated to a particular motive or important facts subject that may be allotted to support enterprise needs. Data marts are analytical data shops designed to awareness on specific enterprise capabilities for a specific network inside an company.

Data marts are frequently derived from subsets of statistics in a facts warehouse, although inside the backside-up information warehouse design methodology the facts warehouse is constituted of the union of organizational facts marts.

They are three kinds of facts mart they're:

Dependent

Independent

Logical facts mart

Q6. What Is Use Of Attribute Hierarchy Ordered ?

Attribute Hierarchy Ordered: Determines whether or not the related characteristic hierarchy is ordered. The default cost is True. However, if an characteristic hierarchy will not be used for querying, you can save processing time via changing the value of this assets to False.

Q7. How Will You Make An Attribute Not Process?

By selecting  “ AttributeHierarchyEnabled = False”, we will make an  characteristic no longer in system.

Q8. What Is The Minimum And Maximum Number Of Partitions Required For A Measure Group?

In 2005 a MAX of 2000 partitions can be created in line with measure organization and that restrict is lifted in later versions.

In any version the MINIMUM is ONE Partition in line with degree organization.

Q9. What Is Star, Snowflake And Star Flake Schema?

Star schema: In megastar schema reality table can be immediately connected with all dimension tables. The superstar schema’s dimensions are denormalized with each dimension being represented via a unmarried table. In a star schema a central fact table connects some of person dimension tables.

Snowflake: The snowflake schema is an extension of the megastar schema, in which each factor of the celebrity explodes into more points. In a star schema, every measurement is represented by a unmarried dimensional desk, while in a snowflake schema, that dimensional table is normalized into a couple of research tables, every representing a stage in the dimensional hierarchy. In snow flake schema fact table will be linked at once as well as there can be some intermediate measurement tables between fact and size tables.

Star flake: A hybrid shape that consists of a aggregate of megastar(denormalized) and snowflake(normalized) schema’s.

Q10. What Is Hierarchy, What Are Its Types And Difference Between Them?

A hierarchy is a very essential a part of any OLAP engine and lets in users to drill down from  precis tiers hierarchies constitute the manner person assume to explore facts at greater specific stage

hierarchies  is made from multipule stages creating the shape primarily based on cease user requirements.

->years->region->month->week ,are all the ranges of calender hierarchy

They are 2 forms of hierarchies they are

Natural hierarchy

Unnatural hierarchy

Natural hierarchy: This means that the attributes are intuitively related to one another. There is a clean relationship from the top of the hierarchy to the bottom.

Example: An example of this would be date: 12 months, sector and month follow from every different, and in component, define each other.

Unnatural hierarchy: This manner that the attributes aren't without a doubt associated.

Example: An instance of this is probably geography; we may additionally have usa -> nation -> metropolis, however it is not clean wherein Province might sit.

Q11. After Creating The Cube, If We Added A New Column To The Oltp Table Then How You Add This New Attribute To The Cube?

Just open the datasourceview and on proper click on we discover the choice REFRESH. Click the REFRESH then it'll add new attributes to the table which can be added to Cube.

Q12. What Are Key, Name And Value Columns Of An Attribute?

Key column of any characteristic: Contains the column or columns that represent the key for the characteristic, that is the column within the underlying relational table in the information source view to which the attribute is sure. The cost of this column for each member is exhibited to users unless a fee is targeted for the NameColumn assets.

Name  column of an attribute: Identifies the column that offers the name of the attribute this is displayed to customers, rather than the cost inside the key column for the attribute. This column is used while the important thing column price for an characteristic member is cryptic or no longer in any other case useful to the consumer, or whilst the key column is based totally on a composite key. The NameColumn assets isn't utilized in determine-infant hierarchies; as an alternative, the NameColumn assets for infant members is used because the member names in a parent-infant hierarchy.

Value columns of an attribute: Identifies the column that gives the price of the characteristic. If the NameColumn element of the characteristic is designated, the identical DataItem values are used as default values for the ValueColumn detail. If the NameColumn element of the attribute is not particular and the KeyColumns series of the characteristic consists of a unmarried KeyColumn detail representing a key column with a string statistics kind, the equal DataItem values are used as default values for the ValueColumn detail.

Q13. What Are Translations And Its Use?

Translation: The translation feature in analysis provider lets in you to show caption and attributes names that correspond to a selected language. It helps in offering GLOBALIZATION to the Cube.

Q14. What Is A Data Source Or Ds?

The statistics supply is the Physical Connection statistics that analysis service uses to connect with the database that host the information. The statistics source carries the connection string which specifies the server and the database website hosting the records in addition to any vital authentication credentials.

Q15. What Is Scd (slowly Changing Dimension)?

Slowly converting dimensions (SCD) decide how the ancient adjustments inside the size tables are dealt with. Implementing the SCD mechanism allows customers to realize to which category an item belonged to in any given date.

Q16. What Are The Types Of Database Schema?

They are 3 kinds of database schema they may be

Star

Snowflake

Starflake

Q17. What Are Types Of Storage Modes?

There are 3 trendy storage modes in OLAP programs

MOLAP

ROLAP

HOLAP

Q18. What Is Attribute Hierarchy?

An characteristic hierarchy is created for every attribute in a size, and each hierarchy is to be had for dimensioning fact statistics. This hierarchy consists of an “All” level and a element stage containing all individuals of the hierarchy.

You can organize attributes into person-defined hierarchies to offer navigation paths in a dice. Under sure instances, you can need to disable or disguise a few attributes and their hierarchies.

Q19. What Is Use Of Attribute Hierarchy Optimized State?

Attribute Hierarchy Optimized State:

Determines the extent of optimization implemented to the characteristic hierarchy. By default, an attribute hierarchy is FullyOptimized, because of this that Analysis Services builds indexes for the characteristic hierarchy to improve query overall performance. The different alternative, NotOptimized, manner that no indexes are built for the attribute hierarchy. Using NotOptimized is beneficial if the attribute hierarchy is used for purposes other than querying, because no extra indexes are built for the attribute. Other uses for an characteristic hierarchy may be supporting to order another attribute.

Q20. What Is Amo?

The full shape of AMO is Analysis Management Objects. This is used to create or alter cubes from .NET code.

Q21. What Are The Difficulties Faced In Cube Development?

Giving attribute relationships

Calculations

Giving measurement usage (many to many dating)

Analyzing the necessities

Q22. What Is Data Source View Or Dsv?

A statistics source view is a continual set of tables from a facts supply that deliver the facts for a particular cube. BIDS additionally includes a wizard for developing information supply views, which you could invoke by means of right-clicking at the Data Source Views folder in Solution Explorer.

Datasource view is the logical view of the records within the records supply.

Data source view  is the only element a cube can see.

Q23. How Will You Add A Dimension To Cube?

To add a size to a dice observe those steps.

In Solution Explorer, right-click on the dice, and then click on View Designer.

In the Design tab for the dice, click on the Dimension Usage tab.

Either click the Add Cube Dimension button, or right-click everywhere at the work floor  after which click Add Cube Dimension.

In the Add Cube Dimension conversation field, use one of the following steps:

To add an existing size, pick the measurement, after which click OK.

To create a brand new size to add to the cube, click New measurement, and then comply with the steps inside the Dimension Wizard.

Q24. How You Provide Security To Cube?

By defining roles we offer safety to cubes. Using roles we are able to limit users from gaining access to constrained facts.

Procedure as follows: 

Define Role

Set Permission

Add suitable Users to the function

Q25. What Is Partition, How Will You Implement It?

You can use the Partition Wizard to outline partitions for a measure group in a cube. By default, a unmarried partition is defined for each degree group in a cube. Access and processing overall performance, however, can degrade for massive partitions. By creating a couple of partitions, every containing a portion of the statistics for a degree organization, you could enhance the get entry to and processing overall performance for that measure institution.

Q26. What Is Factless Fact Table?

This may be very important interview query. The “Factless Fact Table” is a table which is similar to Fact Table besides for having any degree; I imply that this desk simply has the hyperlinks to the size. These tables permit you to track activities; indeed they are for recording activities.

Factless truth tables are used for tracking a technique or amassing stats. They are referred to as so because, the fact table does now not have aggregatable numeric values or facts. They are mere key values with regards to the size from which the stats can be amassed

Q27. How Many Types Of Relations Are There Between Dimension And Measure Group?

They are six relation among the size and degree institution, they may be

No Relationship

Regular

Refernce

Many to Many

Data Mining

Fact

Q28. What Is Surrogate Key?

A surrogate key's the SQL generated key which acts like an exchange number one key for the desk in database, Data warehouses commonly use a surrogate key to uniquely discover an entity. A surrogate isn't always generated by way of the user however through the system. A number one difference between a number one key and surrogate key in few databases is that primarykey uniquely identifies a document even as a Surrogatekey uniquely identifies an entity.

Ex: An employee can be recruited earlier than the yr 2000 even as another worker with the equal call may be recruited after the yr 200@Here, the primary key will uniquely discover the file even as the surrogate key might be generated by means of the gadget (say a serial wide variety) for the reason that SK is NOT derived from the statistics.

Q29. How Many Types Of Dimensions Are There And What Are They?

They are three forms of dimensions:

verify measurement

junk size

degenerate characteristic

Q30. What Is Use Of Attributehierarchyenabled?

AttributeHierarchyEnabled: Determines whether or not an attribute hierarchy is generated with the aid of Analysis Services for the attribute. If the characteristic hierarchy is not enabled, the attribute can not be utilized in a user-defined hierarchy and the characteristic hierarchy can not be referenced in Multidimensional Expressions (MDX) statements.

Q31. What Are Types Of Scd?

It is a concept of STORING Historical Changes and whilst ever an IT guy finds a brand new way to store then a new Type will come into image. Basically there are three kinds of SCD they're given underneath

SCD type1

SCD type2

SCD type3

Q32. What Is Perspective, Have You Ever Created Perspective?

Perspectives are a manner to lessen the complexity of cubes by using hidden elements like degree businesses, measures, dimensions, hierarchies and so on. It’s not anything but slicing of a cube, for ex we are having retail and sanatorium statistics and stop user is subscribed to look most effective medical institution facts, then we will create attitude in keeping with it.

Q33. What Is Database Dimension?

All the scale which can be created the use of NEW DIMENSION Wizard are database dimensions. In different words, the size which can be at Database level are referred to as Database Dimensions.

Q34. What Are The Types Of Processing And Explain Each?

They are 6 varieties of processing in ssas ,they're

Process Full

Process Data

Process Index

Process Incremental

Process Structure

UnProcess

Q35. What Is Attribute?

An characteristic is a specification that defines a property of an item, element, or report. It may additionally talk to or set the specific value for a given instance of such.

Q36. Explain The Flow Of Creating A Cube?

Steps to create a cube in ssas:

Create  a information source.

Create a datasource view.

Create Dimensions

Create a dice.

Deploy and Process the cube.

Q37. What Are The Difference Between Data Mart And Data Warehouse?

Datawarehouse is entire facts where as Data mart is Subset of the identical.

Ex: All the corporation facts may also related to finance branch, HR, banking dept are saved in records warehouse where as in information mart most effective finance information or HR branch records may be saved. So facts warehouse is a collection of various facts marts.

Q38. What Is The Use Of Attributehierarchyvisible ?

AttributeHierarchyVisible : Determines whether the attribute hierarchy is seen to customer applications. The default value is True. However, if an characteristic hierarchy will no longer be used for querying, you could keep processing time by using changing the cost of this assets to False.

Q39. What Is A Cube?

The primary unit of garage and evaluation in Analysis Services is the dice. A dice is a set of records that’s been aggregated to permit queries to return facts fast.

For example, a dice of order records might be aggregated by means of time period and through identify, making the dice rapid whilst you ask questions regarding orders by week or orders with the aid of identify.

Q40. What Is Named Calculation?

A named calculation is a SQL expression represented as a calculated column. This expression seems and behaves as a column within the desk. A named calculation lets you make bigger the relational schema of current tables or views in a records supply view without modifying the tables or perspectives within the underlying records source.

Named calculation is used to create a new column inside the DSV the use of difficult coded values or by the use of present columns or maybe with both.

Q41. What Is Molap And Its Advantage?

MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the maximum used garage type. Its designed to offer most query overall performance to the users. The records and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is each right and bad. When a dice with MOLAP storage is processed, the statistics is pulled from the relational database, the aggregations are executed, and the records is saved within the AS database. The statistics within the cube will refresh most effective whilst the dice is processed, so latency is excessive.

Advantages:

Since the information is stored at the OLAP server in optimized format, queries (even complex calculations) are quicker than ROLAP.

The statistics is compressed so it takes up less space.

And because the information is stored on the OLAP server, you don’t need to preserve the connection to the relational database.

Cube surfing is fastest using MOLAP.

Q42. What Is Impersonation? What Are The Different Impersonation Options Available In Ssas?

Impersonation allows SSAS to expect the identity/security context of the purchaser application which is used by SSAS to carry out the server aspect records operations like facts get right of entry to, processing and so on. As a part of impersonation, the following alternatives are to be had in SSAS:

Use a particular Windows consumer call and password: This choice helps you to to specify Windows account credentials to be able to be used by SSAS to perform operations like source information get entry to, processing and so on.

Use the carrier account: When this selection is selected, SSAS makes use of the credentials of the service account beneath which the Analysis Services service is configured/running for supply statistics get entry to, processing etc.

Use the credentials of the current person: When this selection is about, SSAS makes use of the credentials of the modern person for acting operations like DMX Open Queries, Local cubes and so forth. This option cannot be used for acting server aspect operations like supply facts get admission to, processing etc.

Inherit: This option let's the SSAS server determine which impersonation mode is appropriate for each form of operation. When this feature is ready, by way of default SSAS will use the carrier account for operations like processing and the credentials of the modern consumer for operations like Local cubes, querying the facts mining fashions, and so on...

Q43. How Many Types Of Attribute Relationships Are There?

They are 2 forms of characteristic relationships they're

Rigid

Flexible

Rigid: In Rigid relationships  wherein the connection among the attributes is fixed, attributes will no longer change tiers or their respective attribute relationships.

Example: The time measurement. We recognise that month “January 2009” will ONLY belong to Year “2009” and it wont be moved to another 12 months.

Flexible :   In Flexible courting among the attributes is modified.

Example: An worker and department. An employee can be in debts branch today however it's miles feasible that the employee might be in Marketing department day after today.

Q44. What Is Deploy, Process And Build?

Bulid: Verifies the task files and create numerous nearby files.

Deploy: Deploy the shape of the dice(Skeleton) to the server.

Process: Read the information from the source and build the scale and dice structures

Q45. How Will You Hide An Attribute?

We can disguise the characteristic by means of selecting “AttributeHierarchyVisible = False” in homes of the characteristic.

Q46. What Is Role Playing Dimension With Two Examples?

Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A size can play one of a kind role in a truth desk you could recognize a roleplay measurement whilst there are multiple columns in a fact table that each have overseas keys to the identical size desk.

Ex1: There are three size keys in the factinternalsales,factresellersales tables which all consult with the dimtime desk,the equal time measurement is used  to music sales through that include either of those fact table,the corresponding  function-gambling size are robotically brought to the dice.

Ex2 : In retail banking, for checking account dice we may want to have transaction date dimension and effective date measurement. Both dimensions have date, month, region and year attributes. The formats of attributes are the identical on each dimensions, for instance the date attribute is in ‘dd-mm-yyyy’ layout. Both dimensions have contributors from 1993 to 2010.

Q47. What Is Cube Dimension?

A dice size is an example of a database dimension inside a cube is called as dice measurement. A database dimension can be utilized in multiple cubes, and more than one dice dimensions can be based on a unmarried database measurement

Q48. What Is Named Query?

Named query in DSV is similar to View in Database. This is used to create Virtual desk in DSV so one can no longer effect the underlying database. Named query is particularly used to merge the two or extra table in the datasource view or to clear out columns of a desk.

Q49. What Are Actions, How Many Types Of Actions Are There, Explain With Example?

Actions are powerful manner of extending the cost of SSAS cubes for the give up consumer. They can click on a dice or portion of a dice to start an software with the chosen item as a parameter, or to retrieve statistics approximately the selected item.

One of the items supported by using a SQL Server Analysis Services cube is the motion. An movement is an event that a user can provoke when getting access to dice statistics. The occasion can take a number of bureaucracy. For instance, a user is probably capable of view a Reporting Services record, open a Web web page, or drill via to specified information associated with the cube information

Analysis Services helps three kinds of actions..

Report action: Report motion Returns a Reporting Services document that is related to the cube records on which the motion is primarily based.

Drill via: Drillthrough Returns a end result set that offers exact data related to the cube facts on which the movement is based totally.

Standard: Standard has five motion subtypes that are primarily based on the specified dice data.

Dataset: Returns a multidimensional dataset.

Proprietary: Returns a string that may be interpreted with the aid of a patron software.

Rowset: Returns a tabular rowset.

Statement: Returns a command string that can be run through a customer application.

URL:  Returns a URL that may be opened via a patron application, typically a browser.

Q50. What Is Rolap And Its Advantage?

ROLAP (Relational Online Analytical Processing) : ROLAP does now not have the high latency downside of MOLAP. With ROLAP, the information and aggregations are stored in relational layout. This means that there might be zero latency among the relational supply database and the dice.

Disadvantage of this mode is the overall performance, this type gives the poorest query overall performance because no gadgets gain from multi dimensional storage.

Advantages:

Since the records is saved inside the relational database rather than on the OLAP server, you could view the facts in almost real time.

Also, for the reason that information is stored in the relational database, it lets in for lots larger amounts of facts, which could imply higher scalability.

Low latency.