Explain the structure of MDX query?

 MDX functions?

MDX KPI Functions:

KPICurrentTimeMember, KPIGoal, KPIStatus, KPITrend

KPIValue, KPIWeight  

MDX Metadata Functions:

Axis, Count (Dimension), Count (Hierarchy Levels), Count (Tuple)

Hierarchy, Level, Levels, Name,Ordinal, UniqueName  

MDX Navigation Functions:

Ancestor, Ancestors, Ascendants, Children

Cousin, Current, CurrentMember, CurrentOrdinal

DataMember, DefaultMember, FirstChild, FirstSibling

IsAncestor, IsGeneration, IsLeaf, IsSibling

Lag, LastChild, LastSibling, Lead

LinkMember, LookupCube, NextMember, Parent

PrevMember, Properties, Siblings, UnknownMember

To learn MSBI go through ITGuru's MSBI Online Training

MDX Other Functions:

CalculationCurrentPass, CalculationPassValue, CustomData, Dimension

Dimensions, Error, Item (Member), Item (Set)

Members (String), Predict, SetToArray

MDX Set Functions:

AddCalculatedMembers, AllMembers, BottomCount, BottomPercent

BottomSum, Crossjoin, Descendants, Distinct

Except, Exists, Extract, Filter

Generate, Head, Hierarchize, Intersect

MeasureGroupMeasures, Members (Set), NonEmpty, NonEmptyCrossjoin

Order, StripCalculatedMembers, Subset, Tail

TopCount, TopPercent, TopSum, Union

Unorder   

MDX Statistical Functions:

Aggregate, Avg, CoalesceEmpty, Correlation

Count (Set), Covariance, CovarianceN, DistinctCount

LinRegIntercept, LinRegPoint, LinRegR2, LinRegSlope

LinRegVariance, Max, Median, Min

Rank, RollupChildren, Stdev, StdevP

Sum, Var, VarP, VisualTotals

MDX String Functions:

MemberToStr, NameToSet, Root, SetToStr

StrToMember, StrToSet, StrToTuple, StrToValue,TupleToStr, UserName  

MDX SubCube Functions:

Leaves, This 

 

MDX Time Functions:

ClosingPeriod, LastPeriods, Mtd, OpeningPeriod

ParallelPeriod, PeriodsToDate, Qtd, Wtd,Ytd   

MDX UI Functions:

DrilldownLevel, DrilldownLevelBottom, DrilldownLevelTop, DrilldownMember

DrilldownMemberBottom, DrilldownMemberTop, DrillupLevel, DrillupMember

ToggleDrillState

  

MDX Value Functions:

IIf, IsEmpty, MemberValue, ValidMeasure, Value

3. What is the difference between set and tuple?

Tuple: It is a collection of members from different Dimension

Set: collection of one or More tuples from same dimension

4. What do you understand by Named set? 

Named sets are simply MDX expression that return a set of members.

To define a named set:

CREATE SET MySet AS SomeSetExpression

or you can use

WITH SET MySet AS SomeSetExpression

The different between the two is the scope. Using WITH specifies the scope of the named set as the query, so as soon as the query finishes executing, that named set is gone. Using CREATE, the scope of the query is limited to the MDX session as long as you don't drop the set.

When defining your named set, you also have the option to specify when the named set is evaluated using DYNAMIC or STATIC, as seen here:

CREATE DYNAMIC SET MySet AS SomeSetExpression

or

CREATE STATIC SET MySet AS SomeSetExpression

A Dynamic Named Set respects the context of a query's subcube and the query's WHERE clause and is evaluated at the time the query is executed.

A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in WHERE clause.

Example 1:

CREATE SET DateRange AS

[Date].[Calendar Year].&[2001] : [Date].[Calendar Year].&[2004]

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,

DateRange ON ROWS

FROM [Adventure Works]

Example 2:

WITH SET SouthEastUS AS

{[Geography].[State-Province].&[AL]&[US],

[Geography].[State-Province].&[FL]&[US],

[Geography].[State-Province].&[GA]&[US],

[Geography].[State-Province].&[SC]&[US]}

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,

SouthEastUS ON ROWS

FROM [Adventure Works]

5. How will you differentiate among level, member, attribute, hierarchy?

6. What are the differences among exists, existing and scope?

7. What will happen if we remove CALCULATE keyword in the script?

8. How will you pass parameter in MDX?

9. What is the difference between .MEMBERS and .CHILDREN?

10.What is the difference between NON EMPTY keyword and NONEMPTY() function?

NON EMPTY:

Non Empty is prefixed before the sets defining the axes and is used for removing NULLs.

 In short, only the rows having NULL for all the members of the set defined in the column axis is filtered out. This is because the Non Empty operator works on the top level of the query. Internally, the sets defined for the axes are generated first and then the tuples having NULL values are removed.

SELECT

 NON EMPTY

  {

   [Measures].[Hits]

   ,[Measures].[Subscribers]

   ,[Measures].[Spam]

  } ON COLUMNS

,{

  [Geography].[Country].Children

 } ON ROWS

FROM [Blog Statistics];

NONEMPTY():

The NonEmpty() returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. Suppose we want to see all the measures related to countries which have a non-null value for Subscribers

SELECT

 {

  [Measures].[Hits]

  ,[Measures].[Subscribers]

  ,[Measures].[Spam]

 } ON COLUMNS

,{

  NonEmpty

  (

   [Geography].[Country].Children

   ,[Measures].[Subscribers]

  )

 } ON ROWS

FROM [Blog Statistics];

11. Functions used commonly in MDX like Filter, Descendants, BAsc and others

12. Difference between NON EMPTY keyword and function, NON_EMPTY_BEHAVIOR, ParallelPeriod, AUTOEXISTS

13. Difference between static and dynamic set

CREATE DYNAMIC SET MySet AS SomeSetExpression

or

CREATE STATIC SET MySet AS SomeSetExpression

A respects the context of a query's subcube and the query's WHERE clause and is evaluated at the time the query is executed.

A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in WHERE clause.

14. Difference between natural and unnatural hierarchy, attribute relationships

15. Difference between rigid and flexible relationships

16. Write MDX for retrieving top 3 customers based on internet sales amount? 

17. Write MDX to find current month's start and end date?

18. Write MDX to compare current month's revenue with last year same month revenue? 

19. Write MDX to find MTD(month to date), QTD(quarter to date) and YTD(year to date) internet sales amount for top 5 products?

20. Write MDX to find count of regions for each country?

21. Write MDX to rank all the product category based on calendar year 2005 internet sales amount?

22. Write MDX to extract nth position tuple from specific set?

syntax:

Index syntax:

Set_Expression.Item(Index)

String expression syntax:

Set_Expression.Item(String_Expression1 [ ,String_Expression2,...n])

The following example returns ([1996],Sales):

{([1996],Sales), ([1997],Sales), ([1998],Sales)}.Item(0)

23. Write MDX to set default member for particular dimension?

24. What are the performance consideration for improving MDX queries?

25. Is Rank MDX function performance intensive? 

26. Which one is better from performance point of view...NON Empty keyword or NONEMPTY function?

27. How will you find performance bottleneck in any given MDX?

28. What do you understand by storage engine and formula engine?

More info go through our ITGuru's MSBI Online Course Blog

What is SQL Server Integration Services (SSIS)?

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.  SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

 

It is the new data transformation standard for SQL Server 2005, 2008 and 2012 and has replaced the old SQL Server Data Transformation Services.

First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions. 

 

To more information go through MSBI Course

What are the MSBI benefits over the other BI tools?

 Microsoft Business Intelligence (MSBI) is a series of tools that provide us with business intelligence solutions, along with data mining solutions. MSBI, along with Visual Studio and SQL, allows us to make great choices in our business operation.

                                    

Three methods to perform different tasks are included in the Microsoft BI stack.

• SSIS: It is an integration tool and stands for Integration Services for SQL Server.

• SSAS: It stands for SQL Server Analysis Services and is an analysis tool.

• SSRS: It is a reporting tool and stands for Reporting Services for SQL Server.

For all our BI needs, MSBI is the solution that overcomes all the IT problems and high-cost issues. For Business Intelligence solutions, it is one of the strongest instruments.

As listed below, a few of its benefits are as follows.

Visualization and simple data exploration

The ability to accurately explore and visualize information is a great skill in this world of exploding data. It's not a matter of urgency to get accurate data visualizations, but it is very necessary. In data visualization, MSBI instruments are just amazing. This tool is fantastic in the process of data visualization as opposed to other Business Intelligence software.

For Business Intelligence, MSBI makes use of pie charts. It is a round map split into a large number of numbers and quantities representing slices. A certain amount is specified in any slice that looks like a pie.

The use of 3D graphs in MSBI is fantastic. The 3-dimensional maps are often used to display stuff in a vast number of dimensions by the scientific community. We can use bright colors with the MSBI tool to highlight items in our company study.

In the paper, designing sparklines using MSBI helps a lot. With highly intense data that tells us about the historical context, these sparklines are simple. It shows the beginning and end of the article when we highlight the top and bottom points using a sparkline.

With MSBI, we can also use trellis graphs that provide us with a time-limit comparison of data patterns.

Although we have a wide collection of data, using these scatter plots, we can figure out the correlations of the quantitative pieces. In data visualization operations, MSBI utilizes these scatter plots. It also uses Power View to get the charts visualized. We can use this tool to remove the filled color and through its form, size, and color to identify different data, thereby reducing complications.

In the reporting of knowledge processing, MSBI makes excellent use of maps to get an understanding of the geospatial context.

Self-service BI Tool Managed

Self-service Business Intelligence resources are offered by Microsoft Business Intelligence. The Microsoft Excel that employees use every day to provide reports and deliver Data Analytics results is an example of this self-service BI method.

Initially, Business Intelligence instruments were only handled by data analysts, administrators, and experts with full knowledge of BI. As workers encountered issues with the use of BI resources in organizations, they had to call the IT department for support. For months, people had to wait for the IT department to respond to their queries.

With the launch of the self-service Business Intelligence tools in the industry, however, all became quite comfortable. The primary goal of these self-service instruments is to be able to operate this BI tool for all workers regardless of whether they have a degree in statistics or any other BI-related field. MSBI is a platform for self-service and, compared to traditional ones, is very simple to use.

Self-service BI aims to generate new insights for frontline workers, by more ad-hoc analysis, to obtain new findings from data.

Full Use of Features of Native MS Excel

To report data analysis, MSBI makes use of Excel to the heart. Using Excel makes it simple to gather and use data to create eye-catching reports, dashboards, etc.

In Excel, we can perform a wide range of tasks, from collecting data to making reports according to the BI requirements of your business.

To get the data into the Excel sheet, MSBI uses the SQL Server table and its analysis works. To merge and optimize data using internal as well as external data sources, we can use Power Query. For formatting columns, it uses Flash Fill and designs a data model where we can bring two or three tables together. In this way, we can sort and organize our data for our work by using Excel in MSBI. Excel can also be used to generate a vast variety of reports, including charts and tables, and 3D data visualizations can be generated using Power Map. We may use Power View to provide tables, maps, and graphs. By the use of Excel, in which we can deploy filters, explore and analyze data, etc., this is how data visualization becomes clearer.

Become a BI Master for Strength

MSBI offers dashboards and scorecards of great quality. This offers access to information and performance management through interactive, content-driven dashboards and scorecards that integrate data from multiple systems into a single browser-based interface from virtually any data source within the organization. We can bring together all the data from different systems into just one browser using these dashboards and scorecards. Through these dashboards, they provide access to different data and job assessment from almost every knowledge resource.

MSBI Web Services Support

Microsoft BI clarifications influence your .NET and SQL Server active technology funds to build up well-off integrated therapy and analytics practices. This enables customers to improve access to reliable, up-to-date decision-making data. With many years of quality experience, it offers well-built expertise to provide consumers with abundant benefits while achieving better value-to-cost ratios.

Market Solutions end-to-end

MSBI provides Market Insight and great ideas and is a great provider of business decisions. Top-to-bottom market strategies are offered by MS BI. When used with the BI, this BI instrument provides great solutions for deployment. Microsoft BI uses Visual Studio with SQL Server to push a tremendous range of quality business solutions. It has its own distinct and entirely independent tools for different services. For different business solutions, such as online transaction processing, data warehouse, data mart, BI semantic model, and data mining, various MSBI tools are used. Most companies all over the world have their branches created. Therefore the information of these companies is also circulated all over.

Various sectors in a company deal with various data. Patterns and innovations in data storage keep updating. For the storage of data, the MSBI tool uses SQL Server.

Applications Data Warehouse

Market Intelligence still has a solution for all the criteria of Data Analytics. SSIS helps to get the data warehouse from data sources that are different. The data warehouse is ideal for collecting different information required to effectively conduct the analytical process. It has numerous sources of data with all kinds of information. SSIS is a tool for Microsoft BI that does the process of ETL.

There are three sub-processes to the ETL process. They are data extraction, transformation, and load. The data from the source is collected in the extraction process. The data is translated to the appropriate format in the destination database in the transformation process. The transformed data is loaded into the destination database in the load phase.

It is washed and made ready to be uploaded into the data warehouse until the data is collected. It is sent to the landing area and then to the data warehouse after it has been washed.

Conclusion

I hope you have a brief idea about the benefits of MSBI over other tools. You can gain expertise in MSBI through MSBI online training.

Explain about MDX in MSBI

 A query language used to collect data from multidimensional databases is MDX (Multi-Dimensional eXpressions). More specifically, MDX is used to query Research Services for multidimensional data and supports two distinct modes. 

MDX is not a proprietary language; it is a query language based on specifications that are used to recover data from SSAS databases. MDX is part of the Microsoft supported OLE DB for OLAP specification. 

They could refer either to the MDX query language or to MDX expressions if one refers to MDX. While the language of the MDX query has a syntax close to that of MSBI SQL, it is substantially different,More info through ITGuru's MSBI Course.

Concepts of Fundamentals in MSBI

Typically, a multidimensional database is referred to as a cube. The cube is the basis of a multidimensional database, and more than two dimensions are usually used in each cube.

A set may contain zero tuples, one tuple or more. A zero-tuple set is known as an empty set. An empty set is depicted as follows.

<pre>{ } <pre>{ }

{Customer.Country.Australia, Customer.Country.Canada,Customer.Country.Australia}</pre> {Customer.Country.Australia}</pre>

This collection includes two instances of the Customer.Country.Australia. tuple. Since a member of a dimension alone forms a tuple, in MDX queries it can be used as such. Likewise, if there is a tuple that only one hierarchy defines, we do not need the parentheses to define it as a set. We do not need curly braces to show when there is a single tuple defined in a question.

Queries on MDX in MSBI

For an MDX query, the syntax is as follows.

<pre>[WITH <formula expression> [, <formula expression> ...]]] WITH <formula expression>

SELECT [<axis expression>, [<axis expression> ...]]]

[<cube expression>]] FROM

[WHERE]</pre> [slicer expression]

The keywords WITH, Pick, FROM, and WHERE are referred to as clauses, along with the phrases that accompany them. Anything defined in square brackets in the previous MDX query template means that it is optional; that is, in an MDX query, that portion of the query is not mandatory. We can see that the WITH and WHERE clauses are optional because inside square brackets they are enclosed.

For custom calculations and procedures, the WITH clause is usually used.

The Pick statement and definition for the axis in MSBI

A subset of the multidimensional data in an OLAP cube is retrieved using the MDX SELECT statement. The SELECT statement in MSBI SQL allows one to determine which columns in the row data we retrieve will be included, which is viewed as two-dimensional data. You have the X and Y axes if you consider a two-dimensional scheme of coordinates. For COLUMNS, the Y-axis is used and ROWS uses the X-axis. The SELECT statement in MDX is defined in a way that allows data with more than just two dimensions to be retrieved. Indeed, MDX gives you the ability to retrieve data on one, two, or multiple axes.

The SELECT statement syntax is:

SELECT [<axis expression>, [<axis expression> ...]]]

After the Pick, the axis expressions defined to relate to the dimension data that we are interested in recovering. As the information from these dimensions is projected onto the respective axes, these dimensions are referred to as axis dimensions.

The syntax for expression is-axis.

<pre><axis expression>:= < set > ON (axis | AXIS(axis number) | number of the axis)</pre> < set > ON (axis | AXIS(axis number)

In order to retrieve multidimensional result sets, Axis dimensions are used. The set, a set of tuples, is defined to form a dimension of the axis. In the SELECT statement, MDX gives you the option to define up to 128 axes. Aliases have the first five axes. COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS are them. It is also possible to specify axes as a number, so you can specify more than five dimensions in your SELECT statement.

Let's take the example below:

Pick Measures.[Amount of Internet Sales] ON COLUMNS,

[Customer].[Country]. ON ROWS Participants,

[The Product].[The Product Line].MEMBERS ON PAGES

FROM [Works of Adventure]

In the SELECT statement, three axes are defined. Steps, consumers, and product data from dimensions are projected on to the three axes to form the dimensions of the axis.

This sentence may be written equivalently as follows.

SELECT Measures.[Amount of Internet Sales] ON 0, 0,

[Customer].[Country].MEMBERS ON 1, 1, ON 1, ON 1, ON 1, ON 1,

[The Product Line].[The Product Line].MEMBERS ON 2

FROM [Works of Adventure]

Dimensions of Axis in MSBI

When we describe a SELECT sentence, the axis dimensions are what we construct. For each dimension, a SELECT statement defines a set; COLUMNS, ROWS, and additional axes. Axis dimensions retrieve and hold data for multiple members, not just single members, unlike the slicer dimension.

The Definition of the FROM Clause and Cube in MSBI

In an MDX query, the FROM clause specifies the cube you retrieve and evaluate data from. In an MSBI SQL query, it's close to the FROM clause where you define a table name. For any MDX query, the FROM clause is a requirement.

The FROM clause's syntax is:

<cube expression> from FROM

The cube expression denotes a cube's name or a subsection of a cube that we want to retrieve data from. We can specify more than one table in MSBI SQL's FROM clause, but only one cube name can be specified in the MDX FROM clause.

That is, all portions of axis expressions are obtained from the sense of the cube defined in the “FROM” clause.

[Internet Sales Amount] ON COLUMNS Pick [Measures].

FROM [Works of Adventure]

This is a valid MDX query that retrieves information on the X-axis from the [Internet Sales Amount] measure. The measured data is retrieved from the [Adventure Works] cube background. While the FROM clause limits us to operate with only one cube or part of a cube, using the MDX LookupCubefunction, we can retrieve data from other cubes.

If there are two or more cubes with similar dimension members, the LookupCubefunction uses the common dimension members to retrieve steps outside the range of the current cube.

The WHERE Definition for Clause and Slicer in MSBI

We issue queries that return only portions of the total data available in a given table, collection of joined tables, and/or joined databases in any relational database work that we do. This is achieved using MSBI SQL statements that define what information we are doing and do not want to return as a result of running your query.

Here is an example of an unrestricted MSBI SQL query containing sales details for goods on a table called Product:

CHOOSE *

Product from FROM

In MSBI SQL, the two columns chosen are now the COLUMNS and ROWS axes. The state in the MSBI SQL WHERE clause, which is a string analogy, is translated into the MDX WHERE clause, which refers to a cube slice that includes silver-colored products.

The Dimension of the Slicer in MSBI

When you describe the WHERE sentence, the slicer dimension is what you construct. It is a filter that excludes unwanted components and measurements.

Any axis in the cube contains the slicer dimension, including those that are not specifically included in any of the axes requested. In the slicer axis, default hierarchy members that are not included in the query axes are included. MDX will evaluate such tuples as a collection when there are tuples defined for the slicer axis and the results of the tuples are aggregated based on the measures used in the question and the aggregation function of that particular measure.

Conclusion

I hope you have an idea by now about MDX in MSBI. You can learn more about MDX expressions and other functions through MSBI online training.