t-sql fenster funktionen grundlagen pass session

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

T-SQL Fenster Funktionen

Ach ja, das mit RANK, oder?

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

About me…

• Sascha Lorenz• Lead Consultant, Coach & Trainer• PSG Projekt Service GmbH, Hamburg• Our Customers are German Federal Agencies, Shipping

Companies and Banks (Fonds Administration & NPL)• Engaged in German PASS Chapter (Hamburg)

Follow me on Twitter, Facebook and http://saschalorenz.blogspot.com

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Wie viele von Euch?

• StreamInsight• Tabellarische Modelle• Service Broker• PIVOT & UNPIVOT• MERGE• GROUP BY – WITH ROLLUP / ROLLUP()– WITH CUBE / CUBE()– GROUPING SETS() & GROUPING()

• Fenster Funktionen

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER( )

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER( )

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER( ) -> Fenster…

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Was ist ein Fenster?

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Fenster

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Unser Abfrageergebnis Fenster

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Was ist ein Fenster?

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Zeile

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Fenster

Unser Abfrageergebnis

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Was ist ein Fenster?

• Was ist im Fenster?• Und wie ist es „sortiert“?

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Warum?

Wir erhalten u.a. Summen in unterschiedlichen Granularitäten ohne auf Details verzichten zu müssen oder ohne uns diese teuer wieder „ran-zu-joinen“.

Es gibt noch unendliche viele weitere Gründe, um sich damit zu beschäftigen !

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER ( PARTITION BY … )

Ist nicht…

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER ( PARTITION BY … )

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER ( ORDER BY … )

Ist nicht…

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

OVER ( ORDER BY … )

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

Rahmen

• ROWS BETWEEN – UNBOUNDED PRECEDING– <n> PRECEDING– <n> FOLLOWING– CURRENT ROW

• AND– UNBOUNDED FOLLOWING– <n> PRECEDING– <n> FOLLOWING– CURRENT ROW

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

ROWS

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

So long…

Any Questions?

Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services

BIMethodology

BI LifecycleSkill Improvment Data Profiling

Architecture

Dimensional Modeling

BI Strategy

Patterns

Consulting

Coaching

Coaching

MDM

The End

Thank you!Follow me on Facebook and http://saschalorenz.blogspot.com

Send me a mail -> lorenz@psg.de