t27duck/active_reporting

OLAP-like DSL for ActiveRecord-based reporting

active_reporting's Issues

Change Mini Test by Rspec + FactoryBot + Dummy App

What?

While doing a couple of PR in this repo I notice specs data is shared for all specs. This makes harder to add new test cases. Also this gem relay on ActiveRecord. What if specs were done again a dummy rails app with Rspec and FactoryBoy.

This is an example

The new elements

1. Rspec: I've work with Rspec and I find super clear their DSL. But this is just my opinion. Nothing again Mini Test.
2. FactoryBot: It makes super easy to generate isolated test data for each spec. I find it super helpful. Also seed.rb file is getting big.

What do you think? What are your thoughts on this?

FactModels Table

Hey,

sorry if the question is in the wrong place, but I need some help to get started.

I'm currently learning on data warehousing for university and my work and want to build a small data warehouse for a project myself. Now I found this gem and trying to understand this gem's usage.

I made myself familiar with star schema, snowflake schema etc. but I don't really understand how I link the FactTable with the actual table in the database.

I know it should be a table with some belongs_to associations to the dimensions, but how would I specify a migration for this? At least I didn't find an example in the docs.

So consider my following example where I'm modeling a simple cube for reporting details to hospital stays and patients. I have 3 model - patient, case and diagnoses:

create_table "cases", force: :cascade do |t|
    t.string "casenumber"
    t.date "admission_date"
    t.date "discharge_date"
    t.bigint "patient_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["patient_id"], name: "index_cases_on_patient_id"
  end

  create_table "diagnoses", force: :cascade do |t|
    t.string "icd_code"
    t.text "icd_description"
    t.bigint "case_id", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["case_id"], name: "index_diagnoses_on_case_id"
  end

  create_table "patients", force: :cascade do |t|
    t.string "first_name"
    t.string "last_name"
    t.date "birthdate"
    t.string "gender", limit: 1
    t.string "domicile"
    t.string "street"
    t.integer "zip_code"
    t.integer "house_number"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "diagnoses", "cases"

Now I have a CaseFactModel with the dimensions patient, case and diagnoses

class CaseFactModel < ActiveReporting::FactModel
    self.model = CaseFact
    
    dimension :patient
    dimension :case
    dimension :diagnosis

    # dimension_filter :for_admission_date, ->(year) { joins(:case).where('extract(YEAR from admission_date) = ?', year) }
end

class CaseFact < ApplicationRecord
  belongs_to :case
  belongs_to :patient
  belongs_to :diagnosis
end

Now, from what I've read this FactTable has to be a database table aswell, right? In the example test files in the repository I couldn't find any migration related to the fact tables.
And how does this FactModel map to this table then, as it's not inheriting from ActiveRecord::Base?

Am I getting something completely wrong here? Any help is appreciated, thanks in advance!

Support for weighted Average

Wondering if the aggregations can support weighted averages>

sample rails app to test

Hi,

Please provide any sample app or hosted app to check how does the reporting looks.

Thanks.

avoid using Order as an object name in the documentation

Not a huge deal, but using 'Order' as a model object in the documentation confuses it with commands to set the sort order. It would be clearer if you used Sale or similar.

Support for multiple aggregations like sum and count together

Hi,
At first, excellent work on this gem especially around the multidimensional reporting.
I am currently working on a use case for which i need to provide both sum and count values for a vendor for different months which the current gem does not support. Could you please advise on a work around for this?
Thanks in advance for your support.

Any plan to support mongoid?

Implicit hierarchies with datetime columns on model relations

In this example a Sale has a belongs_to ActiveRecord relation with DateDimension.

class Sale < ActiveRecord::Base
  belongs_to :placed_at, class_name: 'DateDimension'
end

class SaleFactModel < ActiveReporting::FactModel
  self.measure= :total

  dimension :placed_at
  dimension :item
end

class DateDimensionFactModel < ActiveReporting::FactModel
  default_dimension_label :date

  dimension_hierarchy [:date, :month, :year, :quarter]

  dimension_label_callback :quarter, ->(q) { "Q#{q}" }
end

I would like to use Implicit hierarchies with datetime columns on date_dimension table through sale fact model.

Something like this:

metric = ActiveReporting::Metric.new(
  :a_metric,
   fact_model: SaleFactModel,
   dimensions: [{ placed_at: :date }]
)

I think this is related with this other PR because we could allow pass a configuration object like this:

ActiveReporting::ReportingDimension.new(
  @placed_at_dimension,
   label: :date,
   datetime_hierarchy: month
)

What do you think?

Color coding rows in the report

Hi,

I would like to know the we can do color code rows in report as mentioned in below link ?

bogdan/datagrid#141

default dimension_filters

Dimension successfully defaults to using 'name', maybe a dimension_filter should do so as well.

e.g. assuming I have dimension :player in my FactModel, asking for dimension_filter: {player: 'Jez'} would filter on player.name = 'Jez'.... it would not require adding dimension_filter :player, ->(x) { joins(:player).where(players: {name: x}) } to the model.

Set custom name on dimension

What is this?

Hi, first thanks for your work. I'm trying it and the code looks great.
One question, I was asking if would be possible to set a custom name for each dimension? I will try to explain myself with an example.

# leave.rb
class Leave < ActiveRecord
  belongs_to :employee
end

# leave_fact_model.rb
class LeaveFactModel < ActiveReporting::FactModel
  dimension :start_on
  dimension :employee
end

# employee_fact_model.rb
class EmployeeFactModel < ActiveReporting::FactModel
  dimension :employee
  dimension_hierarchy %i[gender birthday_on]
end

As you can see I've 2 models and 2 fact models. LeaveFactModel has a belongs_to relation with Employee. I want to get this report:

[
  {"num_of_leaves"=>1, "start_on"=>Mon, 04 Jun 2018, "gender"=>"female"},
  {"num_of_leaves"=>1, "start_on"=>Mon, 01 Aug 2018, "gender"=>"male"},
  {"num_of_leaves"=>3, "start_on"=>Mon, 10 Aug 2018, "gender"=>"male"}
]

Instead what I get is employee instead of gender like this

[
  {"num_of_leaves"=>1, "start_on"=>Mon, 04 Jun 2018, "employee"=>"female"},
  {"num_of_leaves"=>1, "start_on"=>Mon, 01 Aug 2018, "employee"=>"male"},
  {"num_of_leaves"=>3, "start_on"=>Mon, 10 Aug 2018, "employee"=>"male"}
]

This is my metric:

ActiveReporting::Metric.new(
   ...,
   dimensions: [:start_on, { employee: :gender }]
)

I see you're using employee witch is dimension name like AS in the query fragment

What if we could set label like AS name?

# Now
ss = ["#{label_fragment} AS #{name}"]

# My proposal
ss = ["#{label_fragment} AS #{label}"]

I'm proposing this because no matter what it's the dimension in the filter gender or birthday_on in my case, generated SQL result will be always employee

Performance?

What about performance? I mean, you are using OLTP database (Postgres) for OLAP queries. The key value of this gem is to change developer's mindset first to treat reports functionality as a set of reporting best practices? Or it's aimed to increase reports render speed?

Allow datetime hierarchies with date fields too

What?

Now the gem only allow datetime hierarchies for datetime columns on database. It seems date fields are also valid

Values of type date and time are cast automatically to timestamp

Possibly incorrect code example in wiki

Hello 👋

This library is really great. We are thinking about using it for a live analytics dashboard at work. Thanks for all the work you have put into it.

While going through the docs I think I found an out of date example in the wiki

https://github.com/t27duck/active_reporting/wiki/Configuring-Dimension-Information

I don't think hierarchy_labels exist anymore: 399ebe9

Unfortunately since I am just getting up to speed on the gem I am not yet sure what it should be changed to. But I thought I would at least point it out. Thanks!

[FAQ] has_many relationships do not work

Hi,

You made changes to README file 4 months ago.

Does has_many relationships still not supported ?

Thanks.

Format report results

When running a report, is it possible to specify / control the format of the results that are returned?

By default I am getting;
{ measure_name => measure_value, dimension1_name => dimension1_value, dimension2_name => dimension2_value

I would like to get a "values only" output;
{ [dimension1_value, dimension2_value] => measure_value }

Consider null values ​​when group by dimension

Problem

Considering this models

class Game < ActiveRecord::Base
  belongs_to :platform
end

class Platform < ActiveRecord::Base
  has_many :games
end

I want a report of Games grouped by Platform dimension. This could be easy achieve adding a dimension platform to GameFactModel.
But, what if i have some non categorized Games that have platform_id equals nil.

In this scenario the report will only get the Games that have a Platform. Games without a Platform will be missing.

---

Possible Solution

This behavior can be achieve using a Model.left_outer_joins instead using Model.joins on ActiveReporting::Report#statement.

I'm not sure what is the best way to add this behavior to the gem.

I could add another type of dimension , a left_join type, id addition to :standard and :degenerate. Then when passing the dimension to Metric i add a type to hash with dimension type. This would allow to have dimensions defined with standard type or left_join type.

Or maybe a global configuration to use INNER JOIN or LEFT OUTER JOIN is more suitable for this task.

Not sure if the LEFT OUTER JOIN behavior should be the default, that way I would never have missing values.

---

Any thoughts about this matter?

@andresgutgon @niborg

[FAQ] reports_kit vs active_reporting vs datagrid

Hi,

I am now confused with 3 implementation(reports_kit vs active_reporting vs datagrid) Reporting module in Rails.

Please compare and throw some light on merit and demerits of each.

tombenner/reports_kit#5

Thanks.