News for hackers. Stuff that matters.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Tuesday April 02, 2019 @10:56AM (#19554756)
@akalitenya are you even in the clear with this? Some of the Old LookML syntax is an exact copy.

But more importantly, the challenge for any such tool is to go beyond use by 2-3 people. At 2-3 people anything will work. Where BI tools (open source and close source) struggle is scale: having all the right features for, essentially, a group of users who actually don't know how to work with data (did I just say that aloud?). Chartio caps at 20 people. RJ capped at 50-100 (and later became Stitch for that reason). We haven't seen where Metabase caps, but I bet it is in a similar range. 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.

Also, a combination of BI with LookML is a complicated product. During my days at Looker, we were handling 50+ bugs / week, and filing 1,000+ tickets. Every day we were filing over a 100 new features.

So with all that, the question is, is it really worth the struggle? What's the end vision for supporting this? Why should someone who implements BI for a living bet on this product?

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @01:05PM (#19556335)
> 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.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by endlessvoid94 on Tuesday April 02, 2019 @03:27PM (#19557731)
I used Looker for years and have always wanted a product priced less for the enterprise and more for developers. I even prototyped a similar tool myself.

Congratulations on the launch. I will be using mprove and will give you feedback along the way.

EDIT: I would love to get in touch with you and learn more about what your plans are. Possibly collaborate if you'd be open to that. I'm [email protected] -- I couldn't find your contact info anywhere!

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Thursday April 04, 2019 @12:00AM (#19569206)
Thank you, I sent you an email. I am open to any suggestions - [email protected].
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by __coaxialcabal on Tuesday April 02, 2019 @03:35PM (#19557797)
> 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.

At one point Looker was essentially a web application with a query service that could be scaled by adding more servers behind a load balancer. Each end user is essentially working in a shared nothing environment and the query engine is driven by metadata stored in a git repository. Looker itself did not manage cache or analytical processes. All of the real effort to scale was in the database backend.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Tuesday April 02, 2019 @05:56PM (#19558970)
"scale" meant adoption, not performance. Scaling performance can generally be solved with hardware. Adoption, not so much.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by zackt on Thursday April 04, 2019 @12:53AM (#19569436)
Looker is easy to cr*ck (terrible security) and you can deploy it everywhere for your BI needs. So we dont need similar product.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by MechanicalTwerk on Tuesday April 02, 2019 @11:07AM (#19554893)
I agree with pretty much everything you've said. However, as someone who has used Chartio extensively, I'd say 20 is wayyy too low. It can definitely handle 100s. But, like you said, 1,000s is a struggle for anyone.

Also, if you think this is a rip off of LookML, you should take a look at what GitLab is doing with Meltano. They completely jacked LookML.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by g14i on Tuesday April 02, 2019 @12:57PM (#19556240)
Please, do you mind sharing what exactly makes a BI solution difficult/struggling for 1,000+ users?

Is it something technically related or more business/feature related?

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Tuesday April 02, 2019 @02:06PM (#19556945)
mmmm, both!

Tech-wise, data stacks are complicated. Hundreds of pre-existing tools, with many owners and diverse interests. BI is where they all meet. With some organizations its easy to build a central data warehouse, but in a large corporate environment that's a dream. Imagine how many data warehouses an organization like Microsoft might have? OK, MS is too large - how about Expedia or Zillow? Thousands? So you connect a tool like Looker--that was designed for 2-10 connections--to a thousand? How do you even administer all the connections? How do you govern access? Access permissions at Looker (and other BI tools) are great, but it would take you 10 years to set them up for 1,000 DBs. And what about tool access? Some want SSH tunneling, others want complete on-prem. Some need Google authentication, while others want one through a pre-existing corporate login. Most large organizations typically end up building--or hiring someone to build--their own custom solutions on top of these tools. Such custom solutions are bigger projects than the implementation of a BI tool to begin with. Oh, and most also fail. The success of a BI in an enterprise environment is partly good sales/marketing/support, but a big part is due to the product achieving some form of maturity with all this side tech--the stuff totally not core to the analytics itself.

Business-wise, try getting people with diverse backgrounds agree on common terminology. Let's take marketing for instance. Should be easy, no? But hey, marketing is actually like 10-20 different kinds of people - some know SQL, others can't put 2+2 together and arrive at anything other than the word "magic". Some think in terms of stories--others think in terms of conversion funnels. OK, so you've put together some data dictionary, did some training, segmented users into 1) technical ones (SQL/LookML/BackML...), 2) business (explore), 3) consumers (dashboards/pretty charts). But now it turns out that much of the data they rely on is generated by a different team--say, operations or sales. Again, you've got 10-20 different kinds of personalities there. Somehow all these people have to agree. How do you make them all agree? Short answer: you cannot. No one can. They don't even like speaking to one another - and now you are going to come in and make them agree on what kind of KPI is going to determine their success => bonus. Hell no!

There are ways to make progress on both fronts. And, no doubt, an open source project has some chance. But it is not easy. And no one has really done it in 30 years. Many have tried.

Full disclosure: I love BigQuery (Google Cloud partner). Love Looker. Rely on Open Source constantly. Just trying to demonstrate a realistic view of how hard this problem is.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by g14i on Wednesday April 03, 2019 @08:15AM (#19562693)
Wow. Thanks for putting great energy for this answer. Great perspective.

Again, if you don't mind sharing, what would be a dream solution for you, something that could solve most of the problems you mentioned?

I understand that some of them are cultural problems (like people no talking to each other), but maybe somehow a technology could solve this?

If you could think of a new approach, what would that be?

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by thingsilearned on Friday April 05, 2019 @06:46PM (#19587744)
Hey @segah, founder of Chartio here. I can deeply second all the comments on how long of a feature tail BI is, and the amount of work it takes to have real product depth and stability vs. an impressive demo. It takes years, and ongoing maintenance that is impossible to estimate in the beginning.

Also, it's definitely a challenge to support 100's and 1000's of users digesting data, especially in the democratized fashion that we're typically used in. It takes good data governance, support, and admin tools. I gotta chime in and say for the record though that Chartio well supports many such customers.

It may be weird for you to chat as you worked at Looker, but I'd love to hear anytime on why you see Chartio capping out at a lower # than the others listed. You can reach me at dave-at-chartio.com!

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by yellowapple on Tuesday April 02, 2019 @08:17PM (#19559767)
I don't think ripping off LookML is a bad thing; on the contrary, it's probably an awesome thing for Looker to be in the position of shaping how companies model their data and the relationships thereof. At the very least, it means Looker can turn around and say "hey, if you're outgrowing this smaller tool that uses LookML, why not switch to a platform that can meet your scale (like, say, us)?". The more smaller-scale "competitors" aping LookML, the more teams outgrowing those platforms and looking for larger-scale platforms where they can `git pull $old-platform`, `git push $new-platform`, copy over some data sources / credentials, and call it a day. If $new-platform=Looker, then congrats, Looker now has a decently-sized customer.

In other words: Looker ought to embrace the idea of LookML being the data modelling language. Encourage other implementations of it. Treat them as customer sources instead of customer sinks. That's how they can take over the world :)

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by morenoh149 on Tuesday April 02, 2019 @12:58PM (#19556250)
Why discourage an alternative? We've already seen there is demand for libre analytics tools as an alternative to google analytics. I'd use this product in the lower range as well <100 person company. I say go for it, looker is doing fine they won't care or be phased by this at all.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by sbr464 on Tuesday April 02, 2019 @12:30PM (#19555900)
By support do you mean performance/load or array of features/team tools to be effective, support different use cases? Curious what you mean by 20 user cap for Chartio.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by pplonski86 on Tuesday April 02, 2019 @11:46AM (#19555378)
What is the main reason that LookML is complicated product?
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Thursday April 04, 2019 @12:41AM (#19569382)
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.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Thursday April 04, 2019 @11:25AM (#19573405)
@akalitenya the way I used to explain it to the technical audience is: would you rather program in a low-level language like C or in a high-level language like Java, Python, or Node? Well, if you are writing compilers, you might choose C, but if you are doing almost everything else, you want to choose a language that fits the purpose: python for data science, Node for web backend, etc. Same with LookML - it provides a language that is ideal for doing analytics in-database. SQL is a language for extracting and summarizing data - it is not an ideal language to preserve business logic in just as C would not be an ideal language to preserve backend web dev. work in.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Thursday April 04, 2019 @01:46PM (#19575081)
@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?
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Friday April 05, 2019 @12:03PM (#19583835)
that's the wrong question. People choose different tools for all kinds of reasons. And many of these reasons are totally valid. A tool, Looker or otherwise, is no panacea.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by danpalmer on Tuesday April 02, 2019 @12:14PM (#19555686)
The headlining feature seems to be that it has a dark and a light theme. This isn’t very encouraging for the rest of the product. I’m sure there’s a lot of good stuff here, but themes aren’t important enough to be the first feature mentioned.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by morenoh149 on Tuesday April 02, 2019 @12:59PM (#19556263)
+1 it should not be the first feature listed, bump that down
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Thursday April 04, 2019 @12:04AM (#19569225)
Thank you, I plan to add more videos on the page.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by sandGorgon on Tuesday April 02, 2019 @09:06AM (#19553606)
this is super cool and i would pay for this. Bigquery is a cheap alternative to a lot of the mobile analytics tools.

Quick point however - why do you need a new database ? You can use a table inside bigquery itself. It seriously reduces the dependencies required.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @03:08PM (#19557539)
Mprove creates permanent derived tables in BigQuery if the user wants it.

But you can't use BigQuery for OLTP.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by sandGorgon on Tuesday April 02, 2019 @09:11PM (#19560014)
No - I'm talking about MySQL being needed as a dependency.

https://github.com/mprove-io/mprove/blob/master/deploy/docke...

Can you not use biquery database itself. Create tables for your internal use instead of MySQL ?

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Wednesday April 03, 2019 @12:37AM (#19560784)
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.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by sandGorgon on Wednesday April 03, 2019 @09:52AM (#19563288)
Hmm..I would prefer to not have it. In production, managing database persistence is very hard. Especially when you go down the kubernetes road.

I would take higher latency, but avoid pulling in a whole database infrastructure.

Plus a huge number of us use postgresql..so that becomes another set of a mess. I would strongly urge you to do this on the same bigquery database that you would connect to anyways.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Friday April 05, 2019 @07:38AM (#19581540)
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.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by vgt on Tuesday April 02, 2019 @01:02PM (#19556305)
BigQuery PM here. Nice work!

Here's a recently built Graphite connector as well:

https://twitter.com/vadimska/status/1112816503055843330

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by siculars on Tuesday April 02, 2019 @01:55PM (#19556820)
Will this handle (repeatable) “record” (struct/array) data types natively?

/disclaimer: work in google cloud

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @02:49PM (#19557347)
Yes it should, there is special "unnest" parameter for fields in BlockML reference - https://mprove.io/docs/blockml/fields/dimension
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by segah on Tuesday April 02, 2019 @04:37PM (#19558321)
wow, impressive! how about symmetric aggregates (e.g. being able to do correct summation/aggregation on numeric values despite a one_to_many join)?
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @11:57PM (#19560590)
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.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by infinite8s on Thursday April 04, 2019 @08:20PM (#19578453)
How do the underlying queries look for symmetric aggregates? Sadly SQL never supported the ability to compute an aggregate based on the unique values of another column.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Friday April 05, 2019 @05:50AM (#19581075)
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
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by verdverm on Tuesday April 02, 2019 @08:44AM (#19553417)
Link to the open source part?

I can't seem to find any on the site...

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by rahimnathwani on Tuesday April 02, 2019 @08:45AM (#19553421)
There's a github link at the top of the page:

https://github.com/mprove-io/mprove

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by verdverm on Tuesday April 02, 2019 @08:51AM (#19553462)
Oh, had to rotate my phone, does not show up in the menu on a smaller screen layout.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by pugworthy on Tuesday April 02, 2019 @09:37AM (#19553886)
Wow, very different menus on mobile based on orientation. Vertical has “Pricing” and no open source mention. Horizontal gives a GitHub link and no mention of pricing.

I assume there was a time when this was meant to be a non-open source project.

Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @11:55AM (#19555493)
thanks, i just fixed it
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by mwexler on Tuesday April 02, 2019 @02:03PM (#19556919)
In mobile, it doesn't seem to appear til you rotate your phone, but it's there in desktop view.
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by yantra_ml on Tuesday April 02, 2019 @02:19PM (#19557055)
Do you support on-prem?
Re:Show HN: Open-Source Business Intelligence for BigQuery –... by akalitenya on Tuesday April 02, 2019 @02:59PM (#19557468)
Source code is open. Anyone can deploy Mprove to his server and use it for free.