Having Trouble with Dynamic Cubes and SQL?

I’m going to be honest. I love Transformer and PowerPlay cubes. There, I said it. I admit it. Since I have a love for the “older” technology, I was REALLY excited to hear that Dynamic Cubes and Cognos Cube Designer were part of the 10.2 BI release!  However are you having trouble with Dynamic Cubes and SQL?

Dynamic Cubes

For those of you who are not familiar with Dynamic Cubes, it is really Transformer on steroids for big data, as it allows for analyzing terabytes of data quickly and accurately.

Before you can start using this new technology, there are a few things you need to make sure are set up:
1. Your data warehouse must be in a star or snowflake schema format
2. Your data source configuration in Cognos Administration must be set up with JDBC connection
3. Most likely, you will need to download the JDBC drivers from Microsoft’s download area of their website
4. You will need to install them in a couple of places; see this link for exact direction: http://www-01.ibm.com/support/docview.wss?uid=swg21584451
5. Lastly, you will need to make sure you have 32-bit SQL installed, as all connections made through client are 32bit only

Once all of these things are complete, you should be able to launch Cognos Cube Designer and load up the sample model or connect to your own data source and be off and running with Dynamic Cubes.

My next blog will discuss deploying and reporting from Dynamic Cubes.

Stored Procedures in TM1 Turbo Integrator

​Learn How to use Stored Procedures in TM1 Turbo integrator

As many of you know, when you are using stored procedures in TM1 Turbo Integrator to import data and metadata into TM1, you can bring it in from many sources. You can use comma-delimited ASCII files, other TM1 cubes and views, MSAS (Microsoft Analysis Service) and relational database tables that are accessible through an ODBC connection. For this blog, we are going to concentrate on relation database tables through SQL and what you may not know is that using multiple tables and writing multiple joins query in SQL using the Turbo Integrator query box can make the pull of the data very slow!

An easy way to correct this is to write your join statements in SQL using SQL Server Management Studio. Write your joins to create a “summary table” of all the fields you want pulled into TM1. Now create a stored procedure that uses the joins, creates your new table and subsequently updates the summary table based on the schedule you have set.

So, instead of using the long join statement in the Turbo Integrator query box, simply write a statement to execute a stored procedure in the query box. BAM! Your query is much quicker and returns your data as you would expect!

Stored Procedures in TM1 Turbo integrator

Did you like this blog on Stored Procedures in TM1 Turbo integrator, if so, then check out this blog on how to assign TM1 Element Security Within a Dimension by clicking here.

If you need help setting this up our excellent services is here to help!  Reach out to us at services@lodestarsolutions.com today!  Also, if you have not joined our community, go to lodestarsolutions.com and join now!  Don't miss out on the latest programs, events and educational blog like this one!  It is easy to join and takes less than 1 minute!