Microsoft MDX statements video lecture

Loading...

Do curso por University of Colorado System

Conceitos, Modelagem e Integração de Dados em Data Warehouse

603 ratings

University of Colorado System

Conceitos, Modelagem e Integração de Dados em Data Warehouse

603 ratings

Curso 2 de 5 em Specialization Armazenagem de Dados e Inteligência de Negócios

Este é o segundo curso na especialização em 'Data Warehousing' para Inteligência de Negócios. O ideal é obedecer a sequência dos cursos. Neste curso, você aprenderá conceitos e habilidades interessantes para modelar data warehouses e criar fluxos de integração de dados. Estas são habilidades fundamentais para desenvolvedores e administradores de data warehouse. Você terá experiências práticas em projeto de data warehouse e usará produtos de fonte aberta para manipular tabelas dinâmicas e para criar fluxos de integração de dados. Você também adquirirá conhecimento conceitual sobre modelos de maturidade, arquiteturas, modelos multidimensionais, e práticas de gestão, provendo uma perspectiva organizacional sobre o desenvolvimento de data warehouse. Se você é um profissional de tecnologia da informação ou de negócios e quer se tornar um construtor ou administrador de data warehouse, este curso lhe fornecerá conhecimento e habilidades para tanto. Ao final do curso, você terá a experiência de projetar, o conhecimento de software, e o contexto organizacional que o prepara para ser bem sucedido em projetos de desenvolvimento de data warehouse. Neste curso você criará modelos de data warehouse e fluxos de integração de dados para satisfazer as necessidades de inteligência de negócios das organizações. Ao concluir este curso, você estará apto a: * Avaliar a maturidade do data warehouse de uma organização e o alinhamento com a arquitetura de negócios; * Criar um fluxo de integração de dados usando um proeminente software de tecnologia aberta; * Refletir sobre o papel de alterar dados, restrições de atualização, negociações de frequência de atualização, objetivos de qualidade de dados na modelagem do processo de integração de dados; e * Realizar operações em tabelas dinâmicas para satisfazer solicitações típicas de análises de negócios usando proeminente software de tecnologia aberta.

Na lição

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 Pivot4J, an open source tool 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.

Microsoft MDX statements video lecture6:07

Conheça os instrutores

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to lesson four,

module two on multi-dimensional data representation and manipulation.

I'm gonna start with an important product question

that I want you to think about throughout this lesson.

Why is MDX used more in graphical tools

than directly used by analysts writing MDX retrieval statements?

Lesson four continues your learning about MDX from lesson three.

In lesson four, you will see simple MDX statements.

You will learn about the select, from and where clause in MDX statements.

Because MDX is tedious and

complex, you are not required to write MDX statements in assignments.

Rather then you should understand basic aspects of data representation and

retrieval in MDX to prepare you for the guided practice and

assignment involving pivot 4J a graphical implementation of MDX.

You have three learning objectives in this lesson today.

In your own words you should be briefly explain simple MDX statements.

As reflective goals you should compare SQL to MDX and

gain insight into the complexity of MDX.

Although the MDX select statement appear similar to SQL select statement,

there's a fundamental difference.

The result of a SQL select statement is a table.

One of the result of the MDX statement is a data cube.

Tables are two dimensional objects with rows and columns.

Data cubes are n-dimensional possibly of summary calculations.

Different mathematical approaches, relational algebra for tables, and

matrix algebra for data cubes, provide the foundation for query languages.

The difference in underline objects,

tables versus data cubes, makes retrieval languages where they're different,

even though some important keywords are the same.

Before inspecting MDX retrieval statements,

let's briefly consider the major clauses in MDX retrieval statements.

The select clause, in SQL the columns are specified.

In MDX,

the axis dimensions from the source data cube are specified, the FROM clause.

In SQL, a list of tables or joint expressions, is given the FROM clause.

In MDX, only the source data cube is specified, WHERE clause.

In SQL, conditions restricting rows in a result are specified.

In MDX, conditions on the cells in the results data cube are specified.

Note that the dimensions in the result cube are usually different

than the dimensions in the source data cube.

This snapshot, taken from Pivot 4J, shows the data cube result and

associated MDX SELECT statement.

In the SELECT clause, members of the time dimension, 2003 and 2004,

appear in the rows.

The columns axis contains two measure, sales and quantity.

The from clause indicates that steel wheels sales is the source data cube.

The WHERE clause restricts sales and quantity calculations to classic cars.

The WHERE clause condition is known as the slicer.

The cross joint operator combines multiple dimensions or measures on a query access.

In this statement,

the cross joint operator combines selected members of the order status,

that is shipped an canceled, and the sales quantity measures on the columns access.

As in the first example, the WHERE clause restricts sales and

quantity calculations to classic cars.

Note that the Shipped and Cancelled status numbers are not exhaustive.

So Sales and Quantity values for

other Order Status members are not reflected in the result.

These two examples show the impact of a slicer condition.

The MDX statement on the right has a WHERE condition

to limit the calculated results to the North American member

In the territory attribute of the markets dimension.

The sales values in the cells are smaller because of the slicer condition.

For example, the sales of classic cars in 2003 is 4,959 for North America,

but 12,762 for all territories in 2003.

The statements in both examples depict multiple dimensions on

the columns dimension.

And the usage of the default measure, sales, not shown in either query access.

If no measure is specified on a query access

the default measure is shown in the cells.

Lesson four extended coverage of the MDX language from lesson three.

In lesson three, you learned about data cube representation in MDX,

including dimensions, measures, asterisks and members.

This lesson demonstrate basic parts of the SELECT from and WHERE clauses.

You learned about specifying dimensions in the query axes, the Crossjoin operator to

combine dimensions on on an axis and slicer conditions in the WHERE clause.

In answer to the opening questions, MDX can be complex and

tedious in practice, especially for analysts familiar with SQL.

MDX query formulation involves more complexity

in a different conceptual process than SQL query formulation.

MDX contains a number of complicating elements

not shown in this simple overview.

MDX statements can involve conditions in both the SELECT and WHERE clauses,

sub SELECT statements in a WHERE clause and multiple stages of a computation.

SQL SELECT statements have complexities also, but

trained in SQL must re-orientate their thought process for MDX.

Thus, most users of MDX is indirect through graphical tools,

such as Pivot 4J and PivotTables in Microsoft Excel.

In the remainder of this module, you will learn about two graphical tools for

data cubes.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2018 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play