Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Creating cubes (or equvelent) from Vertica

Hi,
Recently our database is moved to Vertica. 
We were creating data cubes using ‘SQL Server Business Intelligence Development Studio’ (MS Visual Studio) for last 10 years. There are about 40 different end users are using these data cubes.
Unfortunately, since we made the move to Vertica, it takes over 6 hours to create these data cubes.
Does anyone have any ideas to deliver the same data requirements in a better way? 
Cheers,
San
(Data Modeller)

Comments

  • So, to answer your question in three simple steps:
    1) You want to create your master data/tables in one schema which can be used to join with several other fact tables. 
    2) Have all your metrics/fact tables in one schema.
    3) Optimize Vertica for joins, group bys, better encoding/compression etc... To begin with, DBD would be a great place to tune projections for all your 40 different user queries.
  • Hi San,

    Is it the refresh which is taking time for Cubes to build.

    If yes, then you should be trying to look into what queries are sent to Vertica from Cubes while refreshing. Usually the queries are distinct type queries, then you can tune the schema further for these queries.

    It should not take 6 hours to refresh the cube with Vertica.

    Hope this helps
    NC


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.