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)
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)
0
Comments
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.
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