amo und adomd.net mit dem sql server 2012 (tabular & olap)
TRANSCRIPT
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
AMO und ADOMD.NET mit dem SQL Server 2012
bySascha Lorenz
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Über mich…
• Sascha Lorenz • Lead Consultant, Coach & Trainer• PSG Projekt Service GmbH – The SQL Server Company,
Hamburg• Unsere Kunden sind Bundesbehörden, Reedereien und
Dienstleister (Banken, Projekt Entwicklung usw.)• Engagiert in der Deutschen PASS Community für 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
Ziele für diese Session
• Hinweis auf Technologie:– Analysis Management Objects – ADOMD.NET
• Neugierig machen…• Es wurde Zeit für ein Update! • Hinweise und Ideen für den Einsatz
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Management Objects
„Bei den Analysis Management Objects (AMO) handelt es sich um die vollständige Bibliothek von Objekten mit programmgesteuertem Zugriff, die einer Anwendung ermöglicht, eine gerade ausgeführte Instanz von Microsoft SQL Server Analysis Services zu verwalten.“
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Ja, wir sprechen übers programmieren!
Kein Scherz!• Es gibt keinerlei Einschränkungen, da der SQL
Server objektiv eh nur aus Diensten besteht.• Alle Werkzeuge des SQL Servers erzeugen bzw.
bearbeiten nur eine Beschreibung, welche auf die Dienste übertragen wird.
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Nun die große Frage…
Wozu?
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Meine Lernkurve
Begeisterung für die Technologie
Repository, Repository…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Management Objects
„Bei den Analysis Management Objects (AMO) handelt es sich um die vollständige Bibliothek von Objekten mit programmgesteuertem Zugriff, die einer Anwendung ermöglicht, eine gerade ausgeführte Instanz von Microsoft SQL Server Analysis Services zu verwalten.“
Parametrisierung…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
MS BI Middleware
Repository(DB)
SSIS SSASSSRS
Bus
DWH
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Meine Lernkurve
Begeisterung für die Technologie
Wirksamkeit von Management Konzepten
Repository, Repository…
Technologie ohne Konzept ist wirkungslos!
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Universalwerkzeuge Analysis Services
Zwei Bibliotheken für zwei Zielplattformen
ADOMD.NET
OLAP
TM
AMO
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Universal?
OLAP Tabular
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Kurzer Blick auf ein OLAP Projekt
• SSAS dreht sich nicht um Cubes!
• Da ist immer eine Datenbank um den Cube herum!
• Wir arbeiten also mit Datenbanken…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services ObjectsÜberblick
SSAS DBs
Data Sources
Data Source Views
Dimensions
Attributes
Hierarchies Levels
Cubes
MDXScript
Measure Groups
Measures
Partitions
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services…using Microsoft.AnalysisServices;…using (Server svr = new Server()){
svr.Connect(„myServer");
Database db = svr.Databases.Add(pDatabaseName); db.DataSourceImpersonationInfo = new
ImpersonationInfo(ImpersonationMode.ImpersonateServiceAccount);db.Update();
DataSource ds = db.DataSources.Add(pDSName);ds.ConnectionString = pDSConnString;ds.Update();
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services…DataSourceView dsv = db.DataSourceViews.Add(pDSVName);dsv.DataSourceID = pDSName;dsv.Schema = new DataSet();
OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString);connection.Open();
rows = tbl.Select("keypath = 'Database/DSV/Table'");foreach (DataRow row in rows)
AddTable(dsv, connection, row["keyvalue"].ToString());
?
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services…rows = tbl.Select("keypath = 'Database/DSV/Relation'");foreach (DataRow row in rows){
string[] pRelation = row["keyvalue"].ToString().Split(';');
AddRelation(dsv, pRelation[0],pRelation[1],pRelation[2],pRelation[3]);}
dsv.Update();
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services…dim = db.Dimensions.Add(pDimensionName);dim.Source = new DataSourceViewBinding(pDSVName);…
attr = dim.Attributes.Add(pAttributeName);…
attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], pKeyColumn[1], pKeyColumn[2]));
attr.NameColumn = CreateDataItem(db.DataSourceViews[0], pNameColumn[0], pNameColumn[1]);
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
DemoAMO für zwei…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Services ObjectsÜberblick für TM
SSAS TM DB
Data Source
Data Source View(Sandbox)
Dimensionen
Attributes
Hierarchien Levels
Cubes(Model)
Measure Groups
Measures
Partitions
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
DemoAMO2Tabular
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Wie können wir AMO nutzen?
Auslesen
• Inventarisieren• Dokumentiere
n
Erzeugen
• „From Scratch“• Duplizieren
Ändern
• Refactoring• Deployment
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Dokumentieren
• Dokumentation ist ein wesentlicher Teil eines Projekt Risikomanagements
• Dokumentation ist Kommunikation• Nur, verdammte Axt, was ist eine
Dokumentation? • These von mir:
Über 80% aller Dokumentationen sind in Wirklichkeit nichts weiter als ein Inventar!
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Dokumentieren
• Eine gute Dokumentation sollte dadurch Ressourcen sparen, indem sie redundante Fragestellungen vermeidet.
• Also nicht nur WAS sondern auch das WARUM dokumentieren.
• Des Weiteren ist sie klar vom Betriebshandbuch abzugrenzen.
Gleich komm
ich drauf…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Wie können wir AMO nutzen?
Auslesen
• Inventarisieren• Dokumentiere
n
Erzeugen
• „From Scratch“• Duplizieren
Ändern
• Refactoring• Deployment
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Deployment
• Auslieferung von Ergebnissen an Zielsysteme
Entwicklungssystem(e)
Test System(e)
Integrationstest System(e)
Produktive System(e)
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Deployment
• Zerlegen von Ergebnissen in Artefakte– Kleinstmögliche/sinnvolle Einheit für eine
Ergebnissgattung… • Artefakte haben Abhängigkeiten untereinander• Beispiele für Artefakte:– Spalte in einem Vorsystem– Spalte in einer Dimension– Attribute in einer Cube Dimension– Ein RDL– Ein DTSX (Diskussion bitte…)
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Quellcode Systeme?
• Nice to have und sind kein Deployment System!
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Dokumentation durch Deployment!
Nun die Idee… welche tatsächlich auch so produktiv in Projekten eingesetzt wird.
• Es wird nur das Ergebnis ausgeliefert, welches sauber im Deployment System ist.
• Artefakte müssen „dokumentiert“ werden.• Damit ist alles was jemals ausgeliefert wurde
auch dokumentiert!
Und deswegen brauchte man in
diesem Projekt u.a.
Analysis Management Objects !
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Kurz zur Implementierung…
• Entwicklungsumgebungen:– Visual Studio C# / VB– SSIS Script Task– PowerShell
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Universalwerkzeuge Analysis Services
Zwei Bibliotheken für zwei Zielplattformen
ADOMD.NET
OLAP
TM
AMO
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
ADOMD.NET
„ADOMD.NET ist ein Microsoft.NET Framework-Datenanbieter, der auf die Kommunikation mit Microsoft SQL Server Analysis Services ausgelegt ist.“
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
BeispielAdomdConnection mdcon = new AdomdConnection(„Connectionstring“);
…
mdcon.Open();
AdomdCommand cmd = mdcon.CreateCommand(); cmd.CommandText = „MDXQuery“;
CellSet cs = cmd.ExecuteCellSet();
Wir machen hoffentlich was Nützliches mit dem CellSet…
mdcon.Close();
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
CellSet
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
CellSet
int x = cs.Axes[0].Positions.Count; // Anzahl Spaltenint y = cs.Axes[1].Positions.Count; // Anzahl Zeilen
cs.Axes[0].Positions[xi].Members[0].Caption;cs.Axes[0].Positions[xi].Members[0].UniqueName;
Value = cs[xi,yi].Value.ToString();
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
Analysis Service 2012
• Nun kann Analysis Services auch DAX• Früher war meine Welt ein Würfel• Nun ist er eine Fläche…
Data WarehouseETL OLAPData Quality Reporting ServicesAnalysis Services
BIMethodology
BI LifecycleSkill Improvment Data Profiling
Architecture
Dimensional Modeling
BI Strategy
Patterns
Consulting
Coaching
Coaching
MDM
DemoADOMD.NET für TM