rclement / datasette-dashboards Goto Github PK

Context

• It would be nice to set a default value for date-based filters such as "Last Month", automatically adjusting the dashboard given the current date
• When exploring metrics on a dashboard, selecting canonical date ranges is tedious (current day/month/quarter/semester/year, past day/month/quarter/semester/year)

Technical details

• Native HTML date input components are basic and do not let select relative dates
• Datasette offers column filters arguments for queries
• Date Range Picker is a JS component providing extensive options for relative and range based date filtering

References

Other BI tools are providing extensive date range filtering with relative dates.

For instance here is how Metabase currently displays it:

Sometimes, a simple table chart is needed within a dashboard to display some data rows.

Here is a sample metadata definition (note there is no display attribute needed as everything can be inferred from the SQL query):

        latest-offers:
          title: Latest offers
          db: jobs
          query: >-
            SELECT
              date(date) as Date,
              title as Title,
              company as Company,
              '<a href="' || url || '">' || source || '</a>' as Link
            FROM offers_view
            WHERE TRUE
              [[ AND date >= date(:date_start) ]]
              [[ AND date <= date(:date_end) ]]
              [[ AND source = :source ]]
              [[ AND region = :region ]]
            ORDER BY date DESC
            LIMIT 5
          library: table

There is already a datasette-table component that could be of use or inspiration.

I'm receiving this error: TypeError: Cannot convert undefined or null to object

In this line:

It just happen the first time I load the dashboard, I have multiples tables and all of them are rendering good.

I'm declaring my tables like this:

my-list:
          title: my list is cool
          db: my_db
          query: |-
            select * from my_table;
          library: table
          display: null

While the generic /-/dashboards/ experience is fine for most cases, it can be useful to be able to embed either a dashboard or a single chart within an iframe, for instance:

<iframe
    src="/-/dashboards/my-dashboard-1/embed?start_date=2023-01-01&end_date=2023-12-31"
    frameborder="0"
    width="800"
    height="600"
    allowtransparency
>
</iframe>

<iframe
    src="/-/dashboards/my-dashboard-1/my-chart-1/embed?start_date=2023-01-01&end_date=2023-12-31"
    frameborder="0"
    width="800"
    height="600"
    allowtransparency
>
</iframe>

Ideas

Inspired by Metabase dashboard public embedding:

• Define dedicated URLs:
  • Dashboards: /-/dashboards/<dash>/embed
  • Charts: /-/dashboards/<dash>/<chart>/embed
• Dashboards display :
  • Hide header (breadcrumbs)
  • Keep footer ("Powered by Datasette")
  • Keep title, description, filters, charts
• Charts display:
  • Hide everything except the chart

Roadblocks

What about permissions?

• To allow embedding, selected dashboards and charts must be made public
• Dashboards and charts are currently sending requests to Datasette from client-side (JS), opening up the possibility to request other data behind the hood
• How to restrict access to dashboards and charts?
• How to restrict access to a dashboard/chart but enable public embedding?

I'm not sure if it's an issue related to datasette-dashboard or something else in the toolchain but it took me some time to understand my query wasn't working due to double quotes in my where clause -- when the YAML file is converted into JSON.

The YAML file was well-formed, but datasette-dashboard wasn't displaying the content of the chart.

Honestly, I don't have any idea how to deal with this in datasette-dashboard. Maybe put a comment in the example:

        events-source:
          title: Number of events by source
          db: jobs
          # Be careful: don't use double quotes (") inside your query.
          query: SELECT source, count(*) as count FROM events WHERE TRUE [[ AND date >= date(:date_start) ]] [[ AND date <= date(:date_end) ]] GROUP BY source ORDER BY count DESC

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Error type: Cannot find preset's package (github>whitesource/merge-confidence:beta)

faker dependency is updated too often and brings noise to Renovate auto updates.

@HaveF suggests to limit the update frequency using Renovate configuration.
For instance to update the package monthly on the first day of the month:

{
  "packageRules": [
    {
      "matchPackageNames": ["faker"],
      "schedule": ["before 4am on the first day of the month"]
    }
  ]
}

Discussed in #203

Issue

When using a text filter type, styling is not consistent with other filter types such as date and select:

Solution

The culprit comes from Datasette default CSS for form input[type="text"].

To reset the style, we can use the following snippet:

form input[type="text"] {
  all: revert;
}

leading to this more consistent styling:

This issue provides visibility into Renovate updates and their statuses. Learn more

This repository currently has no open or pending branches.

• Check this box to trigger a request for Renovate to run again on this repository

The top-right menu bar is not showing in the plugin pages:

• dashboard_list.html
• dashboard_view.html
• dashboard_chart.html

The issue seems to come from a misuse of the nav template block instead of crumbs.

When opening the Vega Editor for Vega/Vega-Lite charts:

The data URL is relative to the deployment domain and is not absolute, thus making Vega Editor unable to fetch the data:

For Vega Editor to fetch the data, one must insert the deployment domain as a prefix to the data URL:

It would be useful to render charts using an absolute data URL to overcome this issue.

Hi, 😄

I found that markdown didn't show quite consistently.

select '# Analysis details

We wanted to analyze data from job offers, using the **`python` search keyword**
from three sources of job-boards:
[APEC](https://www.apec.fr),
[Indeed](https://fr.indeed.com/) and
[RegionsJob](https://regionsjob.com).

## Process

The process was in 3 steps:

* Extraction
* Transformation
* Loading

After the ETL process, an extra data enrichment step was developed to provide
location geocoding, based on place names.

## SQL query

```sql
SELECT
  date(date) as day,
  count(*) as count
FROM offers_view
GROUP BY day
ORDER BY day
```'
as demo_markdown

But when display in dashboars, there is no * before list items

Using datasette-dashbaords 0.6.1 library: table with Datasette 1.0a3 or 1.0a4 fails to render and gives following Javascript error:

Unhandled Promise Rejection: TypeError: undefined is not an object (evaluating 'data.columns.forEach')
dashboards.js:234

The new Datasette 1.0 JSON API does not have a columns property anymore. Here's an example of new JSON:

{
  "ok": true,
  "rows": [
    {
      "Parameter": "my-parameter",
      "None": 123,
      "Some": 456,
      "Many": 90
    }
  ],
  "truncated": false
}

When accessing datasette-dashboards pages, breadcrumbs are broken: an extra home entry is always shown at the end.

Here are some screenshots:

The features are implemented in this plugin and incorporated into the demo deployment, the more the README screenshot is out-of-date!

In order to automate the process of keeping the screenshot up-to-date, one solution would be to use shot-scraper:

• https://shot-scraper.datasette.io
• https://simonwillison.net/2022/Mar/10/shot-scraper/
• https://simonwillison.net/2022/Oct/14/automating-screenshots/

As this tool is using a browser automation, we might be able to point it to the demo dashboard and wait for a certain amount of time (or condition) to take the shot, so that all JS code has been executed and rendered.

As proposed by @20after4:

• It would be nice if datasette-dashboards would support custom chart rendering engines
• Using Datasette plugin hooks would allow other plugins to implement rendering engines

Let's discuss about the possibilities here, as Datasette Dashboards should keep some rendering engines built-in allowing the "one-stop shop" experience for new users.

It would be nice for the dashboards to be allowed to toggle a fullscreen mode at the click of a button.

Here is a snippet to toggle fullscreen mode:

<button
  onclick="document.fullscreenElement
    ? document.exitFullscreen()
    : document.body.requestFullscreen()
  "
>
  Toggle Fullscreen
</button>

Instead of document.body it is more advisable to select a sub-section of the page to be made fullscreen: for instance, it could be only the central dashboarding part (title, description, filters, chart grid) without header nor footer.

Hi, Romain,

I love you repo! ❤️ very useful!

I'm curious about your workflow. Could you please give me an overview?
When I'm debugging, I find myself manually closing the server with ctrl+c every time I adjust the metadata.yml file. Then I have to restart it and refresh the page to see if the configuration is appropriate. If it's not, I have to repeat the previous steps.
I'm not sure if there's a way to automate this entire process, such as automatically refreshing the page. I'm wondering how you handle it.
Thanks again!

List of values would be great for filters.

I hope the following example speaks by itself.

      filters:
        country:
          name: Country
          type: list
          values: world,be,es,fr,uk,us
        language:
          name: Language
          type: query
          # it has to be a unique column
          sql: select distinct language from this_table;

Some ideas for other visualization types:

• Table of results
• Leaflet maps
• Single metric (with unit / prefix / suffix)
• Text : plain / html / markdown

Some ideas to improve the dashboards system:

• Dashboard filters: inspired by Metabase, allow to define filters at the dashboard level dynamically modifying "WHERE" statement in SQL requests (would need to define per chart which field is to filter in metadata) (using query named parameters and/or canned query parameters?)
• Dashboards permissions: use Datasette RBAC system to restrict access to dashboards (maybe using the "roles" feature?)
• Dashboard export (HTML/PDF) / public sharing?
• Generic/abstract configurable widgets: use "vega" for advanced customization otherwise pre-setup "line", "area", "bar", "scatter", "pie", "choropleth", "word-cloud", "metric" (already implemented), "markdown" (already implemented) (see Metabase)
• Chart builder? (there is already Vega editor)
• Dashboard builder? (metadata seems enough)
• Link to chart visualization in full page
• Dashboard layout: define a generic layout (with better responsive sizes for small screens 240px) and allow to specify a custom layout in metadata
• Link to chart underlying data: display call to action to go to the SQL query page

Misc:

• Combining custom layouts and Markdown text would allow for "literate notebooks" (cf Markdown in Metabase Dashboards)

Documenting the configuration of this plugin in the README was nice at first, but the more options added the less it becomes readable. And when adding generic widgets and such features, it will not be maintainable.

So it might be time to start moving the documentation to a standalone static website. Here is the plan:

• Use mkdocs documentation system
• Use mkdocs-material theme
• Deploy to GitHub Pages on rclement.github.io/datasette-dashboards/

Add an option to select filter type allowing to select multiple values.

Example:

...
      filters:
        source:
          name: Source
          type: select
          multiple: true
          options: [Source 1, Source 2, Source 3]
          default: [Source1, Source 2]
...

2 potential implementation:

• HTML select tags can specify a multiple attribute allowing multiple values selection of options: https://developer.mozilla.org/en-US/docs/Web/HTML/Attributes/multiple
• HTML checkbox input using a fieldset: https://developer.mozilla.org/en-US/docs/Web/HTML/Element/Input/checkbox

When specifying a default value for a filter, it is not applied automatically when opening the dashboard.

As done by Metabase (and others), when accessing a dashboard without query parameters, the request should be redirected to the same dashboard but with query arguments filled with filters default values.

Example:

Using this configuration:

plugins:
  datasette-dashboards:
    job-offers-stats:
      title: Job offers statistics
      description: Gather metrics about job offers
      layout:
        - [analysis-note, offers-day, offers-day, offers-count]
        - [analysis-note, offers-source, offers-day-source, offers-region]
      filters:
        date_start:
          name: Date Start
          type: date
          default: '2021-01-01'
        date_end:
          name: Date End
          type: date

When accessing /-/dashboards/job-dashboard, it should be redirected to /-/dashboards/job-dashboard?date_start=2021-01-01.

It is now possible for the Datasette website to reference third-party plugins:
https://github.com/simonw/datasette.io/blob/main/plugin_repos.yml

Once Datasette Dashboards is more in a more acceptable usage (generic widgets, public sharing),
it might be a good idea to submit a PR by adding rclement/datasette-dashboards to the list to gain some more users and contributors.

Context

As pointed out by @HaveF and discussed in #142, Datasette sets limits on the number of rows returned by SQL queries (setting max_returned_rows defaulting to 1000). This can have an impact on rendered charts when more than max_returned_rows are expected to be displayed.

Technical details

To be able to detect when a data truncation occurs, all charts should be using the JSON API with the _shape=objects query argument, allowing to get the truncated boolean value.

For instance, this query:

https://datasette-dashboards-demo.vercel.app/jobs.json?sql=SELECT date(date) as Date, title as Title, company as Company, source as Source, city as City, longitude, latitude FROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL AND date >= date(:date_start) &date_start=2021-01-01&_shape=objects

yields the following JSON output:

{
    "ok": true,
    "database": "jobs",
    "query_name": null,
    "rows": [
        {
            "Date": "2021-04-21",
            "Title": "Devops Python H/F",
            "Company": "Sully Group",
            "Source": "RegionsJob",
            "City": "Aix-en-Provence",
            "longitude": 5.4451153,
            "latitude": 43.5227288
        },
        ...
    ],
    "truncated": true,
    "columns": [
        "Date",
        "Title",
        "Company",
        "Source",
        "City",
        "longitude",
        "latitude"
    ],
    "query": {
        "sql": "SELECT\n  date(date) as Date,\n  title as Title,\n  company as Company,\n  source as Source,\n  city as City,\n  longitude,\n  latitude\nFROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL\n   AND date >= date(:date_start) \n  \n  \n  ",
        "params": {
            "date_start": "2021-01-01"
        }
    },
    "error": null,
    "private": false,
    "allow_execute_sql": true,
    "query_ms": 6.713455999943108
}

Design

As a reference, Here is how Metabase informs the user with a warning tooltip when the row limit is exceeded:

When rendering a Vega-Lite chart with a tooltip property, the rendered tooltip popup table is broken:

When opening the same chart within Vega Editor, here how it should look like:

This might be a case of CSS style conflict between Datasette's own style for tables and Vega-Lite tooltip style.

The current layout for filters is a simple HTML form behind a summary field:

Instead, presenting the filters in a more useful way would be to use an HTML fieldset like this:

It would be great if values for the select filter type could be dynamically filled with the results from a query.

Sample configuration:

...
      filters:
        source:
          name: Source
          type: select
          db: test
          query: select distinct name from sources order by name asc
...

It would be nice for dashboards to be able to auto-refresh itself at a given time interval.

Here is how it looks like in Metabase:

Sensible auto-refresh time interval values in minutes: 1, 5, 10, 15, 30, 60

Apparently it can be done directly by specifying HTML meta tag (don't know if all browsers honor this):

<meta http-equiv="refresh" content="30">

Otherwise, a more traditional way is to trigger the refresh in JS:

window.setTimeout(function() { window.location.reload() }, 1000)

Only Vega-Lite charts are currently supported using the vega library. It would be useful to support both specifications.

When using Vega charts using random generators under the hood, such as wordcloud transform, the rendered chart is always different at each refresh. While in practice this is not a problem, it does not offer a deterministic approach to charts.

Apparently, this is a bit tricky but there is a workaround as explained here: vega/vega#1870 (comment)

vega.setRandom(vega.randomLCG(0))