DOMO BC - Card Building - Checklist Pt 1

General Info

• Box Plots and Histograms have a limit of 100,000 rows of data. Other chart types have a limit of 25,000 rows (although the charting engine may limit the data further depending on how many series are represented).
• Sumo Tables only preview a subset of the data instead of Table cards which load as many as 500 rows
• Card Building FAQ: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Card_Building_FAQs

Some tips for approaching visualisation (normal datasets)

• Think about the level of granularity required
• For the Summary Number, check you are using the right aggregation type. For example, the "Current" aggregation is most appropriate for chart types with a trendline.
• Limiting your number of Collections to roughly 3-5 Collections per page is ideal for navigation and information consumption.
• Remove "Auto Preview" from Analyzer to avoid having the preview to load while building the card
• Avoid reporting on dateTime columns unless absolutely necessary
  • instead separate date and time into separate columns.
  • by separating date and time, can leverage a dimension table to attach time and date attributes (Year, Month, or Morning, Afternoon)
  • high cardinality reduces the efficiency of columnar indexing. (which impacts FILTER performance, JOINS etc

Visualizing Semi-additive facts

Many connectors / reports will include semi-additive facts which cannot be aggregated (typically across the time dimension).

These datasets should be carefully shaped to avoid misuse/misinterpretation of data

• Consider a dataset of Bank Statement Balances.  You could not take the sum(balance) column.
• Dimension tables with aggregate metrics make it difficult to provide trend reporting or understand KPIs in context

Consider taking snapshots of the customer dim table state (data assembler) to create a semi-additive fact table OR uploading the transactional table

Additional Resources

• https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/

Domo Q&A  - Optimizing Dashboards & Dataflows with large data

During a 1:1 mentoring session, I gave a consultant some tried and tested methods for optimizing dashboard and card performance against large datasets.



Quick Tips for Developer Experience in Analyzer

• Remove "Auto Preview" from Analyzer to avoid having the preview to load while building the card

• In Analyzer, apply filters to the card before applying dimensions or measures to the Axis.

  • Apply filters from large to small: Country → Region → City
  • Apply time period filter (Current year, Today...)
  • Apply row limit to the Data table (useful for Top 10)

• Make sure the visualisation granularity is not too high (remember 25,000 points)

  • In Date, apply a date granularity (None → Day → Month → Year) -- avoid the use of DateTime columns.

• Use beast modes saved to the dataset or materialized columns to aggregate by month or year instead of relying on card layer beastmodes, (b/c Adrenaline cannot cache results)

• Remove unnecessary Sorts

• Avoid / minimise use of count(distinct) consider approximate_count_distinct

  • https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/OptimizingCOUNTDISTINCTByCalculatingApproximateCounts.htm

COUNT(DISTINCT(`Measure`))
VS
APPROXIMATE_COUNT_DISTINCT ( expression[, error-tolerance ] )

Create a data-exploration Page instead of using Analyzer

• Filters can be made out of smaller dimensional datasets and therefore load values faster than in Analyzer
• Use a Pivot Table or Sumo Card to explore the data
• Use Dataset Views to explore data at the row level

Part 1