diplomado sql server 2012 - microsoft partner … · diplomado sql server 2012 ... tuning required...

MODELO SEMANTICO

DIPLOMADO

SQL SERVER 2012

• MVP SQL Server

• Regional Mentor PASS

LATAM

Jesús Gil

JESÚS GIL

• Jesús es el actual Director de Operaciones para SolidQ México y un MVP de SQL Server

desde el año 2010

• Tiene experiencia de más de 15 años en el manejo de bases de datos y más de 10 años

con Microsoft SQL Server (desde la versión 7)

• Ha trabajado en empresas de diferentes sectores como Gerente de Sistemas y tomador de

decisiones (Comercial Mexicana, Casas Geo, Banco Azteca, Universidad ETAC)

• Es colaborador de las revistas

• SolidQ Journal (USA) y DotNetMania (España)

• Actualmente está impartiendo la capacitación oficial de Microsoft SQL Server 2012 en

Latinoamérica: Colombia, México y Perú son los países en donde ha estado llevando

acabo estos entrenamientos

• Ha liderado las 2 ediciones de 24 Horas PASS LATAM (2010, 2011), las cuales han tenido

más de 14,000 asistentes y poco más de 60 charlas en línea

• Es miembro fundador de la comunidad SQLPASS - México (Primer presidente de la

comunidad)

• Actualmente funge como Presidente de los mentores regionales para LATAM siendo

responsable de todas las comunidades de PASS en Latinoamérica

Diplomado de SQL Server 2012

•Creación en el año 2002 por 4 reconocidos MVPs

•Ayudamos a obtener lo mejor de su SQL Server

• Expertos de clase y reconocimiento mundial

• Problemas de magnitud y complejidad inusual

•Mentoring, formación y consultoría en

• Proyectos tradicionalmente de Bases de Datos (MS SQL Server)

• Proyectos de Inteligencia de Negocio

• Proyectos de Arquitecturas de Sistemas


http://www.solidq.com

Modelo Semántico

Analysis Services – SQL Server 2008 R2


Broad adoption

“Customers in the Magic Quadrant survey report that their Microsoft

average deployment sizes are now larger than any other vendor in

the survey in terms of users.”

“Use of OLAP functionality by Microsoft customers is more than

double that for the rest of the survey respondents.”

Source: Gartner Magic Quadrant for BI Platforms, 2011

Highest rated infrastructure and development tools

“Microsoft customers rate its BI platform infrastructure and development tools among the highest

compared to other vendors, and a higher percentage of customers use them extensively.”


Large ecosystem

"Wide availability of skills is among the top reasons customers select Microsoft over competing vendors.”


http://www.gartner.com/technology/media-products/reprints/microsoft/vol2/article15/article15.html



Modelo Semántico

Analysis Services – SQL Server 2012


Build on the strengths

and success of Analysis

Services and expand its

reach to a much

broader user base

Embrace the relational

data model – well

understood by

developers and IT Pros

Bring together the

relational and

multidimensional

models under a single

unified BI platform –

best of both worlds!

Provide flexibility in the

platform to suit the

diverse needs of BI

applications

Modelo Semántico

BI Modelo Semántico


One Model for all End User Experiences

Team BI

PowerPivot for SharePoint

Personal BI

PowerPivot for Excel

Organizational BI

Analysis Services

Modelo Semántico

BI Modelo Semántico


7

DAX Query – Data Analysis

Expressions MDX Query

Excel 2010

DAX

In-Memory N/A

SharePoint Library / Analysis Services

PowerPivot

Tabular

SQL Server Data Tools

DAX

In-Memory DirectQuery

Analysis Services Tabular

Tabular

SQL Server Data Tools

MDX

MOLAP ROLAP

Analysis Services Multidimensional

Multidimensional

Third-Party

Applications

Reporting

Services Excel PowerPivot

SharePoint

Insights

Power

View

LOB

Applications Files

OData

Feeds Cloud

Services

Relational

Databases

Deployed

BI Semantic Model

Modelo Semántico

¿Cómo debo construir mi modelo?


Depends on the application needs for each layer

Two Visual Studio (BIDS) project types in SQL Server 2012

Familiar model, easier to build, faster time to solution

Advanced concepts (parent-child, many-to-many) not available natively in the model… need calculations to simulate these

Easy to wrap a model over a raw database or warehouse for reporting & analytics

Sophisticated model, higher learning curve

Advanced concepts baked into the model and optimized (parent-child, many-to-many, attribute relationships, key vs. name, etc.)

Ideally suited for OLAP type apps (e.g. planning, budgeting, forecasting) that need the power of the multidimensional model

Modelo Semántico

Data Model


Based on Excel formulas and relational concepts – easy to get started

Complex solutions require steeper learning curve – row/filter context, Calculate, etc.

Calculated columns enable new scenarios, however no named sets or calc members

Based on understanding of multidimensional concepts – higher initial learning curve

Complex solutions require steeper learning curve – CurrentMember, overwrite semantics, etc.

Ideally suited for apps that need the power of multidimensional calculations – scopes, assignments, calc members

Modelo Semántico

Business Logic


In-memory column store… typical 10x compression

Brute force memory scans… high performance without tuning…

Basic paging … data volume mostly fits in physical memory

Disk based store… typical 3x compression

Disk scans with in-memory subcube caching… aggregation, tuning required

Extensive I/O support… scales to multiple TBs

Passes through DAX queries & calculations… fully exploits backend database capabilities

No support for MDX queries… no support for data sources other than SQL Server (in Denali)

Passes through fact table requests… not recommended for large dimension tables

Supports most relational data sources…

Modelo Semántico

Data Access and Storage



TRANSICION DEL MODELO SEMANTICO

ATRAVES DEL ESPECTRO DE BI

Team BI

Our Context

BI Solution created by

power user. Context is

for a small team & it’s

managed on a server.

Personal BI Corporate BI

My Context

BI solution created by

user. Context is only

for user & exists as

document.

The Org’s Context

BI Solution created by IT,

Established corporate

context & is reusable,

scalable and backed up.

PowerPivot for Excel PowerPivot for SharePoint Analysis Services

Empowered Aligned

Modelo Semántico


Feels like

Excel

PowerPivot for Excel BI Development Studio

Information Workers BI Developers

Personal BI Team BI Organizational BI

One file,

Save to

SharePoint

Optimized for

Excel power

user

Rapid response

to business

problems

Solutions live

for weeks or

months

Feels like Visual Studio

Optimized

for BI Pros

Teams building BI

solutions

Source Control,

TFS

Large data

volumes

It’s a project

(business case,

budget, dates)

Deployment

scripts, versions

Modelo Semántico

Construyendo un Modelo Semántico


Modelo Semántico

Que pasa con las aplicaciones existentes de Analysis Services?


New

applications New technology options

Existing

applications Based on Unified

Dimensional Model

Existing

applications Every UDM becomes a BI

Semantic Model

Existing

applications Based on Unified

Dimensional Model

Modelo Semántico

BI Modelo Semántico: Arquitectura


Third-party

applications

Reporting

Services Excel PowerPivot

Databases LOB Applications Files OData Feeds Cloud Services

SharePoint

Insights

Modelo Semántico - Escenarios

Power View over a Sales model


SQL Server Dynamics CRM

End

User

Model

Developer

Power View


Excel over a Sales model


SQL Server Dynamics CRM

End

User

Model

Developer


Excel over a Finance model


Oracle SAP

End

User

Model

Developer


Power View over a Finance model


Oracle SAP

End

User

Model

Developer

Power View

Modelo Semántico

Explotando un Modelo Semántico


Modelo Semántico


• Rich data modeling

capabilities

• Sophisticated business

logic using MDX and DAX

• Fine-grained security –

row/cell level

• Enterprise capabilities –

multi-language and

perspectives

• Multi-dimensional and

tabular modeling

experiences

• MDX and DAX for

business logic and

queries

• Cached and passthrough

storage modes

• Choice of end-user BI

tools

• VertiPaq for high

performance, MOLAP for

mission critical scale

• DirectQuery and ROLAP

for real-time access to

data sources

• State-of-the-art

compression algorithms

• Scales to largest

enterprise servers

Flexibility Richness Scalability

Recursos

• Blog Jesús Gil

• http://blogs.solidq.com/JGil/Home.aspx

• Solid Quality Mentors blogs

• http://blogs.solidq.com/Pages/Home.aspx

• Solid Quality Journal

• http://www.solidq.com/sqj/Pages/Home.aspx

• SQL Server 2012

• Crear una conexión de modelo semántico de BI a una

base de datos de modelo tabular

• Conexión de modelo semántico de BI PowerPivot

(.bism)

• Twitter: @jesus_gilv


http://blogs.solidq.com/JGil/Home.aspx

http://blogs.solidq.com/Pages/Home.aspx

http://www.solidq.com/sqj/Pages/Home.aspx

http://msdn.microsoft.com/es-es/library/hh230972.aspx



http://msdn.microsoft.com/es-es/library/gg471575.aspx






¿¿PREGUNTAS??

@jesus_gilv

Solid Quality Mentors blogs

http://blogs.solidq.com/Pages/Home.aspx

Solid Quality Journal

http://www.solidq.com/sqj/Pages/Home.aspx

diplomado sql server 2012 - microsoft partner … · diplomado sql server 2012 ... tuning required...

Documents