mara / mara-schema Goto Github PK
View Code? Open in Web Editor NEWMapping of DWH database tables to business entities, attributes & metrics in Python, with automatic creation of flattened tables
License: MIT License
Mapping of DWH database tables to business entities, attributes & metrics in Python, with automatic creation of flattened tables
License: MIT License
Currently the function data_set_sql_query
does not support other db engines and is mainly raw SQL coded. This should be IMO be changed.
db
parameter using functools.singledispatch
data_set_sql_query
function as deprecatedMetabase allows measure columns (so something with an order, like revenue per hour) to be used in Groupings by binning it. Unfortunately, mara-schema (together with mara-metabase) currently does not allow this, as measure columns are hidden in each sync. To work around this, I can precompute the bins and add this as an Type.ENUM Attribute, but this removes the flexibility metabase gives me to chose my own bins during the analysis as it's converted to a string.
One could currently use Type.ID for this, as, despite the docs saying so, it is not converted to TEXT:
Type.ID: A numeric ID that is converted to text in a flattened table so that it can be filtered
but in the code: cast_to_text=attribute.type == Type.ENUM
My first guess is that the the docs correct and the code incorrect here and this inconsistency should be fixed.
If so, I would like to request a Type.BINNED
or Type.QUANTITY
or so which is not converted to TEXT during SQL generation for metabase.
Any other idea?
(Example: in our case it's for "measures" like "revenue per hour" which I precompute and put into a column: other examples could be "xth order" or simply "number if items in order")
We currentlx want to implement some workaround which manifests as a constant factor in a metric: basically "Overhead = [Composed Metric] * factor". Unfortunately this isn't really possible as the parser cannot handle the constant factor in a formula, so I have to either implement the overhead formula in SQL (meaning I have to reimplement the compose metric :-() or add a column (and add it as simple measure) which represents the factor (bad as we will in the next step replace the constant with some dynamic thingy...).
For now I will do the first, but having the ability to specify a constant factor in a composed metric would be awesome...
Currently the package has several assumptions which are based on a snake case (e.g. my_foreign_table_reference_fk
) naming convention. You might work in a data warehouse where you use a different naming convention e.g. Pascal case (e.g. MyForeignTableReferenceFK
).
With PR #23 I added an option to patch the foreign key naming behavior. But it would be nice to have this as an option in the package itself to define which naming convention you use.
This is ticket is related to #11
We decided on using Uppercase names for entities, so we have attributes like Business Region
. If such an Attribute is added with a Prefix, we get Prefix business Region
because mara_schema.attributes.Attribute.prefixed_name(...)
will lowercase the first char in a name if and all path elements if it is prefixed: https://github.com/mara/mara-schema/blob/master/mara_schema/attribute.py#L54-L55
It would be nice if this could be configured/patchable. I'm currently patching the whole prefixed_name()
function but because this is a method and not a function, the normal @patch()
doesn't work :-(
# Overwrite the lowercasing of entity names after a prefix
# Can't use patch, but simply assigning works :-)
def _prefixed_name(self, path: t.Tuple['EntityLink'] = None) -> str:
"""Generate a meaningful business name by concatenating the prefix of entity link instances and original
name of attribute. """
from mara_schema.attribute import normalize_name
if path:
prefix = ' '.join([entity_link.prefix for entity_link in path])
return normalize_name(prefix + ' ' + self.name)
else:
return normalize_name(self.name)
mara_schema.attribute.Attribute.prefixed_name = _prefixed_name
(BTW: seeing this, if would be nice if there could be a best practise guide for namings, so that this doesn't show up later. Especially because metabase has a bug (metabase/metabase#7923 (comment)) which does not allow for renaming only the casing of an already ingested column, which totally screws up the schema sync in mara-metabase in such cases :-()
in the schema definition the terms 'simple metric' and 'composed metric' are used (e.g. see code file metric.py).
Shouldn't this be renamed:
I think this article gives a good explanation how to distinguish between both terms:
In a data context, measures are the numbers or values that can be summed and/or averaged, such as sales, leads, distances, durations, temperatures, and weight. [...]
A metric is a quantifiable measure that is used to track and assess the status of a specific process. If you’re confused because we haven’t yet covered “measure,” get this: according to the Oxford dictionary, the word measure is derived from the Latin word "metiri." In others words, their meanings are almost identical—which is why you may find them used interchangeably. That said, here is the difference: a measure is a fundamental or unit-specific term—a metric can literally be derived from one or more measures. [...]
The measures and metrics analyzed in this repo about the New York Citi bike trips can be distinguished as following:
incoming trips
— Number of trips whose endpoint is this station.outgoing trips
— Number of trips whose starting point is this station.bikes outbound
— Number of bikes which start their day here and get ridden out.outbound trips
— Number of trips taken by bikes which start their day here.bikes inbound
— Number of bikes which end their day here.inbound trips
— Number of trips taken by bikes which end their day here.all trips
— Number of trips that end or being here (incoming trips
+ outgoing trips
)delta bikes
— Difference in number of bikes between start and end of the day (bikes inbound
- bikes outbound
).delta trips
— Difference in number of trips taken by bikes which start their day here (incoming trips
+outgoing trips
).The folder mara_schema/ui is only installed when usind dev. mode (pip install -e
), not when installing without -e
.
mkdir test
cd test
python3 -m venv .venv
source .venv/bin/activate
pip install wheel
pip install mara-schema
ls -l .venv/lib/python3.7/site-packages/mara_schema/
# returns:
# total 37
# -rwxrwxrwx 1 ice1e0 ice1e0 3105 Oct 22 18:35 attribute.py
# -rwxrwxrwx 1 ice1e0 ice1e0 230 Oct 22 18:35 config.py
# -rwxrwxrwx 1 ice1e0 ice1e0 10616 Oct 22 18:35 data_set.py
# -rwxrwxrwx 1 ice1e0 ice1e0 6800 Oct 22 18:35 entity.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:35 example
# -rwxrwxrwx 1 ice1e0 ice1e0 505 Oct 22 18:35 __init__.py
# -rwxrwxrwx 1 ice1e0 ice1e0 3908 Oct 22 18:35 metric.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:35 __pycache__
# -rwxrwxrwx 1 ice1e0 ice1e0 8119 Oct 22 18:35 sql_generation.py
It does not work with
[..]
pip install git+https://github.com/mara/mara-schema.git#egg=mara-schema
ls -l .venv/lib/python3.7/site-packages/mara_schema/
# total 41
# -rwxrwxrwx 1 ice1e0 ice1e0 3192 Oct 22 18:46 attribute.py
# -rwxrwxrwx 1 ice1e0 ice1e0 238 Oct 22 18:46 config.py
# -rwxrwxrwx 1 ice1e0 ice1e0 10840 Oct 22 18:46 data_set.py
# -rwxrwxrwx 1 ice1e0 ice1e0 6937 Oct 22 18:46 entity.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:46 example
# -rwxrwxrwx 1 ice1e0 ice1e0 536 Oct 22 18:46 __init__.py
# -rwxrwxrwx 1 ice1e0 ice1e0 4004 Oct 22 18:46 metric.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:46 __pycache__
# -rwxrwxrwx 1 ice1e0 ice1e0 8283 Oct 22 18:46 sql_generation.py
either, only with
[..]
pip install -e git+https://github.com/mara/mara-schema.git#egg=mara-schema
ls -l .venv/src/mara-schema/mara_schema/
# returns:
# total 41
# -rwxrwxrwx 1 ice1e0 ice1e0 3192 Oct 22 18:42 attribute.py
# -rwxrwxrwx 1 ice1e0 ice1e0 238 Oct 22 18:42 config.py
# -rwxrwxrwx 1 ice1e0 ice1e0 10840 Oct 22 18:42 data_set.py
# -rwxrwxrwx 1 ice1e0 ice1e0 6937 Oct 22 18:42 entity.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:42 example
# -rwxrwxrwx 1 ice1e0 ice1e0 536 Oct 22 18:42 __init__.py
# -rwxrwxrwx 1 ice1e0 ice1e0 4004 Oct 22 18:42 metric.py
# -rwxrwxrwx 1 ice1e0 ice1e0 8283 Oct 22 18:42 sql_generation.py
# drwxrwxrwx 1 ice1e0 ice1e0 512 Oct 22 18:42 ui
I use several times composite business keys in addition to a surrogate keys. Sample dimensional table:
CREATE TABLE vendor
(
id INTEGER IDENTITY(1,1) NOT NULL,
company_num VARCHAR(5) NOT NULL,
account_num VARCHAR(20) NOT NULL,
name TEXT,
street TEXT,
PRIMARY KEY (id),
UNIQUE (company_num, account_num)
)
My fact table often only includes the business key but not the surrogate key:
CREATE TABLE purchase_transaction
(
company_num VARCHAR(5) NOT NULL,
vendor_order_account_num VARCHAR(20) NOT NULL,
vendor_invoice_account_num VARCHAR(20) NOT NULL,
invoice_date DATE,
product_num VARCHAR(20),
net_line_amount_acy DECIMAL(32,5),
net_line_discount_acy DECIMAL(32,5)
)
The mara_schema
module currently only supports relationships where the foreign key is related to the primary key (which is unique). So I have to manually add a vendor_order_account_fk
to purchase_transaction
and use an UPDATE
SQL statement to set the foreign key based on the business key connection.
It would be great when mara_schema would support the following use cases:
Required changes:
bk_column_names
in Entity.__init__
), `Entity.Entity.link_entity
method would need a new parameter bk_columns
.mara_schema.sql_generation.data_set_sql_query
would need to use the business key in the LEFT JOIN
when the primary key is not defined.Required changes:
UPDATE purchase_transaction
SET purchase_transaction.vendor_order_account_fk = vendor.id
FROM purchase_transaction
INNER JOIN vendor ON
vendor.company_num = purchase_transaction.company_num AND
vendor.account_num = purchase_transaction.vendor_order_account_num
WHERE purchase_transaction.vendor_order_account_fk IS NULL
ALTER TABLE purchase_transaction ADD COLUMN IF NOT EXISTS vendor_order_account_fk INTEGER;
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.