<< VIEW FULL RESOURCE LIBRARY

Four Ways to Model Data in Power BI

March 12, 2020

Microsoft Power BI

Source and Prepare Your Data for Power BI

In this on-demand webinar, learn the different ways Power BI lets you source and prep data for analysis. We examine four different scenarios you should consider that span desktop to enterprise use. We review how they serve different analytics and reporting needs and discuss the pros and cons of each approach, including their impact on scalability, security and performance.

READ THE ANSWERS TO THE QUESTIONS ASKED DURING THE WEBINAR

What method you choose depends on the type of analytics you want to perform, the ease-of-use factor for your end users, and your organization’s need for data security, reporting performance and data integrity. Learn which of these approaches is best suited to your organization’s requirements:

  • Directly querying operational systems
  • Querying the data warehouse
  • Dataflows
  • Datasets

Watch this recording to learn the best way to marry your data to Power BI.

▼ TECHNOLOGIES COVERED

Microsoft Power BI

▼ PRESENTER

Michael Weinhauer
Director of Training and Content
Senturus, Inc.

In addition to heading up the training at Senturus, Michael’s team is responsible for the development of the Senturus Analytics Connector, which lets Tableau and Power BI use Cognos as a data source.  He has been designing, delivering and selling analytics solutions for over 20 years. Before Senturus, Michael held positions at Oracle, IBM and SAP, acquiring a wealth of hands-on, practical BI and Big Data experience. 

▼ PRESENTATION OUTLINE

  • The rise of self-service
  • Microsoft BI offerings – through the years
  • What is Power BI?
    • A self-service analytics solution that lets you prepare and present data for your organization
    • Power BI serves five essential functions:
    • Power BI is actually a set of integrated tools:
      • Power BI Desktop
      • Power BI Service
      • Power BI Report Server
      • Power BI Mobile
      • Power BI Embedded
      • Power BI Data Gateway
      • …and Excel (???)
  • Power BI core concepts and workflow
    • Dataflows pull data from sources, performs transforms and loads into Azure (ADLG2)
    • Datasets are published, analysis-ready models that are reusable and certifiable
    • Dashboards present visuals, pinned from reports
    • On-Premise Data Gateways let the service connect back to data sources
    • Datasets consist of two object types:
    • Queries – defines how to extract and transform data sources, accessed via connectors
    • Model – defines tables, relationships, columns and measures
    • Reports present data from datasets on one or more pages of visuals
  • Tradeoffs and considerations
    • Real-time vs. latent
    • Store once vs. multiple copies
    • Analysis ready vs. transactional
    • Cost
    • Performance
    • Scalability
    • On-prem vs. cloud
    • Governance vs. agility
    • Security
  • Query methods – definitions
    • DirectQuery – live query to data modeled in Power BI
    • Live – live query to pre-modeled data (datasets or SSAS)
    • Import – data is pulled from source into Power BI data model
    • Composite/dual – aggregate data is pulled into an import; query engine determines if Import or DirectQuery is used
  • Option #1 – direct connection to transactional data
    • Pros: real-time, no data redundancy, low cost, agile, powerful
    • Cons: performance, system impact, effort, low reuse, point in time (lacks history, trending, change data capture)
    • Ideal for: small groups, real-time use cases, prototypes
    • Methods: DirectQuery, import or composite/dual
    • BI maturity: beginner
  • Option #2 – connect to EDW
    • Pros: easy, designed for analysis, governed, performant, scales, history, trends, change capture, reusable, aligned business metrics
    • Cons: latency, recency, cost, unagile, completeness
    • Ideal for: medium/large enterprises, governed data needs
    • Methods: DirectQuery, import or composite/dual
    • BI maturity: intermediate
  • Option #2a – Connect to SSAS
    • Pros: easy, designed for analysis, governed, performant, scales, history, trends, change capture, reusable, aligned business metrics
    • Cons: latency, recency, cost, unagile, completeness
    • Ideal for: medium/large enterprises, governed data needs
    • Methods: live
    • BI maturity: intermediate
  • Option #3 – shared datasets
    • Pros: governed, certifiable/promotable, performant, reusable, reduced impact on sources, aligned business metrics
    • Cons: governance need/consequences of lack of governance, agility
    • Ideal for: sharing data across similar reporting use cases
    • Methods: live
    • BI maturity: intermediate
  • Option #4 – dataflows
    • Pros: transformation reuse, governance, performance, agility
    • Cons: recency, data duplication
    • Ideal for: orgs wanting to move from desktop to cloud/enterprise, governed reporting use cases
    • Method: import
    • BI maturity: pro
  • Summary comparison matrix
  • Use cases by approach
  • Which is best for you?
    • Some/all of the above
    • Depends on size, use case(s)
    • Balance of factors
    • Iterative process – morphs over time