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 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.
You can answer more but if you end this with these then the interviewer feel that you are REAL EXPERIENCED.
How many types of dimensions are there and what are they?
They are 3 types of dimensions:
confirm dimension
junk dimension
degenerate attribute
Are you Looking for SSAS Training? Please Enroll for Demo SSAS..! |
What are Measures and Measure Groups? What is the difference between them?
A Measure is any numeric quantity/value that represents a metric aligned to an organization’s goals. This is the value which the business users are interested in, and are interested in viewing these values from different angles and different granularity levels. A measure is also commonly called a fact. The term “measures” and “facts” are used interchangeably.
A Measure Group is a collection/group of measures which belong to the same underlying fact table. In SSAS, typically each Measure Group is tied to each one of the underlying fact tables.
A Measure is single numeric value whereas a Measure Group is a collection of measures.
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 are Calculated Members? How do they differ from Measures?
Calculated Members are members of a measure group and are defined based on a combination of one or more base measures, arithmetic/conditional operators, numeric values, and functions, etc. For example, profit is a calculated member/calculate measure, which is defined based on various base measures like selling price, cost, price, tax amount, freight amount, etc.
The value of a measure (base measure) is stored in a cube as part of the cube processing process. Whereas the value of a calculated member/measure is calculated on the fly in response to a user request and only the definition is stored in the cube.
What are Named Sets? What are the two types of Named Sets?
A Named Set is a set of dimension members (usually a subset of dimension members) and is defined using MDX (a Multidimensional Expression). Often Named Sets are defined for improved usability by the end users and client applications. Apart from that, they can also be used for various calculations at the cube level. Similar to calculated members/measures, named sets are defined using a combination of cube/dimension data, arithmetic operators, numeric values, functions, etc. Some of the examples of Named Sets are top 50 customers, top 10 products, top 5 students, etc.
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 are the difference between data mart and data warehouse?
Datawarehouse is complete data where as Data mart is Subset of the same.
What are the different Storage Modes supported by Cube Partitions?
There are primarily two types of data in SSAS: summary and detail data. Based on the approach used to store each of these two types of data, there are three standard storage modes supported by partitions:
ROLAP: ROLAP stands for Real Time Online Analytical Processing. In this storage mode, summary data is stored in the relational data warehouse and detail data is stored in the relational database. This storage mode offers low latency, but it requires large storage space as well as slower processing and query response times.
MOLAP: MOLAP stands for Multidimensional Online Analytical Processing. In this storage mode, both summary and detail data is stored on the OLAP server (multidimensional storage). This storage mode offers faster query response and processing times, but offers a high latency and requires average amount of storage space. This storage mode leads to duplication of data as the detail data is present in both the relational as well as the multidimensional storage.
HOLAP: HOLAP stands for Hybrid Online Analytical Processing. This storage mode is a combination of ROLAP and MOLAP storage modes. In this storage mode, summary data is stored in OLAP server (Multidimensional storage) and detail data is stored in the relational data warehouse. This storage mode offers optimal storage space, query response time, latency and fast processing times.
Have you ever worked on performance tuning, if yes what are the steps involved in it?
We need to identify the bottlenecks to tune the performance, to overcome the bottleneck we need to following the following.
Avoid named queries
Unnecessary relationships between tables
Proper attribute relationships to be given
Proper aggregation design
Proper partitioning of data
Proper dimension usage design
Avoid unnecessary many to many relationships
Avoid unnecessary measures
Set AttributeHierarchyEnabled = FALSE to Attributes that is not required
Won’t take even single measure which is not necessary.
What is processing in SSAS?
Processing is the process to load data into SSAS objects like Cubes, Partitions, and Dimensions, etc. on the OLAP Server with the data from the underlying relational data warehouse. As part of the processing step(s), aggregations are performed, data is loaded into one or more pre-defined partitions and various actions, which involve data modifications are performed in this step. The processed data is stored into respective locations like the OLAP Server, relational data warehouse, etc. depending on the Storage Mode defined for the different objects.
What is database synchronization in SSAS? What is its advantage?
Database synchronization is the process of updating the data and metadata in a database based on the target SSAS Server based on the data and metadata from the source SSAS Server. Analysis Services offers a tool called the “Synchronize Database Wizard” to synchronize databases on two different SSAS databases.
The primary advantage of a database synchronization via the Database Synchronization Wizard is that, while the databases are being synchronized by the wizard, the destination database continues to be available and the users can access the database as usual. Once the synchronization is completed, Analysis Services drops the out dated copy (old database) and switches automatically to the new database which has been updated.
What are the difficulties faced in cube development?
You can tell any area where you feel difficult to work. But always the best answers will be the following.
Giving attribute relationships
Calculations
Giving dimension usage (many to many relationship)
Analyzing the requirements
Explain the flow of creating a cube?
Steps to create a cube in ssas
Create a data source.
Create a datasource view.
Create Dimensions
Create a cube.
Deploy and Process the cube.
What are aggregates? What is the purpose of defining an aggregation design in Analysis Services?
Aggregates are summarized values, each of which corresponds to a combination of an attribute from each dimension and a measure group. An aggregate in SSAS is the differentiating factor between OLAP and OLTP, and is the fundamental principle of SSAS/OLAP, which offers blazing fast performance.
What is a datasource or DS?
The data source is the Physical Connection information that analysis service uses to connect to the database that host 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.
What is datasourceview or DSV?
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.
Datasource view is the logical view of the data in the data source.
Data source view is the only thing a cube can see.
What is named calculation?
A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source.
Named calculation is used to create a new column in the DSV using hard coded values or by using existing columns or even with both.
What are translations? What is its significance in SSAS?
Translations in SSAS allow us to bind labels/properties of those objects in SSAS which can be represented in multiple languages. In simple terms, Translations allow us to display the Labels and Captions of various SSAS objects in different languages. In SSAS both metadata and data can be translated. The objects which support Translations in SSAS include databases, cubes, dimensions, attributes, hierarchies, measure groups, calculated members, KPIs, and various other objects.
What is Time Intelligence? How is it implemented in SSAS?
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.
What is named query?
Named query in DSV is similar to View in Database. This is used to create Virtual table in DSV which will not impact the underlying database. Named query is mainly used to merge the two or more table in the datasource view or to filter columns of a table.
Why we need named queries?
A named query is used to join multiple tables, to remove unnecessary columns from a table of a database. You can achieve the same in database using Views but this Named Queries will be the best bet whe you don’t have access to create Views in database.
How will you add a new column to an existing table in data source view?
By using named calculations we can add a new column to an existing table in the data source view. Named Calculation is explained above.
What is dimension table?
A dimension table contains hierarchical data by which you’d like to summarize. A dimension table contains specific business information, a dimension table that contains the specific name of each member of the dimension. The name of the dimension member is called an “attribute”
The key attribute in the dimension must contain a unique value for each member of the dimension. This key attribute is called “primary key column”
The primary key column of each dimension table corresponding to the one of the key column in any related fact table.
What is fact table?
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 as “The table which contains METRICS” that are used to analyse the business.
It consists of 2 sections
1) Foregine key to the dimesion
2) measures/facts(a numerical value that used to monitor business activity)
What is Factless fact table?
This is very important interview question. The “Factless Fact Table” is a table which 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
Are you Looking for SSAS Online Training? Please Enroll for Demo SSAS..! |
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.