Pros & Cons using one or several databases


  • Pros & Cons using one or several databases

    Hi Jedox Users,


    What pros or cons are there using several databases in Jedox?

    At the moment we have one Jedox connection with one database.
    But as we have many dimensions and cubes it gets more and more difficult to use the "right" dimensions in cubes, e.g. we have 5 different "commodity" dimensions alone for sales, production and quality department and in every dimensions another hierarchy (in some as well parallel hierarchies). So I thought about creating one database for each department.

    I know I´d have to create some dimensions in each database, e.g. time and version but would have an better overview.
    Would variables for example for time work regardless of the database?

    Do you have any input for me regarding my question?

    Thanks in advance! :thumbsup:
  • Hi,

    For us the main reasons to maintain several databases are:

    1. User rights management (it's far easier to manage users per database then per cube / dimension)
    2. Release cycles (often projects have different release cycles which means you can not deploy one project as long another one is used in production that is why we split them up)

    It's always a tradeoff between maintainability and sync. Keeping several databases in sync is also something that is not as easy as in theory - specifically when different departments are involved. You can decide for a master database/dimension but that limits the options of the people in the different projects.
  • Hi SaraLM,

    I know it's quite the late answer, but I thougt I'd put my 2 cents in.
    we also use for each department at least on database. Basically as soon as there would be too much mixing, or a dimension would be named the same but a parallel hierarchy is not a proper solution we create a new database. The only con that I see is the maintenance of the dimensions that vary a lot between the databases. But that can be solved with proper etl-jobs, (if existing) where everything is based on dwh-data.
    We have 13 databases. And for dimensions that are always the same or at least very similar we created a database "masterdata" where we only have to maintain the dimensions at one place and from there it is distributed via jobs to the dimensions of the other databases (e.g. Calendar, Version, KPIs, SAP-Hierarchies etc.)