By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData Collective
  • Analytics
    AnalyticsShow More
    predictive analytics in dropshipping
    Predictive Analytics Helps New Dropshipping Businesses Thrive
    12 Min Read
    data-driven approach in healthcare
    The Importance of Data-Driven Approaches to Improving Healthcare in Rural Areas
    6 Min Read
    analytics for tax compliance
    Analytics Changes the Calculus of Business Tax Compliance
    8 Min Read
    big data analytics in gaming
    The Role of Big Data Analytics in Gaming
    10 Min Read
    analyst,women,looking,at,kpi,data,on,computer,screen
    Promising Benefits of Predictive Analytics in Asset Management
    11 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-23 SmartData Collective. All Rights Reserved.
Reading: Delivering Data in Excel: The DTP Framework
Share
Notification Show More
Latest News
ai software development
Key Strategies to Develop AI Software Cost-Effectively
Artificial Intelligence
ai in omnichannel marketing
AI is Driving Huge Changes in Omnichannel Marketing
Artificial Intelligence
ai for small business tax planning
Maximize Tax Deductions as a Business Owner with AI
Artificial Intelligence
ai in marketing with 3D rendering
Marketers Use AI to Take Advantage of 3D Rendering
Artificial Intelligence
How Big Data Is Transforming the Maritime Industry
How Big Data Is Transforming the Maritime Industry
Big Data
Aa
SmartData Collective
Aa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Business Intelligence > Delivering Data in Excel: The DTP Framework
Business Intelligence

Delivering Data in Excel: The DTP Framework

JuiceAnalytics
Last updated: 2009/01/27 at 11:13 PM
JuiceAnalytics
6 Min Read
SHARE

Here at Juice we build fewer Excel dashboards than we used to. Excel itself is a decidedly imperfect vessel for any serious development–it’s simply too easy to veer off of the disciplined track onto the underbrush.

Contents
DataDataTransformPresentUsing a PivotTable as your interface

Even so, Excel remains a playground where we can do surprising things. For instance, check out our Excel lightbox and an Excel tagcloud. We could appropriate everything that you find on the webbiest of Web 2.0 websites and build our Uruk-hai equivalents.

The key to staying on the rails when building Excel tools–either dynamic dashboards or simply to explore data–is discipline. At Juice, we use a methodology that we call “DTP” (Data Tansform Present). The foundation of DTP is the rigorous separation of data from presentation. This is similar to a well-known approach when building computer user interfaces called Model-View-Controller. I’m going to cover some of the key principles and we’ll follow up with an example later on the blog.

Data

Data is the raw material of any visualization or report. It needs to be easy to add data or change data without having to change anything else about your dashboard.

More Read

ai software development

Key Strategies to Develop AI Software Cost-Effectively

AI is Driving Huge Changes in Omnichannel Marketing
Maximize Tax Deductions as a Business Owner with AI
Marketers Use AI to Take Advantage of 3D Rendering
Top Five AI-Driven Digital Marketing Tools in 2023

We store raw data with dimensions preceding metrics i…

Here at Juice we build fewer Excel dashboards than we used to. Excel itself is a decidedly imperfect vessel for any serious development–it’s simply too easy to veer off of the disciplined track onto the underbrush.

Even so, Excel remains a playground where we can do surprising things. For instance, check out our Excel lightbox and an Excel tagcloud. We could appropriate everything that you find on the webbiest of Web 2.0 websites and build our Uruk-hai equivalents.

The key to staying on the rails when building Excel tools–either dynamic dashboards or simply to explore data–is discipline. At Juice, we use a methodology that we call “DTP” (Data Tansform Present). The foundation of DTP is the rigorous separation of data from presentation. This is similar to a well-known approach when building computer user interfaces called Model-View-Controller.
I’m going to cover some of the key principles and we’ll follow up with an example later on the blog.

Data

Data is the raw material of any visualization or report. It needs to be easy to add data or change data without having to change anything else about your dashboard.

We store raw data with dimensions preceding metrics in blocks in separate worksheets. If you want to sound pretentious, you can call this “first PivotTable normal form”. Key points:

  • Have one worksheet for each data source.
  • Call these sheets “Data”, or “{Title} Data”.
  • Place them at the end of your workbook.
  • Data is snug to the top left of the spreadsheet. This allows us to use dynamic ranges. Dynamic ranges let you add data and have it automatically incorporated in all PivotTables.
  • Ensure that column names are in the first row.
  • Place your dimensions before metrics.
    Dimensions before metrics

Transform

We use PivotTables to transform the data into the structure we need.

  • Call these sheets “Transform” or “XXXXXXX Transform”.
  • Create one sheet for each issue that you are exploring. This doesn’t mean that you will only create one PivotTable. You may have multiple PivotTables to support different views or perspectives on an issue.
  • Turn on “show items with no data” for row and column dimensions. Show all items
  • We are seeking predictability, we want to the PivotTable to always be the same size regardless of what the PageField filters are.
  • Place all the dimensions that aren’t used as rows or columns in the PivotTable as page fields. Every dimension should have a home.
    All dimensions must have a home

    • Set all PivotTables to not store data and refresh on open.
      PivotTable settings

Present

The Presentation page copies data from the Transform page(s) and formats it for display. It also allows users to control what data is being displayed.

  • Build a user interface to interact with your data. There are many ways to let people interact with your data, but one of the easiest is to use a PivotTable as your interface. This is described below.
  • We use an in-house style guide for graphs that you can see in our Chart Chooser.
  • If the Presentation page is likely to be printed, preset the print range.
  • When copying data from the transformation page to the presentation page, blank values will come out as zeros. We use a simple formula, =if('Transform!A2'<>"",'Transform!A2', ""), to ensure that blanks remain blanks.

Using a PivotTable as your interface

A simple way to let people manipulate your data is place a PivotTable containing only PageFields but no data on the presentation sheet. A Visual Basic macro triggered to run whenever the PivotTable changes then pushes out any changes to the master PivotTable to all the PivotTables on your Transform sheet.

Here is the code to make this happen.

This drives our PivotTables in concert and ensures they stay in sync.


That’s a basic overview of our DTP technique. You can try a simplified version of DTP here.

DTP Example.xls

We’ll be back soon to talk through this example.

 Link to original post

JuiceAnalytics January 27, 2009
Share this Article
Facebook Twitter Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

ai software development
Key Strategies to Develop AI Software Cost-Effectively
Artificial Intelligence
ai in omnichannel marketing
AI is Driving Huge Changes in Omnichannel Marketing
Artificial Intelligence
ai for small business tax planning
Maximize Tax Deductions as a Business Owner with AI
Artificial Intelligence
ai in marketing with 3D rendering
Marketers Use AI to Take Advantage of 3D Rendering
Artificial Intelligence

Stay Connected

1.2k Followers Like
33.7k Followers Follow
222 Followers Pin

You Might also Like

ai software development
Artificial Intelligence

Key Strategies to Develop AI Software Cost-Effectively

10 Min Read
ai in omnichannel marketing
Artificial Intelligence

AI is Driving Huge Changes in Omnichannel Marketing

12 Min Read
ai for small business tax planning
Artificial Intelligence

Maximize Tax Deductions as a Business Owner with AI

9 Min Read
ai in marketing with 3D rendering
Artificial Intelligence

Marketers Use AI to Take Advantage of 3D Rendering

7 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

data-driven web design
5 Great Tips for Using Data Analytics for Website UX
Big Data
ai in ecommerce
Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence

Quick Link

  • About
  • Contact
  • Privacy
Follow US

© 2008-23 SmartData Collective. All Rights Reserved.

Removed from reading list

Undo
Go to mobile version
Welcome Back!

Sign in to your account

Lost your password?