i've already built a little BI app that pulls in data from Mindbody (POS), Deputy (timesheets) and Xero (Accounts).. it provides my business some dashboards and insights into how thigns are running.. It's starting to slow down with all the number crunching going on.. i've pretty much written a python reporting module on the app.. I had no idea about data cubes and think it could greatly simplify the app
its python backend w/ angular frontend.. im thinking angular could just query for the data directly using OLAP..
it's pretty much just a sql database with a bunch of tables
Cubes is very lightweight and simpler to configure and use (though less expressive for some queries). You mostly need to have a data warehouse database and configure your cubes model. I recommend CubesViewer to explore your cubes (at least during development) -disclaimer: I'm the author-.
Cubes allow for a particular kind of queries. A full OLAP database is less flexible than a OLTP(SQL) database. Cubes, being simpler, is yet a bit less expressive (may be limited for complex queries), but it has served me well (most of the shortcomings can be worked around with some data transformation).
data warehousing database: although cubes can pretty much map any database schema as a "dimensional" model (required for OLAP), there are some usual database strategies.
The normal pipeline is:
1: Data source(s) -> 2: Data transformation (ETL) -> 3: Data warehouse database
The data warehouse database (aka "data mart") can be anything, but in many cases it is a relational database with a particular schema (Star Schema / Snowflake Schema). This is called Relational Backed OLAP (ROLAP). A nice thing of Cubes is that it allows you to map any database schema, not forcing you to do any transformation for many cases. So it is an option for you to simply map your existing database.
I personally always run a data transformation process (simple python scripts) and load a database instance, which then I use from Cubes.
I recommend not to mix them, and completely ignore OLAP when designing your transactional database. Design your database as usual (normal forms, denormalization according to your intuition/experience, etc...).
Then extract data for analysis and load a separate instance which you use for OLAP. In these kind of processes you have the opportunity to merge other information from other systems (ie country population, data from other tools, etc).
If it's an integration of Business Intelligence, then gather all your data, write your import processes which you already probably have. Using a Star Schema/Snowflake schema shall be appropriate. Model thinking of dimensions and facts. Define your model and you shall be ready to go.
I have an experimental and mostly undocumented ETL tool (able to create and load star/snowflake schemas) that I wouldn't recommend for production purposes, but in case you feel like contributing to an OSS project and you are interested... https://github.com/jjmontesl/cubetl