#SpringCleaning. 15 Tips to manage your Data Mart in Domo

Time for spring cleaning in the Data Mart?

• Is your Domo implementation an unruly, tangled mess of dataflows and datasets?
• Are you struggling to balance new user requests, rationalize ETL while retaining high performance in your dashboards and dataflows?
• Do you want to leverage Domo to support an increasing number of advanced analytics workflows without creating unnecessary dataflow overhead?

The Domo Governance and Data Stats tools can certainly help identify where the problem areas are, or which assets are going unused; however, the first step to rationalizing your Domo data mart is creating a best-practices model for where and how to name or transform datasets. At Onyx Reporting, our consultants align dataset naming conventions recommended in Domo's consulting methodology with conventional wisdom espoused in traditional BI strategy.

In this blog post we'll cover 15 tips for managing your RAW, INT, PROD and STACK dataflows.

Build Dashboard Specific Datasets

In a traditional data mart implementation, central BI will define views that combine fact and dimension tables into a format ready for consumption in applications and reports. In Domo, we deliver a similar experience through STACKed datasets.
One dashboard could have many datasets powering individual cards; however, to engender data trust and assurance, we endeavour to build each dashboard off one STACK dataset—wherein multiple PROD (production) datasets have been UNIONed into a tall sparsely-populated table.

• Exhaust all card configuration options to minimize the creation of new ETL to meet one card visualization requirement. If you cannot leverage an existing STACK, to minimize code management, consider a Fusion or Data View based on a STACK.
• Avoid data cleansing in STACK dataflows. Acceptable transforms in STACK include conforming column names across PROD datasets, adding an identifier (Activity Type) per PROD dataset or implementing functionality specific to the dashboarding requirement.

YouTube tutorial on visualizing forecast and actuals in a STACKed dataset

Build re-useable stand-alone Production Datasets

Produce a PROD (production) version of each dataset before consuming it in a STACK. Onyx Reporting recommends Kimball's fact table modelling guidelines as a baseline for table design.

• Each PROD dataset should contain data about one type of activity, (ex. sales OR sales forecasts, financials OR budgets). To avoid 'crazy math' in the visualization layer, UNION fact tables in STACK instead of JOINing them in PROD, this produces metric columns that can be aggregated using basic CASE statements and the Activity Type.
• Ensure all rows in a PROD table are at the same grain or level of detail.
• Avoid reprocessing unaltered fact rows or storing unnecessarily wide tables by JOINing dimensional attributes or lookups using Fusions.
• Apply appropriate PDP policies to PROD and STACK tables to manage end-user access.

Stage data in Intermediate Dataflows

While staging data, do the minimum work necessary to produce a clean stand-alone (PROD) dataset.

• Avoid applying visualization specific transforms in INT or PROD (save them for STACK).
• To manage dataflow execution times design data pipelines that minimize reprocessing unaltered rows of data (consider Data Assembler).
• Only change the granularity of PROD datasets after completing all INT data cleansing steps.
  • Consider the example of project reporting where the granularity of RAW data is one row per project. If the STACK requires one row per day; produce two PROD datasets with differing granularities.  To minimize the risk of STACK datasets displaying conflicting metrics due to dataflow execution timing, produce alternate versions of the same PROD dataset in one dataflow (ex. PROD_Proj and PROD_Proj_byDay).
• Treat INT transforms and datasets like Kimball's staging transforms—to minimize confusion, explicitly prevent all access by end users.

Capture RAW data

To facilitate validation against source PaaS and SaaS systems and provide long term flexibility, when possible accumulate a RAW dataset that avoids unnecessary data transformation during ingestion.

• If your source system does not accumulate history, create a RAW historical dataset in Domo using appropriate tools (UPSERT or PARTITION) and minimize the use of recursive dataflows.
• If controlling row count in Domo is a priority, consider offloading or archiving data using appropriate write-back methods.

Go Big.  Do More.  WithLess.

With proper governance tools, consistent ETL design practices and a little automation, central BI teams can use Domo to roll-out or augment a hybrid data lake, warehouse or data mart strategy that scales without a linear investment in data administration.
Consider augmenting your Domo Stats page to identify unused
With experience accumulated during the roll-out of 30+ Domo projects and decades of experience implementing reporting and BI solutions, Onyx Reporting is well situated to service and support your organization during your journey. Contact us to schedule a call.