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 Data Warehouse In Short Dwh?
The data warehouse 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
What Is The Difference Between Ssas 2005 And Ssas2008?
In 2005 its not possible to create an empty cube but in 2008 we can create an empty cube.
A new feature in Analysis Services 2008 is the Attribute Relationships tab in the Dimension Designer . to implement attribute relationship is complex in ssas 2005
we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008.
How You Provide Security To Cube?
By defining roles we provide security to cubes. Using roles we can restrict users from accessing restricted data.
Procedure as follows
Define Role
Set Permission
Add appropriate Users to the role
What Is The Maximum Size Of A Dimension?
The maximum size of the dimension is 4 gb.
What Is Deploy, Process And Build?
Bulid: Verifies the project files and create several local files.
Deploy: Deploy the structure of the cube(Skeleton) to the server.
Process: Read the data from the source and build the dimensions and cube structures
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 multidimensional 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 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 Is Perspective, Have You Ever Created Perspective?
Perspectives are a way to reduce the complexity of cubes by hidden elements like measure groups, measures, dimensions, hierarchies etc. It’s nothing but slicing of a cube, for ex we are having retail and hospital data and end user is subscribed to see only hospital data, then we can create perspective according to it.
What Are Aggregations And Its Use?
Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard.
This wizard guides you through the following steps
Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.
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 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 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 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 Is Role Playing Dimension With Two Examples?
Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A dimension can play different role in a fact table you can recognize a roleplay dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table.
Ex1: There are three dimension keys in the factinternalsales,factresellersales tables which all refer to the dimtime table,the same time dimension is used to track sales by that contain either of these fact table,the corresponding role-playing dimension are automatically added to the cube.
Ex2 : In retail banking, for checking account cube we could have transaction date dimension and effective date dimension. Both dimensions have date, month, quarter and year attributes. The formats of attributes are the same on both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format. Both dimensions have members from 1993 to 2010.
What Is Scd (slowly Changing Dimension)?
Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. Implementing the SCD mechanism enables users to know to which category an item belonged to in any given date.
How Many Types Of Relations Are There Between Dimension And Measure Group?
They are six relation between the dimension and measure group, they are
No Relationship
Regular
Refernce
Many to Many
Data Mining
Fact
What Is Attribute?
An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.
What Is Surrogate Key?
A surrogate key is the SQL generated key which acts like an alternate primary key for the table in database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. A primary difference between a primary key and surrogate key in few databases is that primarykey uniquely identifies a record while a Surrogatekey uniquely identifies an entity.
Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.
What Is Measure Group, Measure?
Measure groups : These measure groups can contain different dimensions and be at different granularity but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.
Measures : Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.
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 Data Warehouse In Short Dwh?
The data warehouse 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
What Is The Difference Between Ssas 2005 And Ssas2008?
In 2005 its not possible to create an empty cube but in 2008 we can create an empty cube.
A new feature in Analysis Services 2008 is the Attribute Relationships tab in the Dimension Designer . to implement attribute relationship is complex in ssas 2005
we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008.
How You Provide Security To Cube?
By defining roles we provide security to cubes. Using roles we can restrict users from accessing restricted data.
Procedure as follows
Define Role
Set Permission
Add appropriate Users to the role
 |
| VeriSign Most Frequently Asked Latest SSAS Interview Questions Answers |
What Is The Maximum Size Of A Dimension?
The maximum size of the dimension is 4 gb.
What Is Deploy, Process And Build?
Bulid: Verifies the project files and create several local files.
Deploy: Deploy the structure of the cube(Skeleton) to the server.
Process: Read the data from the source and build the dimensions and cube structures
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 multidimensional 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 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 Is Perspective, Have You Ever Created Perspective?
Perspectives are a way to reduce the complexity of cubes by hidden elements like measure groups, measures, dimensions, hierarchies etc. It’s nothing but slicing of a cube, for ex we are having retail and hospital data and end user is subscribed to see only hospital data, then we can create perspective according to it.
What Are Aggregations And Its Use?
Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard.
This wizard guides you through the following steps
Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.
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 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 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 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 Is Role Playing Dimension With Two Examples?
Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A dimension can play different role in a fact table you can recognize a roleplay dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table.
Ex1: There are three dimension keys in the factinternalsales,factresellersales tables which all refer to the dimtime table,the same time dimension is used to track sales by that contain either of these fact table,the corresponding role-playing dimension are automatically added to the cube.
Ex2 : In retail banking, for checking account cube we could have transaction date dimension and effective date dimension. Both dimensions have date, month, quarter and year attributes. The formats of attributes are the same on both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format. Both dimensions have members from 1993 to 2010.
What Is Scd (slowly Changing Dimension)?
Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. Implementing the SCD mechanism enables users to know to which category an item belonged to in any given date.
How Many Types Of Relations Are There Between Dimension And Measure Group?
They are six relation between the dimension and measure group, they are
No Relationship
Regular
Refernce
Many to Many
Data Mining
Fact
What Is Attribute?
An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.
What Is Surrogate Key?
A surrogate key is the SQL generated key which acts like an alternate primary key for the table in database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. A primary difference between a primary key and surrogate key in few databases is that primarykey uniquely identifies a record while a Surrogatekey uniquely identifies an entity.
Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.
What Is Measure Group, Measure?
Measure groups : These measure groups can contain different dimensions and be at different granularity but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.
Measures : Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.