Monthly Archives: October 2015

Dimensions vs. measures, calculations vs. Aggregations

Aggregate function will use the aggregation function of the measure in the fact table irrespective if it is a COUNT() or SUM().
While Sum function will always give you a sum of the measure in the fact table.

Dimensions
Dimensions are those things you want to track. They’re referrers, pages, country of origin, product category and other items whose attributes are often non-numerical.

Measures
Measures are the quantities you want to measure. Visits, page views, hits, bounce rate and other items that can be quantified numerically.

Remembering Dimensions vs. Measures
dimensions can be thought of as the rows in a spreadsheet, while measures are like the columns. In fact, for web analytics reports, you’re likely to see your data displayed that way.

MDX and SQL difference

The Multidimensional Expressions (MDX) syntax similar to (SQL). You can even duplicate some of the functionality provided by MDX in SQL.

The principal difference between SQL and MDX is the ability of MDX to reference multiple dimensions. Although it is possible to use SQL to query cubes SSAS, MDX provides commands that are designed specifically to retrieve data as multidimensional data structures with almost any number of dimensions.

SQL refers to only two dimensions, columns and rows, when processing queries. The terms “column” and “row” have meaning in SQL syntax.

MDX, in comparison, can process one, two, three, or more dimensions in queries. Because multiple dimensions can be used in MDX, each dimension is referred to as an axis. The terms “column” and “row” in MDX are simply used as aliases for the first two axis dimensions in an MDX query; there are other dimensions that are also aliased, but the alias itself holds no real meaning to MDX. MDX supports such aliases for display purposes; many OLAP tools are incapable of displaying a result set with more than two dimensions.

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.

In SQL, the WHERE clause is used to filter the data returned by a query. In MDX, the WHERE clause is used to provide a slice of the data returned by a query. While the two concepts are similar, they are not equivalent.

The SQL query uses the WHERE clause to contain an arbitrary list of items that should (or should not) be returned in the result set. While a long list of conditions in the filter can narrow the scope of the data that is retrieved, there is no requirement that the elements in the clause will produce a clear and concise subset of data.

In MDX, however, the concept of a slice means that each member in the WHERE clause identifies a distinct portion of data from a different dimension. Because of the organizational structure of multidimensional data, it is not possible to request a slice for multiple members of the same dimension. Because of this, the WHERE clause in MDX can provide a clear and concise subset of data.

The process of creating an SQL query is also different than that of creating an MDX query. The creator of an SQL query visualizes and defines the structure of a two-dimensional rowset and writes a query on one or more tables to populate it. In contrast, the creator of an MDX query usually visualizes and defines the structure of a multidimensional dataset and writes a query on a single cube to populate it. This could result in a multidimensional dataset with any number of dimensions; a one-dimensional dataset is possible, for example.

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, it can be challenging to visualize the structure. 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.

Although SQL and MDX share similar syntax, the MDX syntax is remarkably robust, and it can be complex. However, because MDX was designed to provide a simple, effective way of querying multidimensional data, it addresses the conceptual differences between two-dimensional and multidimensional querying in a consistent and easily understood fashion.

basic MDX questions

What is MDX?
MDX is a language that articulates selections, calculations, and some metadata definitions against an Online Analytical Processing (OLAP) database, and provides some capabilities for specifying how query results are to be represented.

Where you use MDX in SQL Server 2005 OLAP?
MDX queries and expressions in SQL Server 2005 Analysis Services are used to do the following:
• Return data to a client application from a SQL Server 2005 Analysis Services cube.
• Format query results.
• Perform cube design tasks, including the definition of calculated members, named sets, scoped assignments, and key performance indicators (KPIs).
• Perform administrative tasks, including dimension and cell security.

Earn 100$ a Day With Adsense – Get This Ebook

What are different APIs that support MDX?
• OLE DB for OLAP
• ADO MD,
• ADOMD.Net,
• XMLA(XML for Analysis),
• Hyperion Sybase C and Java APIs,

What is Tuple in SSAS?
• A tuple is the intersection of one (and only one) member taken from one or several of the dimensions in the cube. A tuple identifies (or has the potential to identify) a single Cell in the multi-dimensional matrix.
• To compose a tuple with more than one dimension, you must wrap the members in parentheses (Tuple).
• You can’t compose an empty tuple. () is not a valid tuple.

Why don’t a tuple can have more than one member from same dimension?
• Well, if you do, it is inevitable that the ‘tuple’ that you create will end up pointing to more than one cell.

What is SET in SSAS?
• A set is a collection of tuples with the same dimensionality. It may have more than one tuple, but it can also have only one tuple, or even have zero tuples, in which case it is an empty set.
• If Set has three tuples with different dimensionality then we will not call that as SET.
• The curly braces { Tuple1, Tuple2 }, are used to denote the Set

What are query Axes in MDX?
• Query axes specify the edges of a cell set returned by a Multidimensional Expressions (MDX) SELECT statement.
• A query can have up to 128 query axes. The first five are named axes columns, Rows, pages, sections and chapters.
• However, a query that uses axis 1 must also use axis 0, and a query that uses axis 2 must also use axes 1 and 0. You cannot skip an axis in a query-you’ll get an error.
• The axis designations can come in any order.
• For axes beyond axis(4), you must use the numbers, because there are no names for them.

Explain the difference between Query Axis and Slicer Axis ?

Query Axis
•Query axes specify the edges of a cellset returned by a Multidimensional Expressions (MDX) SELECT statement.
•The Quesry axis is defined in the SELECT clause of the SELECT statement in MDX.
•Query axes—the set of hierarchies from which data is retrieved for multiple members

Slicer axis
•The slicer axis filters the data returned by the Multidimensional Expressions (MDX) SELECT statement.
•The slicer axis is defined in the WHERE clause of the SELECT statement in MDX.
•Slicer axis—the set of hierarchies from which data is retrieved for a single member.

Explain the MDX SELECT Statement?
• In MDX, the SELECT statement specifies a result set that contains a subset of multidimensional data that has been returned from a cube.
• The ON keyword is used with an axis name to specify where dimensions from your database are displayed
• Each Axe is made up of either one or more tuple or a Set.
• MDX uses curly braces, { and }, to enclose a set of elements from a particular dimension or set of dimensions.
• We can separate different elements with commas (,). Element names may be enclosed by [ ] characters, and may have multiple parts separated by dot (.) characters.
• The FROM clause in an MDX query names the cube from which the data is being queried.
• A WHERE clause that optionally determines which dimension or member to use as the slicer axis that restricts the extracting of data to a specific dimension or member.

What is the use of colon operator in MDX Select statement?
• You can specify a set as a range of members in that order by listing two members from the same level as endpoints and putting a colon between them to mean “These members and every member between them.”

• For example
SELECT
{ [Time].[Sep,2004] : [Time].[Mar,2005] } on columns,
{ [Product].[Tools] : [Product].[Home Audio] } on rows
FROM [Sales]
WHERE ([Customer].[Lubbock, TX], [Measures].[Unit Sales])

How to establish cube context in MDX query?
• Every MDX query runs within a specified cube context. In the SELECT statement, the FROM clause determines the cube context.
• Although the FROM clause specifies the cube context as within a single cube, this does not have to limit you from working with data from more than one cube at a time.
• You can use the MDX LookupCube function to retrieve data from cubes outside the cube context. Additionally, functions such as the Filter function, are available that allow temporary restriction of the context while evaluating the query.

What is use of .Members function in MDX?
• The .Members operator takes a dimension, hierarchy, or level on its left-hand side,
and returns a set of all members associated with that metadata scope. For example, [Customer].Members results in the set of all customers

What is the use of .Children Function in MDX?
• The Children function returns a naturally ordered set that contains the children of a specified member. If the specified member has no children, this function returns an empty set.

What is use of Descendants() function and specify the Flags usage?
• Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
• Syntax :- Descendants(Member_Expression/Set_expressoin [,Level_Expression /Distance [ ,Desc_Flag ] ] )

What is use of NON EMPTY Keyword in MDX?
• The NON EMPTY Keyword is used to remove the entirely empty slices from the query result.

What precisely Dimensionality of Tuple refers to?
• The “dimensionality” of a tuple refers to the set of dimensions whose members compose it. The order in which dimensions appear in a tuple is an important part of a tuple’s dimensionality

Explain CrossJoin() function use in MDX?
• In many cases, you will want to take the cross-product of members (or tuples) in two different sets (that is, specify all of their possible combinations). The CrossJoin() function is the most direct way of combining the two sets in this way.
• The syntax is as follows: CrossJoin (set1, set2)
• CrossJoin() only takes two sets as inputs. If you want to take the CrossJoin() of three or more sets, such as times, scenarios, and products, you can do it by nesting calls to CrossJoin().

Explain the Filter() function use in MDX?
• Filter() lets you reduce a set by including in the resulting set only those elements that meet some criteria.
• Filter() takes one set and one Boolean expression as its arguments and returns that subset where the Boolean expression is true.
• For example, the expression
Filter ( { [Product].[Product Category].Members }, [Measures].[Dollar Sales] >= 500 )

Explain the Order() function use in MDX?
• To put the tuples in a set into a sequence based on associated data values, we need to use the Order() function.
• The syntax for the Order() function is: Order (set1, expression [, ASC DESC BASC BDESC])

Explain DIMENSION PROPERTIES function usage in MDX?
• DIMENSION PROPERTIES a function is used to retrieve the properties of the members in cube.

SQL server 2012 new features, differences with SQL sever 2008

https://mcpmag.com/articles/2012/03/14/top-12-features-of-sql-server-2012.aspx

1. AlwaysOn Availability Groups
2. Windows Server Core Support

3. Columnstore Indexes — Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles

5. Enhanced Auditing Features –the log.

6. BI Semantic Model — infographics

7. Sequence Objects — .

8. Enhanced PowerShell Support — .

9. Distributed Replay — .

10. PowerView –.

11. SQL Azure Enhancements —

12. Big Data Support — NoSQL support.

Differences

Sql server 2012:
1.The SQL Server 2012 uses 48 bit precision for spatial
2.In SQL server 2012 has unlimited concurrent connections
3.By default it supports 15,000 partitions
4.Available new string functions CONCATE and FORMAT
5.Available new conversion functions are PARSE ,TRY_CONVERT,TRY_PARSE
6.Microsoft.SqlServer.Dac and Microsoft.SqlServer.Dac.Extensions are introduced to perform operations on Packages

Sql server 2008:
1.Maximum number concurrent connections to SQL Server 2008 is 32767
2.The SQL Server 2008 uses 27 bit precision for spatial
3.It can support only 1000 partitions
4.The CONCATE and FORMAT not available
5.Not available conversion function which are mentioned in SQL 2012