When should you use a
Star and when should you use Snowflake Schema ?
Star Schema : If the performance is the priority Than
go for Star Schema, Since here dimension tables are denormalized.
Snowflake Schema : If memory space is the priority than go for Snowflake Schema,
since here dimension tables are normalized
What are the Advantage
and disadvantage of Snowflake Schema ?
Advantage of Snowflake
Schema
·
The main advantage of
Snowflake Schema is the improvement of query performance due to minimized disk
storage requirements and joining smaller lookup tables.
·
It is easier to
maintain.
·
Increase flexibility.
Disadvantage of
Snowflake Schema
·
The main disadvantage
of the Snowflake Schema is the additional maintenance efforts needed to the
increase number of lookup tables.
·
Makes the queries much
more difficult to create because more tables need to be joined.
What is the difference
between a data source and data source view ?
A Data Source represents a connection to a particular data
source using one of the supplied providers. A Data Source View is an
abstraction layer that you design using a data source. The latter also contains
expressions that create calculated fields
What is the difference
between online and offline mode in SSAS ?
Online
mode means that you are directly connected to a particular SSAS database
instance. Any changes you make are implemented on save. Offline mode means that
you are creating metadata files that can be deployed to the SSAS server when
you want. An important consideration for offline mode is that BIDS includes no
granular conflict resolution
What aggregation types are available for measures in BIDS ?
The
default aggregation is SUM. Other available aggregation types include the
following: MIN, MAX, COUNT, DISTINCT COUNT, AVERAGE OF CHILDREN, FIRST CHILD,
LAST CHILD, FIRST NON EMPTY, LAST NON EMPTY. You can also design custom
aggregations using MDX
What is star schema in
SSAS ?
A star schema is a type of modeling used as a basis for
building an OLAP cube. It consists of at least one fact table and many
dimension tables. The rows in the dimension tables have a 1:M
relationship with the rows in the fact table.
In a
star schema every dimension table is related directly to the fact table
What is Cube ?
A cube is a set of related measures and
dimensions that is used to analyze data. A Cube is the primary storage
mechanism for SSAS data. It consists of at least one fact table and many
dimension tables. The rows in these tables have a key relationship to each
other.
What is fact table ?
A fact table is the center table surrounded by
a dimension tables. All the dimension tables directly or indirectly connected
with the fact table.
A fact table contains foreign keys to relate
the rows in the fact table to the dimension table rows and facts. Facts are key
performance indicators, for example net sales dollar, net sales unit, etc.
Facts are usually numeric and additive.
What is a dimension
table ?
Dimension table consists of one or more source tables. Dimension
tables have three types of columns. The first is the original primary key for
each row. The second is a new primary key (Or surrogate key) for each row. This
key is used to guarantee uniqueness, because the original data could come from
multiple sources. The third is a column for each attribute that describes each
dimensional value.
What is the difference between SSAS 2005 and SSAS2008?
1. In 2005 its not possible to create an empty
cube but in 2008 we can create an empty cube.
2. 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
3. we can create ONLY
2000 partitions per Measure Group in ssas 2005 and the same limit of
partitions is removed in ssas 2008
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
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
1. Dependent
2. Independent
3. Logical data mart
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 follow the following.
1. Avoid named queries
2. Unnecessary relationships between
tables
3. Proper attribute relationships to
be given
4. Proper aggregation design
5. Proper partitioning of data
6. Proper dimension usage design
7. Avoid unnecessary many to many
relationships
8. Avoid unnecessary measures
9. Set AttributeHierarchyEnabled =
FALSE to Attributes that is not required
10. Won’t take even single measure
which is not necessary
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 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.
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
§ 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
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 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:
1. confirm dimension
2. junk dimension
3. degenerate attribute
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.
How will you hide an attribute?
We can hide the
attribute by selecting “AttributeHierarchyVisible = False” in properties of the
attribute.
How will you make an attribute not process?
By
selecting “ AttributeHierarchyEnabled = False”, we can make an
attribute not in process.
What is use of IsAggregatable property?
In Analysis
Service we generally see all dimension has All member. This is because of
IsAggregatable property of the attribute. You can set its value to false, so
that it will not show All member. Its default member for that attribute.
If you hide this member than you will have to set other attribute value to
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.
What are key, name and value columns of an attribute?
Key column of
any attribute: Contains the column or columns that represent the key for the
attribute, which is the column in the underlying relational table in the data
source view to which the attribute is bound. The value of this column for each
member is displayed to users unless a value is specified for the NameColumn
property.
Name
column of an attribute: Identifies the column that provides the name of the
attribute that is displayed to users, instead of the value in the key column
for the attribute. This column is used when the key column value for an
attribute member is cryptic or not otherwise useful to the user, or when the
key column is based on a composite key. The NameColumn property is not used in
parent-child hierarchies; instead, the NameColumn property for child members is
used as the member names in a parent-child hierarchy.
Value columns
of an attribute: Identifies the column that provides the value of the
attribute. If the NameColumn element of the attribute is specified, the same
DataItem values are used as default values for the ValueColumn element. If the
NameColumn element of the attribute is not specified and the KeyColumns
collection of the attribute contains a single KeyColumn element representing a
key column with a string data type, the same DataItem values are used as
default values for the ValueColumn element.
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.
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.
AttributeHierarchyOrdered: 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.
