akalitenya

Bigquery exists only in Google Cloud. This is a columnar database like Redshift. It is not designed for fast processing small queries that are necessary to support the operation of any application. Its main feature is that it can scale the execution of heavy analytical queries between 10,000 nodes transparently for the end user. Users have no control over Bigquery instances. This is super cost effective cloud analytical database as a service to be used as centralized data warehouse for company of any size.

Mprove does it the way the Looker did it before:

   CREATE TEMPORARY FUNCTION mprove_array_sum(ar ARRAY<STRING>) AS
  ((SELECT SUM(CAST(REGEXP_EXTRACT(val, '\\|\\|(\\-?\\d+(?:.\\d+)?)$') AS FLOAT64)) FROM UNNEST(ar) as val));

...

   SELECT COALESCE(mprove_array_sum(ARRAY_AGG(DISTINCT CONCAT(CONCAT(CAST(a.id AS STRING), '||'), CAST(a.population AS STRING)))), 0) as a_cohort_size

Recently, Looker began to do it differently, most likely to improve bigquery performance:

  COALESCE(ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE(lesson_5_cohorts.population ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST(lesson_5_cohorts.id  AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(lesson_5_cohorts.id  AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST(lesson_5_cohorts.id  AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST(lesson_5_cohorts.id  AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) )  / (1/1000*1.0) AS FLOAT64), 0), 6), 0) AS lesson_5_cohorts_m_sum_distinct

@segah Good point. Based on your consulting experience, can you tell if there have been any cases when your clients chose another Business Intelligence tool instead of Looker, what are these tools and what are the reasons?

LookML supports different databases as sources. Sometimes Looker adds its own LookML parameters for database - which makes it difficult to read the documentation.

For analysts who work with SQL queries, any wrapper on a language seems complicated. But in the case of LookML and BlockML, in a couple of days you already begin to understand how this works, and after a week you can not tear yourself away from using the product.

Looker and Mprove are in the middle between fully custom DIY solutions and simple query generators tools.

Thank you, I plan to add more videos on the page.

Thank you, I sent you an email. I am open to any suggestions - [email protected].

The MySQL image specified in the docker-compose file you mentioned. It is used for the internal data of the Mprove application (users, projects, members, etc.). Each user action in the web client (angular) can initiate several queries to this database through an backend request. Delays here are crucial. network latency - so you need to keep the database as close as possible to your server side, read / write delays - for queries such as finding a user / setting a username / creating a member, etc.

Yes, look at this page - https://mprove.io/docs/blockml/fields/measure. You need to specify measure "type" and "sql_key" that will be used to avoid counting duplicates.

Mprove creates permanent derived tables in BigQuery if the user wants it.

But you can't use BigQuery for OLTP.

Source code is open. Anyone can deploy Mprove to his server and use it for free.

Yes it should, there is special "unnest" parameter for fields in BlockML reference - https://mprove.io/docs/blockml/fields/dimension

> Some of the Old LookML syntax is an exact copy.

I met the online Looker demo a few years ago when I was looking for a business intelligence tool for another project.

Looker has a closed source code, so I did not see what algorithm is used to build queries.

I kept those LookML features that I understood and liked. However, in some places LookML is confusing (for example - references and aliases). I made them differently.

Later Looker quit using YAML.

> Very few BI products have actually surpassed 100 users at target installations. And beyond 1,000 is a real challenge that only few, and even then with a lot of assistance, can support: Tableau, Looker, Microstrategy, maybe Birst, maybe Domo.

Scaling that size is not a top priority right now.

> Also, a combination of BI with LookML is a complicated product.

You should have told me this a couple of years ago. It seemed pretty simple.

> So with all that, the question is, is it really worth the struggle?

Yes, If people will use it.

> What's the end vision for supporting this?

In future - maybe "skinny" or "thin" option mentioned here - https://medium.com/open-consensus/2-open-core-definition-exa...

>Why should someone who implements BI for a living bet on this product?

If you can not afford Looker (like me) and want to use similar product.

thanks, i just fixed it

do you plan to opensource all of Stitch integrations?