publish subset & view in Excel VBA script

[Carolyn]

I am writing a VBA script that creates a subset, then uses the subset in a view.

The view is used in a viewzeroout function in TI.

Finally, I want to kill the subset and the views.

I have been successful at writing code to create the subset and the views, but they are private, and I need to make them public. In a related problem, I have a subset that will not delete. It claims to be part of a view, but I cannot find the view. Is there any way to list views associated with a given subset? I was working with only a few cubes, and this subset is not apparently in any of the views in any of the cubes.

I need:

1. VBA code to make private subsets and views public
2. VBA code to kill the subset and views after the cube has been selectively zeroed.
3. A way to figure out why the subset cannot be deleted, by tracing down its associated view so I can kill the view and, then, kill the subset.

TIA. I have found many, many answers to other questions by lurking on the message board. These have me stumped.

[Alan Kirk]

I am writing a VBA script that creates a subset, then uses the subset in a view.

The view is used in a viewzeroout function in TI.

Finally, I want to kill the subset and the views.

I have been successful at writing code to create the subset and the views, but they are private, and I need to make them public. In a related problem, I have a subset that will not delete. It claims to be part of a view, but I cannot find the view. Is there any way to list views associated with a given subset? I was working with only a few cubes, and this subset is not apparently in any of the views in any of the cubes.

I need:

1. VBA code to make private subsets and views public
2. VBA code to kill the subset and views after the cube has been selectively zeroed.
3. A way to figure out why the subset cannot be deleted, by tracing down its associated view so I can kill the view and, then, kill the subset.

TIA. I have found many, many answers to other questions by lurking on the message board. These have me stumped.

By "VBA" I take it that you mean Application.Run calls of the various macro and workbook functions rather than the API?

I really wouldn't do that. You'd be far better off doing almost all of it in TI on the server side.

If the problem is that you need to have the elements for the subset specified in Excel then ideally pass the element name(s) as parameters to the TI process via an Action Button. If you absolutely must, create the subset via the SubDefine macro function (and to answer one of your questions, you publish that via the PublishSubset macro function but you need Admin access to do that), but do everything else on the server. (And if the user doesn't have admin rights, you'd need to do the subset creation in TI, which runs with admin rights, anyway.)

Also, I'm aware of standard code which will always create subsets which are used by TI with the same name. This is a practice that I'm vehemently opposed to because when you do get the bogus "subset is in use" message it cripples the whole chore. (I've seen that before; often it will occur if you poke into the cube to check it out while you're debugging. Your session somehow gets a "lock" on the subset which is cleared when you completely shut down your connection. But that's not the ONLY time it happens.) I always give subsets unique names for each execution so that the worst that can happen is that you end up with some orphaned subsets which you can clean up later. (In my case I do it by using the execution number in my chore control cube.)

The more that you can do in TI rather than VBA, the fewer problems you'll have.

[Malcolm MacDonnell]

Agree with all Alan's comments. Another way I've found to clean up the bugus "Subset in use" is to right click on the cube and select "Unload Cube". Usually this means you don't have to restart the whole database, however in one previous version of TM1 (9.0 I think, but I'm not sure) "Unload Cube" would often crash the database (resolving your issue on the way of course)

[Carolyn]

Gentlemen, Thanks.

Yes, I mean Visual Basic for Applications. And, yes, I was trying it with Application.Run.

I will have a list of elements provided to me, in Excel, by a user, then will want to create a subset with that list. The only person executing this procedure will be me, with admin privledges.

I will investigate doing it with TI programming. I have already done a lot of TI programming and could not figure out how to translate that Excel list into TI.

Back to the drawing board. I have learned a lot from this message board, already. Most of the time I find the answers just by lurking.

[Alan Kirk]

Gentlemen, Thanks.

Yes, I mean Visual Basic for Applications.

It's just that you can code against the VB API using VBA as well. Not that I'm encouraging that, just mentioning that it's useful to know which approach is being taken.

And, yes, I was trying it with Application.Run.

I will have a list of elements provided to me, in Excel, by a user, then will want to create a subset with that list. The only person executing this procedure will be me, with admin privledges.

I will investigate doing it with TI programming. I have already done a lot of TI programming and could not figure out how to translate that Excel list into TI.

If you have only a few elements, then parameters are the best way to go. If you have a LOT of elements, you may be better off exporting it into a text file from Excel, using that text file as the data source for a TI process, then creating the subset by reading the elements from the file in the TI.

There are usually multiple solutions to any given problem, and the one that will be best will often vary with the circumstances.

[Carolyn]

If you have only a few elements, then parameters are the best way to go.

Right now, it's about 50, which should be typical. It could be as many as 75 or so. What would you suggest as a "tipping point" beyond which the text file would be more efficient, just for future reference?

Can I indicate an A2:A50 range in the parameters to define the subset? I was going to try that, but thought I would check this message board before going forward.

I am converting a Version 6 database into Version 9. I've made good progress, but this one had me stumped. I dove into TI thanks to your suggestions earlier.

Later: Wow it worked! I created the ASCII text file and used that as the data source for the subset.