Overview of Microsoft MDX video lecture

Loading...

En provenance du cours de University of Colorado System

Data Warehouse Concepts, Design, and Data Integration

750 notes

University of Colorado System

Data Warehouse Concepts, Design, and Data Integration

750 notes

Cours 2 sur 5 dans Specialization Data Warehousing for Business Intelligence

This is the second course in the Data Warehousing for Business Intelligence specialization. Ideally, the courses should be taken in sequence. In this course, you will learn exciting concepts and skills for designing data warehouses and creating data integration workflows. These are fundamental skills for data warehouse developers and administrators. You will have hands-on experience for data warehouse design and use open source products for manipulating pivot tables and creating data integration workflows.You will also gain conceptual background about maturity models, architectures, multidimensional models, and management practices, providing an organizational perspective about data warehouse development. If you are currently a business or information technology professional and want to become a data warehouse designer or administrator, this course will give you the knowledge and skills to do that. By the end of the course, you will have the design experience, software background, and organizational context that prepares you to succeed with data warehouse development projects. In this course, you will create data warehouse designs and data integration workflows that satisfy the business intelligence needs of organizations. When you’re done with this course, you’ll be able to: * Evaluate an organization for data warehouse maturity and business architecture alignment; * Create a data warehouse design and reflect on alternative design methodologies and design goals; * Create data integration workflows using prominent open source software; * Reflect on the role of change data, refresh constraints, refresh frequency trade-offs, and data quality goals in data integration process design; and * Perform operations on pivot tables to satisfy typical business analysis requests using prominent open source software

À partir de la leçon

Multidimensional Data Representation and Manipulation

Now that you have the informational context for data warehouse development, you’ll start using data warehouse tools! In module 2, you will learn about the multidimensional representation of a data warehouse used by business analysts. You’ll apply what you’ve learned in practice and graded problems using WebPivotTable or Pivot4J, open source tools for manipulating pivot tables. At the end of this module, you will have solid background to communicate and assist business analysts who use a multidimensional representation of a data warehouse. After completing this module, you should proceed to module 3 to complete an assignment and quiz with either WebPivotTable or Pivot4J. Because Pivot4J can be difficult to install, I recommend completing the assignment and quiz using WebPivotTable.

Overview of Microsoft MDX video lecture6:52

Rencontrer les enseignants

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to Lesson 3, of Module 2,

on Multidimensional Data Representation and Manipulation.

I'm going to start with an important product question that I want you

to think about throughout this lesson.

What is the impact of the Microsoft MDX language on business intelligence tools?

Lesson three continues your learning about data cubes, from lessons one and two.

In lesson three, you will learn about the Microsoft Multidimensional Expressions

language, MDX, an important de facto standard for data cubes.

The presentation in this lesson is limited to the development history of MDX and

data cube representation in MDX.

In lesson four, you will see examples of basic MDX retrieval statements.

Because MDX is tedious and complex, you're not required to use MDX directly.

Rather, you should understand basic aspects of data representation and

retrieval in MDX.

To prepare you for usage of Pivot4J, a graphical implementation of MDX.

You have two learning objectives in this lesson today.

In your own words, you should be able to briefly explain basic MDX terminology.

As a reflective goal, you should be able to discuss the commercial significance of

the Microsoft MDX language.

MDX was developed in the late 1990s, with a specification in 1997,

and released as part of the Microsoft OLAP Services 7.0 in 1998.

MDX is now part of the Microsoft SQL Server Analysis Services.

After the initial specification in 1997, Microsoft made major revision to

MDX in 2005, with cooperation from Microsoft,

a Web Standards Group known as, the XMLA Council.

Specified mdXML,

as part of the extensible markup language, XML, for analysis standard.

XML is the underlying language, or metalanguage, for other web languages,

such as HTML.

MDX is an important de facto standard, used in Microsoft products,

other vendor products, and open source products.

MDX is the foundation for the Microsoft SQL Server Analysis Service and

Microsoft Excel Pivot Tables.

MDX was also adopted by a wide range of vendors for both commercial and

open source.

Prominent commercial vendors are Hyperion, IBM, and SAP in addition to Microsoft.

Prominent open source projects using MDX are JPivot,

Pivot4J and the Pentaho Business Analytics platform.

An MDX cube consist of dimensions and measures.

This cube structure shows two measures, quantity and sales,

along with five dimensions, markets, customers, product, time and order status.

In MDX, dimensions are composed of attributes.

For example, the product dimension has attributes line, vendor, and product.

Attributes can be independent, or related in a hierarchy.

In this MDX cube example, attributes are hierarchically related.

For the product dimension, the attributes of a hierarchy containing line as

a parent, vendor with line, and product within vendor.

In MDX, members are values of an attribute.

This snapshot depicts members of the attribute hierarchy for

the product dimension.

For example, Classic Cars is a member of the line attribute.

Autocart Studio Design is a member of the vendor attribute.

And 1968 Ford Mustang is a member of the product attribute.

This snapshot was derived from the Steel Wheels data cube in Pivot4J,

an open source product that partially supports MDX.

In this snapshot, members of the product dimension are shown in rows.

While members of the Time dimension are shown on the columns.

The cells contain sales values for the combination of member values.

One from each dimension.

For example, 1,514,407 is a sales value for Classic Cars in 2003.

A somewhat confusing part of MDX cubes is the presence of measures on

either the rows or columns.

In this snapshot,

the Sales dimension is stacked in the columns inside the Time dimension.

An MDX cube can also contain aggregations.

In this example, average sales are displayed for members of the product and

time dimension.

For example, 282,876 is the average Sales in 2003,

and 1,363,807 is the averages sales of Classic Cars.

MDX has some unique terminology for data cubes.

A tuple is a combination of members, one from each dimension.

A tuple identifies a cell.

In the Steel Wheels example, Classic Cars in 2003 identify a cell.

Access refers to a dimension from a source data cube used in a query.

In MDX, cubes typically have two axes, rows and

columns, although more axes can be used.

Multiple dimensions may be stacked, or embedded in the rows or columns.

A slicer refers to a tuple in the result of an MDX query expression.

You will see usage of slicers in the next lesson.

Lesson three, extended coverage of the data cube model, from lessons one and two.

In lesson three, you learned about background about

Microsoft Multidimensional Expressions or MDX.

MDX has evolved into a de facto standard for data cube representation and

manipulation.

This lesson demonstrated basic aspects of data cube representation for

MDX, including dimensions, measures, attributes and members.

In answer to the opening question,

MDX has a profound influence on business intelligence products,

despite that the language is not widely used in a direct way.

MDX is the foundation for Microsoft SQL Server Analysis Services and

pivot tables in Microsoft Excel.

MDX has been a prominent web standard for more than ten years.

MDX has been implemented in products by major commercial software vendors and

open source projects.

Most of the impact of MDX is indirect.

Business intelligence products have developed graphical interfaces to hide

the complexity and tedious aspects of MDX.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play