The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.
Posted Date:- 2021-09-28 07:16:50
1. Structured Query Language (SQL)
2. Multidimensional Expressions (MDX) – an industry-standard query language orientated towards analysis
3. Data Mining Extensions (DMX) – an industry-standard query language oriented toward data mining.
4. Analysis Services Scripting Language (ASSL) – used to manage Analysis Services database objects.
Posted Date:- 2021-09-28 07:16:01
A database is called an OLAP Database if the database satisfies the FASMI rules :
1. Fast Analysis–is defined in the OLAP scenario in five seconds or less.
2. Shared –Must support access to data by many users in the factors of Sensitivity and WriteBacks.
3. Multidimensional –The data inside the OLAP Database must be multidimensional in structure.
4. Information –The OLAP database Must support large volumes of data.
Posted Date:- 2021-09-28 07:14:40
SSAS uses both server and client components to supply OLAP and data mining functionality BI Applications.
The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services is implemented as a separate instance of the Windows service.
Clients communicate with Analysis Services using the standard XMLA (XML For Analysis), protocol for issuing commands and receiving responses, exposed as a web service.
Posted Date:- 2021-09-28 07:12:56
A partition in SSAS is referred to as a physical location of the stored cube data. There will be one partition for every cube by default. Every time a measure group is created, another partition will be created.
Query performance will be high if a partition is used because SSAS will read the data from the partitions that contain answers to the queries. Partitions help in the management of cubes and also stores aggregations.
Posted Date:- 2021-09-28 07:11:34
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.
Posted Date:- 2021-09-28 07:10:36
1. SSAS allows combining data from multiple underlying data sources into a single DSV. To be able to add Table(s)/View(s) from multiple data sources, first, you need to create a DSV using your first source and this source acts as the primary data source. Now after the initial DSV is created, you can add one or more data sources into DSV which will act as secondary data sources and you can choose additional Table(s)/View(s) from the secondary data sources which you want to include in your DSV.
2. The key thing while combining data from multiple data sources is that the Primary Data Source must support OPENROWSET queries. Hence in most cases, SQL Server is used as the Primary Data Source.
Posted Date:- 2021-09-28 07:08:16
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
Posted Date:- 2021-09-28 07:05:55
1. A Named Query is a SQL query/expression in your DSV which acts as a Table. It is used to combine data from one or more Table(s)/View(s) from the underlying data source without requiring any schematic changes to the underlying data source Table(s)/View(s).
2. The SQL Query used in the Named Query should conform to the underlying data source dialect. For example, if the underlying data source is SQL Server, then it should conform to T-SQL, If is it Oracle, then it should conform to PL/SQL, etc…
3. Named Queries are used in various scenarios, the following are some of the common scenarios:
1. Combining data from multiple Tables/Views from the underlying data source by using either simple or complex join operations.
2. Adding filter conditions for filtering out unwanted data or selecting the required data (limiting the data).
4. Pretty much everything that can be done using a Named Calculation can also be done using a Named Query.
Posted Date:- 2021-09-28 07:04:21
A Named Calculation is a new column added to a Table in DSV and is based on an expression. This capability allows you to add an extra column into your DSV which is based on one or more columns from the underlying data source Table(s)/View(s) combined using an expression without requiring the addition of a physical column in the underlying database Table(s)/View(s).
The expression used in the Named Calculation should conform to the underlying data source dialect. For example, if the underlying data source is SQL Server, then it should conform to T-SQL, If is it Oracle, then it should conform to PL/SQL, etc…
Named Calculations can be used in many scenarios, following are some of the common scenarios:
For creating Derived Columns. Say you have First Name and Last Name in the underlying data source Table/View and you want to get the Full Name as “First Name + space + Last Nameâ€. Such things can be added as a Named Calculation.
For performing Lookup Operations. Say you have an Employee table that has AddressID and an Address table in which AddressID is the Primary Key. Now, to get the address information (say Country) into the Employee table in DSV, a Named Calculation can be added to the Employee table with the following expression:
(
SELECT Country
FROM Address
WHERE AddressID = Employee.AddressID
)
Posted Date:- 2021-09-28 07:02:03
Perspective is an approach to diminish the intricacy of blocks by shrouded components like measure gatherings, measures, measurements, progressive systems and so forth. It's only cutting off a shape, for example, if we are having retail and clinic information and the end client is brought in to see just doctor's facility information, at that point we can make a perspective as per it.
Posted Date:- 2021-09-28 06:59:40
These measure groups can contain distinctive measurements and be at various granularity yet inasmuch as you show your 3D square effectively, your clients will have the capacity to utilize measures from every one of these measure bunches in their questions effortlessly and without agonizing over the fundamental multifaceted nature.
SQL Server Training & Certification
* Personalized Free Consultation
* Access to Our Learning Management System
* Access to Our Course Curriculum
* Be a Part of Our Free Demo Class
Posted Date:- 2021-09-28 06:59:06
It is an artificially generated key which is useful when the records don’t have any natural key. And not all tables need surrogate keys. The main reason to use them is they can assure that they act as unique and are represented by an auto-incrementing ID.
Posted Date:- 2021-09-28 06:57:51
There are certain enhancements for SSAS which should be applied in some area like-
>> Time Intelligence applies to Cube.
>> Account Intelligence applies to Dimension or Cube.
>> Custom Aggregation to Dimension or Cube.
>> Custom Member Formula again to Dimension or Cube.
>> Dimension Intelligence applies to Dimension or Cube.
Posted Date:- 2021-09-28 06:56:57
Star Schemas are better as they use fewer foreign keys, more in a denormalized form which makes them better for performance.
However, both schemas depend upon the size of the data. If the data is small then star schema comes into the process on the other hand if data is large with many relationships between them then you should go for Snowflake Schemas.
Posted Date:- 2021-09-28 06:55:52
In SSAS KPIs are Key Performance Indicators and are used to estimate if the goals are achieved. A value, goal, and trend can be measured by it.
Posted Date:- 2021-09-28 06:54:57
The purpose of setting the logical keys is achieved in the scenario where you have to work on the data from a variety of data sources, that time you have to define the logical relationships manually in DSV to supply those relationships that have been defined automatically.
Posted Date:- 2021-09-28 06:54:17
By using named calculations we can add a new column to an existing table in the data source view.
Posted Date:- 2021-09-28 06:53:43
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.
Posted Date:- 2021-09-28 06:53:10
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:
* Foregine key to the dimesion
* measures/facts(a numerical value that used to monitor business activity)
Posted Date:- 2021-09-28 06:51:38
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
Posted Date:- 2021-09-28 06:50:33
>> 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 outdated copy (old database) and switches automatically to the new database which has been updated.
Posted Date:- 2021-09-28 06:50:05
A difference is identified between calculated measure and derived measured based on the time frame of the calculation being performed.
Calculated measure: A measure is called a calculated measure as the calculation is performed when aggregations are created. The values obtained are not stored in a cube. Derived measure: A measure is called a derived measure as the calculation is performed before aggregations are created. These values that are obtained are stored in a cube.
Posted Date:- 2021-09-28 06:48:03
Dimensions in SSAS includes the group of attributes, represented in the form of columns in a table or as views. Dimensions can be used multiple times in a cube, or many cubes and interlinked. The significant type of dimensions is database dimension and cube dimension.
Database dimension: A dimension that is not dependent on a cube is called a database dimension.
Cube dimension: An instance of a database dimension is called a cube dimension.
Posted Date:- 2021-09-28 06:47:23
In Analysis Service we, for the most part, observe that all dimensions have all parts. This is a direct result of an IsAggregatable property of the trait. You can set its incentive to false, with the goal that it won't demonstrate all parts. It's the default part for that characteristic. On the off chance that you shroud this part then you should set others to ascribe an incentive to default part else it will pick some an incentive as default and this will make perplexity in perusing the information in the event that somebody isn't known to change in default part.
Posted Date:- 2021-09-28 06:46:51
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.):
1. Provider
2. Server Name
3. Database Name
4. 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 another relational database with the appropriate OLE DB provider.
Posted Date:- 2021-09-28 06:46:10
We need to identify the bottlenecks to tune the performance, to overcome the bottleneck we need to follow 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 Attribute Hierarchy Enabled = FALSE to Attributes that is not required won’t take even a single measure which is not necessary.
Posted Date:- 2021-09-28 06:45:13
In your SSAS interview questions, you may be asked about the partition storage modes. They are 3.
MOLAP is the multidimensional database. Using the cubes. This option has faster queries.
ROLAP uses the relational database directly, it is slower, but it does not require time to process the cubes.
HOLAP is and Hybrid model. It creates aggregations on partitions.
Posted Date:- 2021-09-28 06:41:58
The snowflake mapping is an expansion of the star pattern, where each purpose of the star detonates into more focus. In a star pattern, each dimension is spoken to by a solitary dimensional table, while in a snowflake construction, that dimensional table is standardized into various query tables, each speaking to a dimension in the dimensional progressive system. In snowdrop mapping, the actuality table will be connected straightforwardly and there will be some transitional dimension tables among certainty and dimension tables.
Posted Date:- 2021-09-28 06:40:36
The "Factless Fact Table" is a table that is like a Fact Table with the exception of having any measure; implying that this table simply has the connections to the measurements. These tables empower you to follow occasions; undoubtedly they are for account occasions. Factless actuality tables are utilized for following a procedure or gathering details. They are called so on the grounds that the reality table does not have aggregatable numeric qualities or data. They are unimportant key qualities with reference to the measurements from which the details can be gathered.
Posted Date:- 2021-09-28 06:40:10
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.
Posted Date:- 2021-09-28 06:39:27
FASMI refers to Fast Analysis Shared Multi-dimensional Information. FASMI is an alternative term for OLAP. A database is an OLAP database if the database follows and satisfies the FASMI rules.
Fast Analysis: The analysis can be performed within 5 seconds or less in the OLAP scenario.
Shared: The data can be accessed by many users using the factors of sensitivity and writebacks
Multi-dimensional: The structure of the data should be multi-dimensional.
Information: OLAP database is capable of handling massive volumes of data.
Posted Date:- 2021-09-28 06:37:10
A significant number of the UIs/planners/wizards in BIDS which are a piece of an SSAS venture rely upon the Primary Key and Relationships among Fact and Dimension tables. Thus it is essential to characterize the Primary Key and Relationships in DSV.
Posted Date:- 2021-09-28 06:36:06
A Named Calculation is another segment added to a Table in DSV and depends on an articulation. This capacity enables you to include an additional section into your DSV which depends on at least one segment from hidden information source Table(s)/View(s) joined together utilizing an articulation without requiring the option of a physical segment in the fundamental database Table(s)/View(s
Posted Date:- 2021-09-28 06:35:30
Impersonation is the process that enables SSAS to expect the personality/security setting of the customer application which is utilized by SSAS to play out the server-side information tasks like information access, preparing, and so forth.
Posted Date:- 2021-09-28 06:35:08
SSAS Supports both.Net and OLE DB Providers. Following are a portion of the significant sources bolstered by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other social databases with the fitting OLE DB supplier.
Posted Date:- 2021-09-28 06:34:42
A Data Source contains the association data utilized by SSAS to interface with the hidden database to stack the information into SSAS during the preparation. A Data Source basically contains the accompanying data (aside from different properties like Query timeout, Isolation and so on.
Posted Date:- 2021-09-28 06:34:17
OLAP is utilized for investigation purposes to help everyday business choices and is described by less continuous information refreshes and contains verifiable information. Though an OLTP (On-Line Transaction Processing) is utilized to help everyday business tasks and is described by continuous information updates and contains the latest information alongside restricted authentic information, dependent on the maintenance approach driven by business needs.
Posted Date:- 2021-09-28 06:33:42
OLAP is the abbreviation for On-Line Analytical Processing. It is a capacity or an arrangement of devices that empowers the end clients to effortlessly and successfully get to the information distribution center’s data utilizing an extensive variety of instruments like Microsoft Excel, Reporting Services, and numerous other outsider business intelligence apparatuses.
Posted Date:- 2021-09-28 06:33:10
* 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.
Posted Date:- 2021-09-28 06:32:51
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.
Posted Date:- 2021-09-28 06:32:11
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.
Posted Date:- 2021-09-28 06:31:42
>> 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 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 defines 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
Posted Date:- 2021-09-28 06:31:15
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 a single numeric value whereas a Measure Group is a collection of measures.
Posted Date:- 2021-09-28 06:28:50
They are 3 types of dimensions:
>> confirm dimension
>> junk dimension
>> degenerate attribute
Posted Date:- 2021-09-28 06:28:35
In 2005 it was 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 feels that you are REAL EXPERIENCED.
Posted Date:- 2021-09-28 06:28:11
In a multidimensional model, it is an aggregation of numeric values. It shows average values, sum, count, distinct and other functions.
Posted Date:- 2021-09-28 06:27:43
If your database is big, you may want to partition your data by date, by zone or other criteria of your preference.
The partitions are logical units to divide the data. That reduces the process time and the load time is divided.
Posted Date:- 2021-09-28 06:26:52
When you install SSAS, there are 2 main options the Multidimensional model (that did not change too much from the original version of SQL 2005 and the tabular model which started on SQL Server 2012.
The multidimensional model is a more mature model that is harder to learn for newbies. By the other hand, tabular is simpler, but it is memory-dependent. Microsoft tried to create a powerful Excel in the tabular model. So, Excel users may feel easier to learn and create projects with tabular models.
Posted Date:- 2021-09-28 06:26:26
It is SQL Server Analysis Services. It is a software created by Microsoft that comes with the SQL Server installer to create cubes and multidimensional data.
Posted Date:- 2021-09-28 06:25:59