"Publish" (export) TM1 cube to SQL server

[jimicron]

Hi all,

I had a (to use Analyst terms) Publish working successfully in Tm1 of a two dimensional cube. We were "publishing" it to a SQL server. With past help here, all was working fine. However, it just stopped and what's totally baffling is that nothing has changed. Sigh. Is Tm1 that finnicky?

The background: I have four "master" cubes that we publish to a SQL server. I set all four of these up at the same time using the same logic, code, etc. Three out of the four stopped working. When I set them up, all four worked. We got one of them to work again by going back and reselecting the view. However for the other two, that isn't working.

Here are screenshots of the cube and process to provide some insight:

And here is the code:

Prolog tab:

Code: Select all

ODBCOpen('SERVER', 'username', 'password');
ODBCOutPut('SERVER', Expand ('truncate table databasename.dbo.tablename));

Data tab:

Code: Select all

ODBCOutPut('SERVER', 
Expand ('INSERT INTO databasename.dbo.tablename
(
Account ,
Status 
)

VALUES

(
''%vAccount%'' ,
''%vStatus%''
) ' )  ) ;

Epilog tab:

Code: Select all

ODBCClose('SERVER');

We can insert into the table using SQL Management Studio with the username, etc. So, we know the table is fine. We also wrote a record to the table and ran the process and it truncated the record so we know the Prolog tab is working. We believe we have it narrowed down to the Data tab. But, without a trace feature, it's tough to know

Again, baffled with why this worked and now just magically has stopped working and why we have two other processes that are just the same (just different cubes) and those work. I have recreated the process and tried a bunch of things, but nothing seems to be working so am reaching out to see if anyone has experience with this.

Thanks a lot!

[tomok]

If you don't get any error messages in the log, meaning the process ran successfully, then the only reason I can think of that the SQL INSERT isn't happening is that the process is not getting any records from the source view. Unfortunately you've left off the most important screen shot which would be the configuration of your view. The screen shot of the view data looks greyed out (the "Active" value) which smells like a rule populated attribute. If that is the case is view zero-suppressed and if so, is the cell fed? Unfed cells would be missing from the data source when the process is run. If they are all unfed then nothing at all would happen on the Data tab.

[jimicron]

Thanks Tomok!! You nailed it!!

I went into all four of my 'master' cubes and added Feeders, which I did not have. They each have columns that are populated via rules and then other columns that are populated from user input. I don't really understand why it worked for awhile and then just stopped working. I would think that it would have never worked. But regardless, the great news is that I am up and running and it's all working!

Thanks a lot for the great bit of information - your nose was dead on in solving this problem!

[lotsaram]

Edit: OP reply beat me to it but for what it's worth seem as I had written it anyway.

What Tomok has said would be my guess also. To test the theory just put an ASCIIOutput on the data tab. If you get no file then there is no data in the view. If you get data in the file but not inserted in the SQL table then there is an issue with the ODBC permissions or connection.

Also it looks like your data source is a static view called publish. You should check the subsets used in the view and make sure no one has inadvertently deleted members resulting in no data. As a "best practice" I would always either create and destroy views and subsets used by TI to ensure they are invisible to end users or at the very least always rebuild subsets to make sure they contain the correct elements to ensure no possibility of accidental alteration by admins (maybe you are already doing this but if you are it isn't clear.)

If Tomok's guess is correct and your view isn't exporting because the rule values aren't fed and why this was working previously and isn't now this could be due to a combination of underfeeding in the current rules and changes during development (such as originally having data in the cube that was then replaced by rule values) that meant the cells were fed originally but unfed after subsequent server restart. This is a common trap for new (and old) developers. Given that you are exporting from a 2D cube sparsity shouldn't be a problem so you could solve the issue either by 1/ make sure rules are fed, 2/ replacing the rules and using TI to populate the cube on demand or 3/ just do away with the SkipCheck in the rule which is fine to do in a 2D cube.

[jimicron]

Thanks a lot for your additional comments 'lotsaram' - appreciate it! That helps to provide some insight on why it once worked and doesn't work any longer.

Also, thanks for the suggestions on best practices!! We are learning those so will be looking into that since you brought it up and implementing! I like to build things that are robust and industrialized to help minimize breakdowns

Thanks again!