olap-cube-js

    0.16.1 • Public • Published

    Build Status codecov Version npm

    OLAP cube.js

    The simplest data analysis tools written in javascript. This solution is a means for extracting and replenishing data, which together with your data storage means and a means of providing aggregate data, is intended for decision making.

    Table of Contents

    Features:

    • Multidimensional conceptual data representation
    • Tree structure for representing hierarchical data
    • Balanced hierarchies
    • Multi-level hierarchies
    • Each cube dimension contains one hierarchies
    • Dynamic fact table
    • OLAP data is typically stored in a snowflake schema
    • Surrogate key is internally generated as unique identifier for dimension member (used composite dimension keys)
    • The ability to edit data
    • Filling - solution for Typical Multidimensional Problem: missing values
    • Multiple hierarchies (each cube dimension can contains more then one hierarchies, dimension with both fiscal and calendar years is one classic example)

    Getting Started

    Prerequisites

    For install the software you need a package manager - npm which is installed with Node.js

    Installing

    Then in the console run the following command

    npm install olap-cube-js

    How Cube is work?

    Structure

     
    // This is an array of data from server
    let facts = [
        { id: 1, region: 'North', year: 2017, month: 'January', product: 'Product 1', category: 'Category 1', value: 737 },
        { id: 2, region: 'South', year: 2017, month: 'April', product: 'Product 2', category: 'Category 1', value: 155 },
        { id: 3, region: 'West',  year: 2018, month: 'April', product: 'Product 3', category: 'Category 2', value: 112 },
        { id: 4, region: 'West',  year: 2018, month: 'April', product: 'Product 1', category: 'Category 2', value: 319 },
    ]
     
    // This is the data schema we need to obtain
    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'regions',
                keyProps: ['region'],
            }
        },
        {
            dimensionTable: {
                dimension: 'date',
                keyProps: ['year', 'month']
            }
        },  
        {
            dimensionTable: {
                dimension: 'products',
                keyProps: ['product'],
            },
            level: [
                {
                    dimensionTable: {
                        dimension: 'categories',
                        keyProps: ['category']
                    }
                }
            ]
        }
    ];
     
    // We send it all to the constructor
    let cube = new Cube({dimensionHierarchies});
    cube.addFacts(facts);
     

    Now the cube will represent the structure below:

    let structure = {
        dimensionHierarchies: [
            {
                dimensionTable: {
                    dimension: 'regions',
                    keyProps: ['region'],
                    members: [
                        { id: 1, region: 'North' },
                        { id: 2, region: 'South' },
                        { id: 3, region: 'West' }
                    ],
                    otherProps: []
                },
                level: []
            },
            {
                dimensionTable: {
                    dimension: 'date',
                    keyProps: ['year', 'month'],
                    members: [
                        { id: 1, year: 2017, month: 'January' },
                        { id: 2, year: 2017, month: 'April' },
                        { id: 3, year: 2018, month: 'April' }
                    ],
                    otherProps: []
                },
                level: []
            },
            {
                dimensionTable: {
                    dimension: 'products',
                    keyProps: ['product'],
                    members: [
                        { id: 1, product: 'Product 1', categories_id: 1 },
                        { id: 2, product: 'Product 2', categories_id: 1 },
                        { id: 3, product: 'Product 3', categories_id: 2 },
                        { id: 4, product: 'Product 1', categories_id: 2 },
                    ],
                    otherProps: []
                },
                level: [
                    {
                        dimensionTable: {
                            dimension: 'categories',
                            keyProps: ['category'],
                            members: [
                                { id: 1, category: 'Category 1' },
                                { id: 2, category: 'Category 2' },
                            ],
                            otherProps: []
                        },
                        level: []
                    }
                ]
            }
        ],
        cellTable: [
            { id: 1, regions_id: 1, date_id: 1, products_id: 1, value: 737 },
            { id: 2, regions_id: 2, date_id: 2, products_id: 2, value: 155 },
            { id: 3, regions_id: 3, date_id: 3, products_id: 3, value: 112 },
            { id: 4, regions_id: 3, date_id: 3, products_id: 4, value: 319 },
        ]
    };

    Sets

    A set is a collection of distinct objects. Set provides a specialized syntax for getting and manipulating the multidimensional data stored in OLAP cubes. Access to the elements of the OLAP-cube can be carried out several types of sets

    Types of sets: Set, Subset, Emptyset, Multiset

    Set, that type determines one element:
    w : ( x , y , z ) → wxyz ,

    Subset, that type determines several elements:
    W : ( x , y ) → W = { wz1 , wz2 , … , wzn } ,

    Emptyset, that type determines all elements:
    W : () → W = { wx1 y1 z1 , wx1 y1 z2 , … , wxn yn zn } ,

    Emptyset, that type determines union of elements:
    W : ({ z1 , z2 }) → W = { Wx1 y1 , Wxn yn } = { wx1 y1 , wxn yn }z1 ∪ { wx1 y1 , wxn yn }z2 .
    Now using different types of sets, you can access the elements of the cube

    API

    Access to measures of the cells

    Access to measures is possible through access to cube cells

    Set

    Define the set with maximum cardinality. For this fixate all dimensions of the first level of the hierarchy.

    Example:

    let set = { regions: { id: 1 }, date: { id: 1 }, products: { id: 1 } }

    execute:

    cube.dice(set).getCells()

    return:

    [
        { id: 1, value: 737, regions_id: 1, date_id: 1, products_id: 1 }
    ]

    execute:

    cube.dice(set).getFacts()

    return:

    [
        { id: 1, region: 'North', year: 2017, month: 'January', product: 'Product 1', category: 'Category 1', value: 737 }
    ]
    Subset

    Fixate some of the dimensions:

    let subSet = { regions: { id: 3 } }

    execute:

    cube.dice(subSet).getCells()

    return:

    [
        { id: 3, value: 112, regions_id: 3, date_id: 3, products_id: 3 },
        { id: 4, value: 319, regions_id: 3, date_id: 3, products_id: 4 },
    ]

    execute:

    cube.dice(subSet).getFacts()

    return:

    [
        { id: 3, region: 'West',  year: 2018, month: 'April', product: 'Product 3', category: 'Category 2', value: 112 },
        { id: 4, region: 'West',  year: 2018, month: 'April', product: 'Product 1', category: 'Category 2', value: 319 },
    ]
    Emptyset

    This way you can take all the facts from the cube back:

    let emptySet = {}

    execute:

    cube.dice(emptySet).getCells()
    // or little shorter
    cube.getCells()

    return:

    [
        { id: 1, value: 737, regions_id: 1, date_id: 1, products_id: 1 },
        { id: 2, value: 155, regions_id: 2, date_id: 2, products_id: 2 },
        { id: 3, value: 112, regions_id: 3, date_id: 3, products_id: 3 },
        { id: 4, value: 319, regions_id: 3, date_id: 3, products_id: 4 },
    ]

    execute:

    cube.dice(emptySet).getFacts()
    // or little shorter
    cube.getFacts()

    return:

    [
        { id: 1, region: 'North', year: 2017, month: 'January', product: 'Product 1', category: 'Category 1', value: 737 },
        { id: 2, region: 'South', year: 2017, month: 'April', product: 'Product 2', category: 'Category 1', value: 155 },
        { id: 3, region: 'West',  year: 2018, month: 'April', product: 'Product 3', category: 'Category 2', value: 112 },
        { id: 4, region: 'West',  year: 2018, month: 'April', product: 'Product 1', category: 'Category 2', value: 319 },
    ]
    Multiset

    Fixate a plurality of dimension values:

    let multiSet = { regions: [ { id: 1 }, { id: 2 } ] }

    execute:

    cube.dice(multiSet).getCells()

    return:

    [
        { id: 1, value: 737, regions_id: 1, date_id: 1, products_id: 1 },
        { id: 2, value: 155, regions_id: 2, date_id: 2, products_id: 2 },
    ]

    execute:

    cube.dice(multiSet).getFacts()

    return:

    [
        { id: 1, region: 'North', year: 2017, month: 'January', product: 'Product 1', category: 'Category 1', value: 737 },
        { id: 2, region: 'South', year: 2017, month: 'April',   product: 'Product 2', category: 'Category 1', value: 155 },
    ]

    Access to members of the dimensions

    Emptyset

    Simple call return all members of the dimension:

    cube.dice({}).getDimensionMembers('products')
    // or little shorter
    cube.getDimensionMembers('products')

    return:

    [
        { id: 1, product: 'Product 1', categories_id: 1 },
        { id: 2, product: 'Product 2', categories_id: 1 },
        { id: 3, product: 'Product 3', categories_id: 2 },
        { id: 4, product: 'Product 1', categories_id: 2 },
    ]
    SubSet

    Queries with the second argument return some members of the dimension in accordance with the passed set

    cube.dice({ categories: { id: 1 } }).getDimensionMembers('products')

    return:

    [
        { id: 1, product: 'Product 1', categories_id: 1 },
        { id: 2, product: 'Product 2', categories_id: 1 },
    ]

    Other example:

    cube.dice({ categories: { id: 1 } }).getDimensionMembers('regions')

    return:

    [
        { id: 1, region: 'North' },
        { id: 2, region: 'South' },
    ]
    Multiset
    cube.dice({ regions: [{ id: 2 }, { id: 3 }] }).getDimensionMembers('products')

    return:

    [
        { id: 2, product: 'Product 2', categories_id: 1 },
        { id: 3, product: 'Product 3', categories_id: 2 },
        { id: 4, product: 'Product 1', categories_id: 2 },
    ]

    Editing dimension members

    let regions = cube.getDimensionMembers('regions');
    // if regions = [{id: 1, region: 'South'}, {id: 2, region: 'West'}]
    let member = regions[0]
    member['region'] = 'East'; 

    The cube and its subcubes have common links to dimension members. It is made possible to edit data in dimension members between operations.

    const subCube = cube.dice({ product: { id: 1 } }) // some operation
    const memberFromCube = cube.getDimensionMembers('regions').find(({id}) => id === 1)
    const memberFromSubcube = subCube.getDimensionMembers('regions').find(({id}) => id === 1)
    memberFromCube === memberFromSubcube // true
    

    Adding dimension members

    let member = { product: 'Product 3' }
    cube.addDimensionMember('products', member)

    Removing dimension members

    let member = { id: 2 }
    cube.removeDimensionMember('products', member)

    Adding facts

    let facts = [
        { id: 3, region: 'South', product: 'Product 3', value: 30 }
    ]
    cube.addFacts(facts)

    Removing facts

    let facts = [
        { id: 3, region: 'South', product: 'Product 3', value: 30 }
    ]
    cube.removeFacts(facts)

    Added dimension hierarchy

    let facts = [
        { id: 1, product: 'TV', mark: 'Sony', country: 'China', count: 2 },
        { id: 1, product: 'TV', mark: 'Samsung', country: 'Niderland', count: 3 }
    ];
    let cube = new Cube();
    cube.addFacts(facts);
    cube.addDimensionHierarchy({
        dimensionTable: {
            dimension: 'product',
            keyProps: ['product']
        },
        level: [
            {
                dimensionTable: {
                    dimension: 'mark',
                    keyProps: ['mark']
                },
            }
        ]
    })
    console.log(cube.getCells())

    return:

    [
        { id: 1, product_id: 1, country: 'China', count: 2 },
        { id: 1, product_id: 2, country: 'Niderland', count: 3 }
    ]

    Removing dimension hierarchy

    Returns the result back to the addition of the hierarchy

    cube.removeDimensionHierarchy(cube.dimensionHierarchies[0])

    Multiple hierarchies

    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'products',
                keyProps: ['product']
            },
            level: [
                {
                    dimensionTable: {
                        dimension: 'discounts',
                        keyProps: ['discount']
                    }
                },
                {
                    dimensionTable: {
                        dimension: 'categories',
                        keyProps: ['category']
                    }
                }
            ]
        }
    ]
    let facts = [
        {id: 1, product: 'TV', discount: 5, category: 'electronics'},
        {id: 2, product: 'milk', discount: 10, category: 'food'},
    ]
    let cube = new Cube({dimensionHierarchies});
    cube.addFacts(facts);
    cube.getDimensionMembers('product')

    return:

    [
        {id: 1, product: 'TV', discounts_id: 1, categories_id: 1},
        {id: 2, product: 'milk', discounts_id: 2, categories_id: 2},
    ]

    or:

    cube.getCells()

    return:

    [
        {id: 1, products_id: 1},
        {id: 2, products_id: 2},
    ]

    Filling empty cells

    Fills the fact table with all possible missing combinations. For example, for a table, such data will represent empty cells

    let dimensionHierarchies = [
         {
             dimension: 'regions',
             keyProps: ['region']
         },{
             dimension: 'products',
             keyProps: ['product']
         }
     ];
     
    let facts = [
        { id: 1, region: 'North', product: 'Product 1', value: 10 },
        { id: 2, region: 'South', product: 'Product 2', value: 20 }
    ];
    let cube = new Cube({dimensionHierarchies});
    cube.addFacts(facts);
     

    Execute filling:

    let defaultMeasures = { value: 0 }; // properties for empty cells
    cube.fillEmptyCells(defaultMeasures);

    Now get the facts back:

    let factsFilled = cube.getFacts()

    factsFilled will be:

    [
        { id: 1, region: 'North', product: 'Product 1', value: 10 },
        { id: 2, region: 'South', product: 'Product 2', value: 20 },
        { region: 'North', product: 'Product 2', value: 0 },
        { region: 'South', product: 'Product 1', value: 0 }
    ]

    Removing empty cells

    You can remove all or some of the empty cells. At the same time, some of the dimension members can be removed too if no more cells found correspond to the dimension member

    cube.removeCells(cube.getEmptyCells())
    // or
    cube.removeCells(cube.getEmptyCells().filter(({ value }) => !value))
     
    // for flexibility use other api methods `createEmptyCells`, `isEmptyCell` and `addEmptyCells`

    Drill-up members

    let markMembers = cube.dice({'product': productMembers}).getDimensionMembers('mark')

    Drill-down members

    let productMembers = cube.dice({'mark': markMembers}).getDimensionMembers('product')

    Slice

    let member = cube.getDimensionMembers('mark')[0]
    let subCube = cube.slice(member)

    Dice

    let markMember = cube.getDimensionMembers('mark')[0]
    let regionMember = cube.getDimensionMembers('region')[0]
    let subCube = cube.dice({ mark: markMember, region: regionMember })

    Additional member props

    It may be that the dimension member may content additional properties from the fact table that do not participate in creating own surrogate key, for this use the property otherProps

    let facts = [{ id: 1, nikname: 'Monkey', name: 'Albert', surname: 'Einstein', countryBirth: 'Germany' }]
    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'country',
                keyProps: ['countryBirth'],
            }
        },
        {
            dimensionTable: {
                dimension: 'user',
                keyProps: ['nikname'],
                otherProps: ['name', 'surname']
            }
        }
    ]
    let cube = new Cube({dimensionHierarchies})
    cube.addFacts(facts);
    let members = cube.getDimensionMembers('user')

    return:

    [
        { id: 1, nikname: 'Monkey', name: 'Albert', surname: 'Einstein' }
    ]

    Custom members

    let facts = [{ id: 1, nikname: 'Monkey', group: 'Administrators' }];
    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'user',
                keyProps: ['nikname'],
                foreignKey: 'USER_ID'
            },
            level: [
                {
                    dimensionTable: {
                        dimension: 'group',
                        keyProps: ['group'],
                        primaryKey: 'ID',
                        foreignKey: 'GROUP_ID'
                    }
                }
            ]
        }
    ];
    let cube = new Cube({dimensionHierarchies});
    cube.addFacts(facts);
     

    execute:

    let userMember = cube.getDimensionMembers('user')[0] 

    return:

    { id: 1, nikname: 'Monkey', GROUP_ID: 1 }

    execute:

    let groupMember = cube.getDimensionMembers('group')[0];

    return:

    { ID: 1, group: 'Administrators' }

    execute:

    let cell = cube.getCells()[0];

    return:

    { id: 1, USER_ID: 1 }

    Default Member Options

    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'user',
                keyProps: ['nikname'],
                defaultMemberOptions: {
                    nikname: 'anonymous'
                }
            }
        }
    ];
    let cube = new Cube({dimensionHierarchies})
    cube.addDimensionMember('user')

    Custom facts

    Like custom members, some times need make custom facts

    let facts = [{ saleId: 1, saleCount: 1 }];
    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'saleCount',
                keyProps: ['saleCount']
            }
        }
    ];
    let cube = new Cube({dimensionHierarchies, factPrimaryKey: 'saleId'})
    cube.addFacts(facts);
     

    Default Fact Options

    let facts = [
        { id: 1, x: 1, y: 1, isOpen: true },
        { id: 1, x: 2, y: 2, isOpen: true },
    ];
    let dimensionHierarchies = [
        {
            dimensionTable: {
                dimension: 'x',
                keyProps: ['x']
            }
        },
        {
            dimensionTable: {
                dimension: 'y',
                keyProps: ['y']
            }
        }
    ];
    let cube = new Cube({dimensionHierarchies, defaultFactOptions: { isOpen: false }});
    cube.addFacts(facts);
    cube.fillEmptyCells();
    cube.addDimensionMember('x', { x: 3 })

    Versioning

    We use SemVer for versioning. Until olap-cube-js reaches a 1.0 release, breaking changes will be released with a new minor version. For example 0.10.0, and 0.10.1 will have the same API, but 0.11.0 will have breaking changes. The project is on stage of developing API.

    Explanation of Build Files

    UMD ES Module
    dev cube.js cube.esm.js
    prod cube.min.js cube.esm.min.js

    Todo

    In future versions:

    Perhaps

    • Add unbalanced, ragged hierarchies
    • Add calculated members
    • Add MDX query language
    • Add Speed tests

    Motivation

    As a frontend developer, I am not very familiar with the subject area of cubic data, but in my work I was faced with cubic data of a hierarchical type, so I wanted to make a small tool to solve my problems. The task turned out to be difficult, but interesting in terms of creating a public API, the results of which are presented above.

    Install

    npm i olap-cube-js

    DownloadsWeekly Downloads

    240

    Version

    0.16.1

    License

    ISC

    Unpacked Size

    1.51 MB

    Total Files

    146

    Last publish

    Collaborators

    • feonit