Pages

Showing posts with label SSAS. Show all posts
Showing posts with label SSAS. Show all posts

Thursday 21 March 2013

Difference between MDX and T-SQL


 MDX  Vs  T-SQL

i)                    The  ability of MDX  to reference multiple dimensions but SQL refers to only two dimensions, columns and rows when processing queries.

ii)                  In SQL, the SELECT clause is used to define the column layout for a query, while the WHERE clause is used to define the row layout. However, in MDX the SELECT clause can be used to define several axis dimensions, while the WHERE clause is used to restrict multidimensional data to a specific dimension or member.

iii)                The process of creating an SQL query is also different than that of creating an MDX query.

iv)                The visualization of an SQL result set is intuitive; the set is a two-dimensional grid of columns and rows. The visualization of an MDX result set is not as intuitive, however. Because a multidimensional result set can have more than three dimensions.

v)                  To refer to such two-dimensional data in SQL, the name of a column and the unique identification of a row, in whatever method is appropriate for the data, are used to refer to a single cell of data, called a field. However, MDX uses a very specific and uniform syntax to refer to cells of data, whether the data forms a single cell or a group of cells.

Saturday 2 June 2012

When should you use a Star and when should you use Snowflake Schema

Q: 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.

Advantage and disadvantage of Snowflake Schema


Q: 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.

Snowflake Schema


Q : What is Snowflake Schema in SSAS ?

A Snowflake Schema contains fact table and dimension tables, with the fact table storing facts about business transactions  and some columns of the fact containing foreign key references to the dimension tables. The dimension tables of a snowflake schema are typically normalized to the third normal form (3 NF) or higher. As a result the data for a given dimension is spread out over multiple tables, and a diagram of the database resembles a snowflake.


Sunday 27 May 2012

Difference between a data source and data source view


Q 3 : 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.

Difference between online and offline mode in SSAS

Q 2. 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 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.

Aggregation types are available for measures in BIDS


Q 4 : 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


Q 1. 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.


Thursday 19 April 2012

Cube, Fact Table, Dimension Table


Q : 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.

Q : 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.

Q : 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.