Dimension Hierarchy Extraction to MS SQL Database

[appleglaze28]

I'd like to ask if there's anyway to load the dimension's elements & its related consolidation to a database?

Since I can't figure out if its possible, since using the ASCIIOutput, TextOutput or ODBCOuput allows you to use a cube view which in return puts all the names in 1 column rather than being able to create other columns to allow you to define the heirarchy.

I just think this would be a good way to back up all the cube and its related data in a database including all your dimensions.

[Alan Kirk]

I'd like to ask if there's anyway to load the dimension's elements & its related consolidation to a database?

Since I can't figure out if its possible, since using the ASCIIOutput, TextOutput or ODBCOuput allows you to use a cube view which in return puts all the names in 1 column rather than being able to create other columns to allow you to define the heirarchy.

I just think this would be a good way to back up all the cube and its related data in a database including all your dimensions.

No it wouldn't.

You seem to be a bit fixated on shoving TM1 data into relational databases. If you export both the data and the metadata into a database then (a) you have to design a structure which will store it, and (b) you'll end up with a huge database. Doing (a) for the data is relatively easy. Doing (a) for the metadata is easier if there's only one hierarchy. As soon as you start with multiple hierarchies, you get complications.

The GOOD way to back up the cube and its related data is to back up your data directory and your log files directory regularly using standard, proven backup practices. In that way if anything goes wrong, you should still be able to restore the cubes... WITHOUT needing to convert the cube metadata into a relational structure and then back again.

[appleglaze28]

Is there any documentation on proven practice to back up the application? I can't seem to find any documentation on what is the best practice on backing up the application.

I'm just tryin to checking what items may be accomodated by TI when its comes to relational database, cause I've interacted with some people that would need their budget to be uploaded in Cognos BI for reporting using its entire BI Suite. Since for my perspective TM1's report are better for financial reports than most execs or top level would like to see.

And I think that being able to clean up the data in TM1 would allow faster performance of since you are able to load those data into a datasource and the cube would no longer calculate past data that is used in Cognos BI for reporting.

[Alan Kirk]

Is there any documentation on proven practice to back up the application? I can't seem to find any documentation on what is the best practice on backing up the application.

That's because it's not specific to TM1. It's the same thing that you should be doing with ANY data. The TM1 files are stored in your data directory. You make sure that the data is saved from memory to disk periodically, then you back up those files in the same way as you would if they were a bunch of Word files, .jpgs, .mdbs or what have you.

I'm just tryin to checking what items may be accomodated by TI when its comes to relational database,

TI doesn't have any restrictions. It'll pass any SQL query to a relational database for that relational database to process. TI doesn't do anything with the SQL command aside from perhaps incorporating variables from the data source into it, and passing the resulting final SQL it to the database for processing. The only restriction to what you can output from TI to a database is therefore the TI author's level of understanding of SQL and relational database structures.

[David Usherwood]

I built something recently to push dimension content into SQL, not to use directly there, but to transfer from a 9.4 system to an earlier version. It actually wasn't that tough to build. FYI, the table structure was

Server
Dimension
Element
Type
Parentnumber (int)
Parent
Weight (float)

But that's quite different from Appleglaze28's original question. I agree with Alan - with everything in one directory, backups are not a challenge.

[Alan Kirk]

I built something recently to push dimension content into SQL, not to use directly there, but to transfer from a 9.4 system to an earlier version.

That's a valid way of doing it, but in such a case I'd have thought that it would be just as easy to use the Export Dimension function to pump it out to a .cma; it would be a trivial exercise to write a TI process in the earlier version to reconstruct it from the text file, and then there's no need to mess around with data tables as an intermediary at all .

(Of course in my own case, I naturally have code which generates an .xdi from a dim... what with me still being an .xdi/.xru zealot, and all... )

[David Usherwood]

Indeed. However the table approach gathers all dimension content in one place, and handles multiple servers as well. For the data transfer I looked at routing through tables and decided it was going to be too slow, so I do use a transfer area, but with a single file format, all text, padded out with enough columns to take the largest file. There were some interesting challenges for attribute cubes (I hadn't realised the dtype was in fact AS/AN/AA) but I've just ran it against a 'real' client app and agreed key totals.

[Alan Kirk]

Indeed. However the table approach gathers all dimension content in one place, and handles multiple servers as well.

Ah, good point.

There were some interesting challenges for attribute cubes (I hadn't realised the dtype was in fact AS/AN/AA)

If you're lucky... I recently wrote some code to output a list of all of our dimensions, the attributes thereof grouped by type, and the cubes that they're used in. I forgot one small thing; the AS/AN/AA DType codes weren't introduced until some time after 8.2.12 (might have been 8.3), which meant that they were all being returned as "Unknown" type. I got around it by temporarily loading our model into 9.1 on the Web server... except that when I logged in it crashed the model with an out of memory error, which served as a useful reminder of why I haven't upgraded yet. (I got around THAT by not loading a couple of the cubes and blowing away everything that was in a couple that DID load, then restarting.) In the end I got my list, deleted the model copy from the Web server, and reminded myself not to think about upgrading beyond 9.0 until or unless we move to 64 bit.