BigBear.ai
  • Home
  • Industries
    • Academia
    • Government
    • Healthcare
    • Manufacturing
  • Solutions
    • Cyber
    • Data Analytics
    • Enterprise Planning and Logistics
    • Intelligent Automation
    • Modeling Solutions
    • Professional Services
  • Company
    • About
    • Investor Relations
    • Partners
    • Team
  • Careers
    • Benefits
    • Culture
    • Explore Jobs
    • Military and Veterans
    • Applicant Login
    • Employee Login
  • Resources
    • Blog
    • Events
    • Newsroom
    • Resource Library
    • Online Store
  • Contact
Search

Home Data Warehousing Drilling Down / Drilling Through (Across)

Blog

Drilling Down / Drilling Through (Across)

Jim McHugh
November 26, 2019
  • Share
  • Share

You often hear “drilling down” and “drilling across” from business intelligence professionals, but what does it mean and how do you, as a data modeler, design for this requirement? This blog post will provide you with insights on how to do this.

To drill down means to decompose fact data via hierarchical dimensional attributes. Time and geography (location) are good examples of hierarchical dimensions. In a complete time dimension, you will have the day, month, quarter and year hierarchy. In a geography dimension you may have a city, district, state, and region hierarchy. Consider your shown a dashboard with year to date (YTD) sales. You may want to decompose that data to look into the sales by quarter or by month to see if there is a trend in the data. Using that same fact data you may be presented with the overall sales in your company but want to research the sales by a smaller geographic area like state or district to see if there are any anomalies or trends happening that you may want to eliminate or take advantage of companywide.

fact-sale-model-500x387_2
Figure 1 – FACT_SALE
Sum Yearly
SELECT DATE_YR_NBR
, SALE_PRDCT_ID
, PRDCT_DESC
, SUM(SALE_QTY)
, SUM(SALE_TOT)
FROM FACT_SALE
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
WHERE DATE_YR_NBR = 2019
GROUP BY DATE_YR_NBR, SALE_PRDCT_ID, PRDCT_DESC
ORDER BY SUM(SALE_TOT) DESC;

 

Sum Quarterly
SELECT DATE_QTR_NBR
, SALE_PRDCT_ID
, PRDCT_DESC
, SUM(SALE_QTY)
, SUM(SALE_TOT)
FROM FACT_SALE
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
WHERE DATE_YR_NBR = 2019
GROUP BY DATE_QTR_NBR, SALE_PRDCT_ID, PRDCT_DESC
ORDER BY SUM(SALE_TOT) DESC;

 

Sum Monthly
SELECT DATE_MONTH_NBR
, SALE_PRDCT_ID
, PRDCT_DESC
, SUM(SALE_QTY)
, SUM(SALE_TOT)
FROM FACT_SALE
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
WHERE DATE_YR_NBR = 2019
GROUP BY DATE_MONTH_NBR, SALE_PRDCT_ID, PRDCT_DESC
ORDER BY SUM(SALE_TOT) DESC;

 

Sum Daily
SELECT DATE_KEY
, SALE_PRDCT_ID
, PRDCT_DESC
, SUM(SALE_QTY)
, SUM(SALE_TOT)
FROM FACT_SALE
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
WHERE DATE_KEY = 2019
GROUP BY DATE_MONTH_NBR, SALE_PRDCT_ID, PRDCT_DESC
ORDER BY SUM(SALE_TOT) DESC;

 

To drill through (or across) is a way to provide additional information about a data element contained within a report. These reports are separate and distinct reports that are opened using data provided by the current report and contain detailed information about an item in the calling report. Finally, because these reports can stand-alone, they are rendered only when the user clicks the drill through link contained in the related report. An example of this may be when I have drilled down on my sales data to a specific day. Here I may be able to see all of the individual sales invoices for that day. If I wanted to see an individual sales invoice, I could then call the existing Display Invoice link passing the invoice number I wish to see.

What do you have to do as a Data Modeler to address ensure the successful implementation of these requests? You must make sure that your hierarchies are setup correctly and contain all of the dimensional detail necessary. You don’t want to leave out a data element (like quarters in a date dimension) only to see it is needed in the future. Since the drill through requests are calling stand-alone reports you should not have the need to do any additional modeling to ensure that these reports can be rendered to the user.

I hope this provides you with a better understanding of these reports and the impact the data model has on these reports.

Posted in Data Warehousing.
BigBear.ai
  • Home
  • Industries
  • Solutions
  • Company
  • Careers
  • Blog
  • Investor Relations
  • Contact
  • Twitter
  • Facebook
  • Linkedin
  • Google My business for BigBear.ai
1-410-312-0885
[email protected]
  • Privacy Policy
  • Terms of Use
  • Accessibility
  • Site Map
© BigBear.ai 2023
We value your privacy
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Privacy Policy | Do not sell my personal information
AcceptCookie Settings
Manage Consent

Cookies Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
JSESSIONIDsessionThe JSESSIONID cookie is used by New Relic to store a session identifier so that New Relic can monitor session counts for an application.
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
CookieDurationDescription
__atuvc1 year 1 monthAddThis sets this cookie to ensure that the updated count is seen when one shares a page and returns to it, before the share count cache is updated.
__atuvs30 minutesAddThis sets this cookie to ensure that the updated count is seen when one shares a page and returns to it, before the share count cache is updated.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
CookieDurationDescription
_ga2 yearsThe _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors.
_ga_NK4L4Q320Q2 yearsThis cookie is installed by Google Analytics.
_gat_gtag_UA_163894009_21 minuteSet by Google to distinguish users.
_gid1 dayInstalled by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously.
at-randneverAddThis sets this cookie to track page visits, sources of traffic and share counts.
CONSENT2 yearsYouTube sets this cookie via embedded youtube-videos and registers anonymous statistical data.
uvc1 year 1 monthSet by addthis.com to determine the usage of addthis.com service.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
CookieDurationDescription
f5avraaaaaaaaaaaaaaaa_session_sessionbusinesswire.com cookie
loc1 year 1 monthAddThis sets this geolocation cookie to help understand the location of users who share the information.
VISITOR_INFO1_LIVE5 months 27 daysA cookie set by YouTube to measure bandwidth that determines whether the user gets the new or old player interface.
YSCsessionYSC cookie is set by Youtube and is used to track the views of embedded videos on Youtube pages.
yt-remote-connected-devicesneverYouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt-remote-device-idneverYouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
Save & Accept