Transcript Powerpoints

From MDS to SSRS - a short walkthrough
Change your master data on the fly…
Say thank you to our Sponsors :
Stick around for RAFFLE and the AFTER EVENT!
 All our volunteers and organisers do not get paid for
organizing this event – If you see them, please:





Give them a hug
Shake their hand
Say thank you
Spread the word
Get involved yourself
 Don’t forget to thank the sponsors for their support
 Thank the speakers for donating their time, energy and
expenses
Speakers
Tillmann Eitelberg
Gabi Muenster
• CEO of oh22information
services GmbH
• PASS Regional Mentor
Germany
• Vice-president PASS
Germany
• Chapter Leader
Cologne/Bonn, Germany
• Microsoft MVP
• Consultant for oh22data
AG
• Pass member since
2008
• Speaker at PASS
regional meetings in
Germany, SQLSaturday,
PASS Summit, SqlBits
Agenda




MDS: Basic overview
Demo: Create a simple Product hierarchy in MDS
Demo: Integrate hierarchical data via Lookup() in SSRS
Demo: MDS versions as source for user-defined hierarchies
in SSAS
 Demo: Changes on the fly and transfer to user frontend
 Preview: New MDS features in SQL 2016
 Q&A
MDS: Basic overview (I)
 Problem
 Heterogenic system environment with several line of business
application [LOB] who produce and consume data from identical
business entities
 Core entities
 Customer
 Product
 Chart of accounts etc.
 Operational and Analytical Problem
We got a problem.....
Where is the truth
MDS: Basic overview (II)
MDS: Basic overview (III)
MDS: Basic overview (IV)
MDS: Basic overview (V)
 Operational MDM
 LOB‘s write and read from MDM to achieve a single point of truth
 MDM enforcing the single point of truth [SPOT] through rules,
security, versioning
 LOB systems provide and consume the SPOT of an entity and the
related attributes
 Open interfaces for data exchange
 All by an LOB independent UI
MDS: Basic overview (VI)
 Analytical MDM
 Instead of loading the data from different LOBs to the DWH landing
area and standardize it in the stage the MDM solution is the
gatekeeper
 The gatekeeper function of MDM will be achieved through rules,
standardized hierarchies, versioning, approvals workflows,
dimension modeling (SCD etc.)
 All by an LOB independent UI
Demo: Create a simple Product hierarchy in
MDS (I)
Demo
Demo: Create a simple Product hierarchy in
MDS (II)
 MDS System Administration:
 Create model
 Excel Add-in:
 Create entities from data
 Configure Domain-Based Attributes
 MDS System Administration:
 Create derived hierarchy
Demo: Create a simple Product hierarchy in
MDS (III)
 MDS Version Management:
 Create flags
 Administer versions
 MDS Integration Management:
 Create SQL views
Demo: Integrate hierarchical data via Lookup() in
SSRS (I)
Demo
Demo: Integrate hierarchical data via Lookup() in
SSRS (II)
 Create dataset with Hierarchy information
 Consider datatypes
 Relate master data to facts using function Lookup(Source,
Destination, Result, Data set) for label and grouping
Demo: MDS versions as source for userdefined hierarchies in SSAS (I)
Demo
Demo: MDS versions as source for userdefined hierarchies in SSAS (II)
 Prepare schema and dimension view in MDS database
=> Recommendation: use independent solution (ETL
transfer, dedicated database, …)
 Create data source in SSAS project and add view to data
source view
 Build dimension with user-defined hierarchies
 Integrate dimension into existing cube
Demo: Changes on the fly and transfer to
user frontend (I)
Demo
What's new with SQL Server 2016
 Massive performance and scale improvements
 Scale entities to 100 million members (and beyond)
 15x performance increase of excel
 Faster entity based staging
 Many improvements in the security area
 Multiple administrator roles
 Granular security permissions in MDS (read/modify/create/delete)
 Features to help solve merge conflict when multiple people
are trying to modify the same entity
 Improved MDS model deployment
 Entity sharing across models
What's new with SQL Server 2016
 Improvements for the today's examples
 Support for custom indexes
 Subscription view for historical data
 Simplified hierarchies – just derieved, explicit is depricated
Questions?