A cube dimension is an instance of a database dimension within a cube is called a cube dimension. A database dimension can be used in multiple cubes, and multiple cube dimensions can be based on a single database dimension.
Posted Date:- 2021-09-28 07:50:52
MOLAP (Multidimensional Online Analytical Processing): MOLAP is the most used storage type. It's designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.
Posted Date:- 2021-09-28 07:50:04
There are three standard storage modes in OLAP applications
1. MOLAP
2. ROLAP
3. HOLAP
Posted Date:- 2021-09-28 07:49:15
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.
Posted Date:- 2021-09-28 07:48:20
AttributeHierarchyOptimizedState: 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.
Posted Date:- 2021-09-28 07:47:05
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.
Posted Date:- 2021-09-28 07:46:15
Calculations are item in the cube that are evaluated 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.
Posted Date:- 2021-09-28 07:45:22
In Analysis Service we generally see all dimension has All member. This is because of the IsAggregatable property of the attribute. You can set its value to false so that it will not show All members. It's the default member for that attribute.
If you hide this member then you will have to set other attribute values to the default member else it will pick some value as default and this will create confusion in browsing data if someone is not known to change in default member.
Posted Date:- 2021-09-28 07:44:38
Star schema: The instar schema fact table will be directly linked with all dimension tables. The star schema’s dimensions are denormalized with each dimension being represented by a single table. In a star schema, a central fact table connects a number of individual dimension tables.
Snowflake: The snowflake schema is an extension of the STAR SCHEMA, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. In the snowflake schema, the fact table will be linked directly as well as there will be some intermediate dimension tables between fact and dimension tables.
Star flake: A hybrid structure that contains a mixture of the star(denormalized) and snowflake(normalized) schemas.
Posted Date:- 2021-09-28 07:43:51
Confirm dimension: It is the dimension that is sharable across the multiple facts or data model. This is also called 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 Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as the junk dimension.
Degenerated dimension: In this degenerate dimension contains their values in the fact table and the dimension is not available in the dimension table. Degenerated Dimension is a dimension key without a 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 dimensions. Therefore, POS Transaction Number looks like a dimension key in the fact table but does not have the corresponding dimension table.
Posted Date:- 2021-09-28 07:43:05
They are 3 types of dimensions:
Confirm dimension
Junk dimension
Degenerate attribute
Posted Date:- 2021-09-28 07:42:21
They are 2 types of attribute relationships they are
1. Rigid
2. 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 won't 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 the accounts department today but it is possible that the employee will be in the Marketing department tomorrow.
Posted Date:- 2021-09-28 07:41:37
ROLAP (Relational Online Analytical Processing) : ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.
Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.
Posted Date:- 2021-09-28 07:40:02
MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.
Posted Date:- 2021-09-28 07:39:27
1. The primary step is to log in to the SSAS instance.
2. Select the database that you would like to backup by right-clicking on it.
3. Check for backup option and click on the same.
4. On selecting a path, the backup will be store in that location. If the path is not selected, it will use C drive as a default folder.
5. SSAS Database backup can also be scheduled using the below steps:
6. From SSMS, right-click on the job folder of SQL
Click on New job
7. Write a job name which is used as a job identity
8. Navigate to steps page
9. Select the option: New
After updating the Step name, Select the SSAS command option as the job type.
Update the server name.
Write the XMLA script as below:
AdventureWorksDW.abf
true
password
10. Click on OK, check for schedule option, and schedule accordingly based on your requirement.
Posted Date:- 2021-09-28 07:38:47
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.
Posted Date:- 2021-09-28 07:38:03
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.
Posted Date:- 2021-09-28 07:37:30
The different languages used in SSAS are:
SQL language: SQL language is a standard language used in relational database systems to perform tasks like storing, updating, and retrieving the data using SQL statements.
MDX Language: MDX Language (Multidimensional Expressions) language is similar to SQL Language, which is used as a query language for OLAP cubes to perform Online Analytical Processing.
DMX Language: DMX Language (Data Mining Extensions) is another standard language used in SSAS to perform functions related to data mining models using DDL(Data Definition Language) statements, DML(Data Manipulation Language) statements.
Analysis Services Scripting Language: To create and manage Analysis Services structures directly on the server, ASSL adds a common language and object definition language. Analysis Services Scripting Language is an extension of the XMLA language.
Posted Date:- 2021-09-28 07:36:39
They are six relation between the dimension and measure group, they are
* No Relationship
* Regular
* Refernce
* Many to Many
* Data Mining
* Fact
Posted Date:- 2021-09-28 07:35:59
There are three types of dimensions called Confirm dimensions, junk dimensions, degenerated dimensions.
Confirm dimension: Confirm dimension, also referred to as Role-playing dimension, is sharable across the data model.
Junk dimension: Lumping of small dimensions to form a single dimension together is called as Junk dimension. Grouping and moving them to a different single dimension is referred to as a junk dimension.
Degenerated dimensions: Degenerated Dimension does not contain the dimension id in the dimension table. It only includes the values in the fact table.
Posted Date:- 2021-09-28 07:35:06
1. No relationship: The dimension and measure group are not related.
2. Regular: The dimension table is joined directly to the fact table.
3. Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.
4. 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.
5. 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.
6 Fact table: The dimension table is the fact table.
Posted Date:- 2021-09-28 07:34:02
Rigid and Flexible relationships are two different types of attribute relationship that represents the relation between various attributes.
Rigid Attribute Relationship: The relationship between the attributes remains the same, constant without any changes. Example: Month and date.
Flexible Attribute relationship: The relationship between the attributes will be dynamic, changing from time to time.Example: Relationship between an employee and a Manager.
Posted Date:- 2021-09-28 07:32:54
This is a very important interview question. The “Factless Fact Table†is a table that is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.
Factless fact tables are used for tracking a process or collecting stats. They are called so because the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected
Posted Date:- 2021-09-28 07:31:08
It is a concept of STORING Historical Changes and whenever an IT guy finds a new way to store then a new Type will come into picture. Basically there are 3 types of SCD they are given below:
>> SCD type1
>> SCD type2
>> SCD type3
Posted Date:- 2021-09-28 07:30:39
The maximum size of the dimension is 4 gb.
Posted Date:- 2021-09-28 07:29:41
The minimum number of partitions allowed per group measure is one.
The maximum number of partitions allowed in a measure group is 2000( As per SSAS 2005). With SSAS 2008 and the next versions, there is no limit for the number of maximum partitions.
Posted Date:- 2021-09-28 07:29:18
A property called “ AttributeHierarchyVisble†has to be selected with the value as False in the properties of the attribute.
It is represented as AttributeHierarchyVisible = False.
Posted Date:- 2021-09-28 07:28:51
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.
Posted Date:- 2021-09-28 07:28:28
Data warehouse is an environment that represents the organization’s data. Data warehouse gives a complete view of the enterprise - current and historical information for decision making.
A data mart is defined as the subset of the organization data. It includes explicitly analytical data of a particular subject or department in an organization. A data mart is of three different types:
1. Dependent
2. Independent
3. Logical data mart
Difference between data warehouse and data mart: The complete data of an organization or enterprise is called a data warehouse, while data mart is the subject of the complete data.
Posted Date:- 2021-09-28 07:27:46
An attribute hierarchy is a hierarchy or strategy followed and created for every attribute in a dimension by SSAS. An attribute consists of two levels: All level and detail level.Hierarchies are used to organize the attributes which will be converted into user-defined hierarchies to provide a path for the cube.AttributeHierarchyDisplayFolder property helps in identifying and displaying the associate attribute hierarchy to the end-users.
Posted Date:- 2021-09-28 07:26:35
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 example we are having retail and hospital data and the end user is subscribed to see only hospital data, then we can create perspective according to it.
Posted Date:- 2021-09-28 07:26:15
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.
Posted Date:- 2021-09-28 07:25:46
By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy†and “OrderByAttributeâ€. Let's say we want to see order counts for each year. In Adventure Works MDX query would be:
SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];
Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];
Posted Date:- 2021-09-28 07:24:56
Simply using the bottom count will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;
Posted Date:- 2021-09-28 07:24:31
Just open the data source view and on right click we find the option REFRESH. Click the REFRESH then it will add new attributes to the table which can be added to Cube.
Posted Date:- 2021-09-28 07:24:01
The full form of AMO is Analysis Management Objects. This is used to create or alter cubes from .NET code.
Posted Date:- 2021-09-28 07:23:34
This is a very important interview question. The “Fact less Fact Table†is a table which is similar to a Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events. Fact less fact tables are used for tracking a process or collecting stats. They are called so because the fact table does not have aggregator numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected
Posted Date:- 2021-09-28 07:23:16
A fact table contains the basic information that you wish to summarize. The table that stores the detailed value for measure is called fact table. In simple and best we can define it as “The table which contains METRICS†that are used to analyse the business.
It consists of 2 sections:
>> Foreigner key to the dimension
>> measures/facts(a numerical value that used to monitor business activity)
Posted Date:- 2021-09-28 07:22:59
By using named calculations we can add a new column to an existing table in the data source view. Named Calculation is explained above.
Posted Date:- 2021-09-28 07:22:30
Time Intelligence is a technique, which allows us to align the Time Dimension in SSAS with our actual Calendar, thereby making time calculations like Period to Date, Comparison across Parallel Time Periods, Cumulative aggregates, etc. very simple without the need for us to write explicit MDX queries/expressions.
Posted Date:- 2021-09-28 07:21:46
A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer. Data source view is the logical view of the data in the data source. Data source view is the only thing a cube can see.
Posted Date:- 2021-09-28 07:21:26
The data source is the Physical Connection information that the analysis service uses to connect to the database that hosts the data. The data source contains the connection string which specifies the server and the database hosting the data as well as any necessary authentication credentials.
Posted Date:- 2021-09-28 07:21:10
This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me to identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.†Indeed, CrossJoin has easily been my bread and butter.
Posted Date:- 2021-09-28 07:20:50
The following table lists the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.
Posted Date:- 2021-09-28 07:20:33
Processing is a critical and resource-intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services offers high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension, or a specific Partition in a cube.
Posted Date:- 2021-09-28 07:20:14
The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.
Posted Date:- 2021-09-28 07:19:58
We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO – Usage-Based Optimizations. Always, prefer the UBO method in real-time performance troubleshooting.
Posted Date:- 2021-09-28 07:19:42
The role of an Analysis Services information worker is the traditional “domain expert†role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.
Posted Date:- 2021-09-28 07:19:25
Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drill-down paths. Expanding through every level for every drill down the path is then unnecessarily complicated.
Posted Date:- 2021-09-28 07:19:05
Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drill-down paths. Expanding through every level for every drill down the path is then unnecessarily complicated.
Posted Date:- 2021-09-28 07:18:37