What Is A Data Source? What Are The Different Data Sources Supported By Ssas?
A Data Source contains the connection information used by SSAS to connect to the underlying database to load the data into SSAS during processing. A Data Source primarily contains the following information (apart from various other properties like Query timeout, Isolation etc.)
Provider
Server Name
Database Name
Impersonation Information
• SSAS Supports both .Net and OLE DB Providers. Following are some of the major sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.
What Is Sql Server Analysis Services (ssas)?
SQL Server Analysis Services (SSAS) is the On-Line Analytical Processing (OLAP) Component of SQL Server. SSAS allows you to build multidimensional structures called Cubes to pre-calculate and store complex aggregations, and also to build mining models to perform data analysis to identify valuable information like trends, patterns, relationships etc. within the data using Data Mining capabilities of SSAS, which otherwise could be really difficult to determine without Data Mining capabilities.
SSAS comes bundled with SQL Server and you get to choose whether or not to install this component as part of the SQL Server Installation.
What Is Attribute Hierarchy?
An attribute hierarchy is created for every attribute in a dimension, and each hierarchy is available for dimensioning fact data. This hierarchy consists of an “All” level and a detail level containing all members of the hierarchy.
you can organize attributes into user-defined hierarchies to provide navigation paths in a cube. Under certain circumstances, you may want to disable or hide some attributes and their hierarchies.
What Is Use Of Attributehierarchydisplayfolder Property ?
AttributeHierarchyDisplayFolder: Identifies the folder in which to display the associated attribute hierarchy to end users. For example if I set the property value as “Test” to all the Attributes of a dimension then a folder with the name “Test” will be created and all the Attributes will be placed into the same.
What Is Use Of Attributehierarchyenabled?
AttributeHierarchyEnabled: Determines whether an attribute hierarchy is generated by Analysis Services for the attribute. If the attribute hierarchy is not enabled, the attribute cannot be used in a user-defined hierarchy and the attribute hierarchy cannot be referenced in Multidimensional Expressions (MDX) statements.
What Is Use Of Attribute Hierarchy Optimized State?
Attribute Hierarchy Optimized State
Determines the level of optimization applied to the attribute hierarchy. By default, an attribute hierarchy is FullyOptimized, which means that Analysis Services builds indexes for the attribute hierarchy to improve query performance. The other option, NotOptimized, means that no indexes are built for the attribute hierarchy. Using NotOptimized is useful if the attribute hierarchy is used for purposes other than querying, because no additional indexes are built for the attribute. Other uses for an attribute hierarchy can be helping to order another attribute.
What Is Use Of Attribute Hierarchy Ordered ?
Attribute Hierarchy Ordered: Determines whether the associated attribute hierarchy is ordered. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
What Is The Use Of Attributehierarchyvisible ?
AttributeHierarchyVisible : Determines whether the attribute hierarchy is visible to client applications. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
What Are Types Of Storage Modes?
There are three standard storage modes in OLAP applications
MOLAP
ROLAP
What is datawarehouse in short DWH?
The datawarehouse is an informational environment that
Provides an integrated and total view of the enterprise
Makes the enterprise’s current and historical information easily available for decision making
Makes decision-support transactions possible without hindering operational systems
Renders the organization’s information consistent
Presents a flexible and interactive source of strategic information
OR a warehouse is a
Subject oriented
Integrated
Time variant
Non volatile for doing decision support
OR
Collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows.
OR
Subject oriented:
It define the specific business domain ex: banking, retail, insurance, etc…..
Integrated:
It should be in a position to integrated data from various source systems
Ex: sql,oracle,db2 etc……
Time variant:
It should be in a position to maintain the data the various time periods.
Non volatile:
Once data is inserted it can’t be changed
What is data mart?
A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization.
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
They are 3 types of data mart they are
Dependent
Independent
Logical data mart
What is attribute relationships, why we need it?
Attribute relationships are the way of telling the analysis service engine that how the attributes are related with each other. It will help to relate two or more attributes to each other.Processing time will be decreased if proper relationships are given. This increases the Cube Processing performance and MDX query performance too.
In Microsoft SQL Server Analysis Services, attributes within a dimension are always related either directly or indirectly to the key attribute. When you define a dimension based on a star schema, which is where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. When you define a dimension based on a snowflake schema, which is where dimension attributes are derived from multiple related tables, an attribute relationship is automatically defined as follows:
Between the key attribute and each non-key attribute bound to columns in the main dimension table.
Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.
Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.
How many types of attribute relationships are there?
They are 2 types of attribute relationships they are
Rigid
Flexible
Rigid: In Rigid relationships where the relationship between the attributes is fixed, attributes will not change levels or their respective attribute relationships.
Example: The time dimension. We know that month “January 2009” will ONLY belong to Year “2009” and it wont be moved to any other year.
Flexible : In Flexible relationship between the attributes is changed.
Example: An employee and department. An employee can be in accounts department today but it is possible that the employee will be in Marketing department tomorrow.
How many types of dimensions are there and what are they?
They are 3 types of dimensions:
confirm dimension
junk dimension
degenerate attribute
What is regular type, no relation type, fact type, referenced type, many-to-many type with example?
No relationship: The dimension and measure group are not related.
Regular: The dimension table is joined directly to the fact table.
Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.
Many to many:The dimension table is to an intermediate fact table,the intermediate fact table is joined , in turn, to an intermediate dimension table to which the fact table is joined.
Data mining:The target dimension is based on a mining model built from the source dimension. The source dimension must also be included in the cube.
Fact table: The dimension table is the fact table.
What are calculated members and what is its use?
Calculations are item in the cube that are eveluated at runtime
Calculated members: You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions.
Example: You can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column. Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.
What are KPIs and what is its use?
In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. We use KPI to see the business at the particular point, this is represents with some graphical items such as traffic signals,ganze etc
What are actions, how many types of actions are there, explain with example?
Actions are powerful way of extending the value of SSAS cubes for the end user. They can click on a cube or portion of a cube to start an application with the selected item as a parameter, or to retrieve information about the selected item.
One of the objects supported by a SQL Server Analysis Services cube is the action. An action is an event that a user can initiate when accessing cube data. The event can take a number of forms. For example, a user might be able to view a Reporting Services report, open a Web page, or drill through to detailed information related to the cube data
Analysis Services supports three types of actions..
Report action: Report action Returns a Reporting Services report that is associated with the cube data on which the action is based.
Drill through: Drillthrough Returns a result set that provides detailed information related to the cube data on which the action is based.
Standard: Standard has five action subtypes that are based on the specified cube data.
Dataset: Returns a mutlidimensional dataset.
Proprietary: Returns a string that can be interpreted by a client application.
Rowset: Returns a tabular rowset.
Statement: Returns a command string that can be run by a client application.
URL: Returns a URL that can be opened by a client application, usually a browser.
What is partition, how will you implement it?
You can use the Partition Wizard to define partitions for a measure group in a cube. By default, a single partition is defined for each measure group in a cube. Access and processing performance, however, can degrade for large partitions. By creating multiple partitions, each containing a portion of the data for a measure group, you can improve the access and processing performance for that measure group.
What is the minimum and maximum number of partitions required for a measure group?
In 2005 a MAX of 2000 partitions can be created per measure group and that limit is lifted in later versions.
In any version the MINIMUM is ONE Partition per measure group.
Confirm dimension: It is the dimension which is sharable across the multiple facts or data model. This is also called as Role Playing Dimensions.
junk dimension: A number of very small dimensions might be lumped (a small irregularly shaped) together to form a single dimension, a junk dimension – the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.
Degenerated dimension: In this degenerate dimension contains their values in fact table and the dimension id not available in dimension table. Degenerated Dimension is a dimension key without corresponding dimension.
Example: In the PointOfSale Transaction Fact table, we have:
Date Key (FK), Product Key (FK), Store Key (FK), Promotion Key (FP), and POS Transaction Number
Date Dimension corresponds to Date Key, Production Dimension corresponds to Production Key. In a traditional parent-child database, POS Transactional Number would be the key to the transaction header record that contains all the info valid for the transaction as a whole, such as the transaction date and store identifier. But in this dimensional model, we have already extracted this info into other dimension. Therefore, POS Transaction Number looks like a dimension key in the fact table but does not have the corresponding dimension table.
What are the types of database schema?
They are 3 types of database schema they are
Star
Snowflake
Starflake
What are the difference between data mart and data warehouse?
Datawarehouse is complete data where as Data mart is Subset of the same.
Ex:
All the organisation data may related to finance department, HR, banking dept are stored in data warehouse where as in data mart only finance data or HR department data will be stored. So data warehouse is a collection of different data marts.
A Data Source contains the connection information used by SSAS to connect to the underlying database to load the data into SSAS during processing. A Data Source primarily contains the following information (apart from various other properties like Query timeout, Isolation etc.)
Provider
Server Name
Database Name
Impersonation Information
• SSAS Supports both .Net and OLE DB Providers. Following are some of the major sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.
What Is Sql Server Analysis Services (ssas)?
SQL Server Analysis Services (SSAS) is the On-Line Analytical Processing (OLAP) Component of SQL Server. SSAS allows you to build multidimensional structures called Cubes to pre-calculate and store complex aggregations, and also to build mining models to perform data analysis to identify valuable information like trends, patterns, relationships etc. within the data using Data Mining capabilities of SSAS, which otherwise could be really difficult to determine without Data Mining capabilities.
SSAS comes bundled with SQL Server and you get to choose whether or not to install this component as part of the SQL Server Installation.
What Is Attribute Hierarchy?
An attribute hierarchy is created for every attribute in a dimension, and each hierarchy is available for dimensioning fact data. This hierarchy consists of an “All” level and a detail level containing all members of the hierarchy.
you can organize attributes into user-defined hierarchies to provide navigation paths in a cube. Under certain circumstances, you may want to disable or hide some attributes and their hierarchies.
What Is Use Of Attributehierarchydisplayfolder Property ?
AttributeHierarchyDisplayFolder: Identifies the folder in which to display the associated attribute hierarchy to end users. For example if I set the property value as “Test” to all the Attributes of a dimension then a folder with the name “Test” will be created and all the Attributes will be placed into the same.
What Is Use Of Attributehierarchyenabled?
AttributeHierarchyEnabled: Determines whether an attribute hierarchy is generated by Analysis Services for the attribute. If the attribute hierarchy is not enabled, the attribute cannot be used in a user-defined hierarchy and the attribute hierarchy cannot be referenced in Multidimensional Expressions (MDX) statements.
What Is Use Of Attribute Hierarchy Optimized State?
Attribute Hierarchy Optimized State
Determines the level of optimization applied to the attribute hierarchy. By default, an attribute hierarchy is FullyOptimized, which means that Analysis Services builds indexes for the attribute hierarchy to improve query performance. The other option, NotOptimized, means that no indexes are built for the attribute hierarchy. Using NotOptimized is useful if the attribute hierarchy is used for purposes other than querying, because no additional indexes are built for the attribute. Other uses for an attribute hierarchy can be helping to order another attribute.
What Is Use Of Attribute Hierarchy Ordered ?
Attribute Hierarchy Ordered: Determines whether the associated attribute hierarchy is ordered. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
 |
| Cognizant Frequently Asked SSAS Interview Questions Answers |
What Is The Use Of Attributehierarchyvisible ?
AttributeHierarchyVisible : Determines whether the attribute hierarchy is visible to client applications. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.
What Are Types Of Storage Modes?
There are three standard storage modes in OLAP applications
MOLAP
ROLAP
HOLAP.
The datawarehouse is an informational environment that
Provides an integrated and total view of the enterprise
Makes the enterprise’s current and historical information easily available for decision making
Makes decision-support transactions possible without hindering operational systems
Renders the organization’s information consistent
Presents a flexible and interactive source of strategic information
OR a warehouse is a
Subject oriented
Integrated
Time variant
Non volatile for doing decision support
OR
Collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows.
OR
Subject oriented:
It define the specific business domain ex: banking, retail, insurance, etc…..
Integrated:
It should be in a position to integrated data from various source systems
Ex: sql,oracle,db2 etc……
Time variant:
It should be in a position to maintain the data the various time periods.
Non volatile:
Once data is inserted it can’t be changed
What is data mart?
A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization.
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
They are 3 types of data mart they are
Dependent
Independent
Logical data mart
What is attribute relationships, why we need it?
Attribute relationships are the way of telling the analysis service engine that how the attributes are related with each other. It will help to relate two or more attributes to each other.Processing time will be decreased if proper relationships are given. This increases the Cube Processing performance and MDX query performance too.
In Microsoft SQL Server Analysis Services, attributes within a dimension are always related either directly or indirectly to the key attribute. When you define a dimension based on a star schema, which is where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. When you define a dimension based on a snowflake schema, which is where dimension attributes are derived from multiple related tables, an attribute relationship is automatically defined as follows:
Between the key attribute and each non-key attribute bound to columns in the main dimension table.
Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.
Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.
How many types of attribute relationships are there?
They are 2 types of attribute relationships they are
Rigid
Flexible
Rigid: In Rigid relationships where the relationship between the attributes is fixed, attributes will not change levels or their respective attribute relationships.
Example: The time dimension. We know that month “January 2009” will ONLY belong to Year “2009” and it wont be moved to any other year.
Flexible : In Flexible relationship between the attributes is changed.
Example: An employee and department. An employee can be in accounts department today but it is possible that the employee will be in Marketing department tomorrow.
How many types of dimensions are there and what are they?
They are 3 types of dimensions:
confirm dimension
junk dimension
degenerate attribute
What is regular type, no relation type, fact type, referenced type, many-to-many type with example?
No relationship: The dimension and measure group are not related.
Regular: The dimension table is joined directly to the fact table.
Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.
Many to many:The dimension table is to an intermediate fact table,the intermediate fact table is joined , in turn, to an intermediate dimension table to which the fact table is joined.
Data mining:The target dimension is based on a mining model built from the source dimension. The source dimension must also be included in the cube.
Fact table: The dimension table is the fact table.
What are calculated members and what is its use?
Calculations are item in the cube that are eveluated at runtime
Calculated members: You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions.
Example: You can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column. Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.
What are KPIs and what is its use?
In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. We use KPI to see the business at the particular point, this is represents with some graphical items such as traffic signals,ganze etc
What are actions, how many types of actions are there, explain with example?
Actions are powerful way of extending the value of SSAS cubes for the end user. They can click on a cube or portion of a cube to start an application with the selected item as a parameter, or to retrieve information about the selected item.
One of the objects supported by a SQL Server Analysis Services cube is the action. An action is an event that a user can initiate when accessing cube data. The event can take a number of forms. For example, a user might be able to view a Reporting Services report, open a Web page, or drill through to detailed information related to the cube data
Analysis Services supports three types of actions..
Report action: Report action Returns a Reporting Services report that is associated with the cube data on which the action is based.
Drill through: Drillthrough Returns a result set that provides detailed information related to the cube data on which the action is based.
Standard: Standard has five action subtypes that are based on the specified cube data.
Dataset: Returns a mutlidimensional dataset.
Proprietary: Returns a string that can be interpreted by a client application.
Rowset: Returns a tabular rowset.
Statement: Returns a command string that can be run by a client application.
URL: Returns a URL that can be opened by a client application, usually a browser.
What is partition, how will you implement it?
You can use the Partition Wizard to define partitions for a measure group in a cube. By default, a single partition is defined for each measure group in a cube. Access and processing performance, however, can degrade for large partitions. By creating multiple partitions, each containing a portion of the data for a measure group, you can improve the access and processing performance for that measure group.
What is the minimum and maximum number of partitions required for a measure group?
In 2005 a MAX of 2000 partitions can be created per measure group and that limit is lifted in later versions.
In any version the MINIMUM is ONE Partition per measure group.
What are confirmed dimensions, junk dimension and degenerated dimensions?
Confirm dimension: It is the dimension which is sharable across the multiple facts or data model. This is also called as Role Playing Dimensions.
junk dimension: A number of very small dimensions might be lumped (a small irregularly shaped) together to form a single dimension, a junk dimension – the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.
Degenerated dimension: In this degenerate dimension contains their values in fact table and the dimension id not available in dimension table. Degenerated Dimension is a dimension key without corresponding dimension.
Example: In the PointOfSale Transaction Fact table, we have:
Date Key (FK), Product Key (FK), Store Key (FK), Promotion Key (FP), and POS Transaction Number
Date Dimension corresponds to Date Key, Production Dimension corresponds to Production Key. In a traditional parent-child database, POS Transactional Number would be the key to the transaction header record that contains all the info valid for the transaction as a whole, such as the transaction date and store identifier. But in this dimensional model, we have already extracted this info into other dimension. Therefore, POS Transaction Number looks like a dimension key in the fact table but does not have the corresponding dimension table.
What are the types of database schema?
They are 3 types of database schema they are
Star
Snowflake
Starflake
What are the difference between data mart and data warehouse?
Datawarehouse is complete data where as Data mart is Subset of the same.
Ex:
All the organisation data may related to finance department, HR, banking dept are stored in data warehouse where as in data mart only finance data or HR department data will be stored. So data warehouse is a collection of different data marts.
Post a Comment