Changing the Planning Repository without Restarting Planning

One of the long-running tenets of working with the Planning repository is that you must restart Planning to see your changes.  I’ve always heard that there were ways around this, but Oracle hasn’t ever been forthcoming with the specifics of how to make that happen.  Finally, at Kscope16 during my presentation on the Planning Repository, someone in the audience by the name of Tjien Lie had the code from Oracle to make this happen.  Before I get to that, let’s start with a primer on the HSP_ACTION table.  I would provide one, but John Goodwin did such a great job, so I’ll just point you to his post here.

<<<wait for the reader to go to John Goodwin’s site and read the information and come back, assuming they don’t get distracted by his wealth of amazing content>>>

Ok…now that you understand what the HSP_ACTION table does, how do we use it differently than John uses it?  By differently, I mean I don’t wan to insert specific rows and update specific things.  That seems like a lot of work.  Instead, why not just have the HSP_ACTION table update the entire cache for us?  Let’s give it a shot.  First I’m going to go add a dimension to my Vision application:

Now let’s make sure the dimension shows up:

Any now let’s delete it:

DELETE FROM HSP_MRU_MEMBERS WHERE DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')

DELETE 
FROM 
	HSP_UNIQUE_NAMES 
WHERE 
	OBJECT_NAME IN (
SELECT
	OBJECT_NAME
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted'))

SELECT
	OBJECT_ID INTO #DeleteChildren
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')	
	AND o.HAS_CHILDREN = 0

SELECT
	OBJECT_ID INTO #DeleteParents
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')	
	AND o.HAS_CHILDREN = 1

DELETE
FROM
	HSP_MEMBER
WHERE
	MEMBER_ID IN (SELECT OBJECT_ID FROM #DeleteChildren)

DELETE
FROM
	HSP_MEMBER
WHERE
	MEMBER_ID IN (SELECT OBJECT_ID FROM #DeleteParents)

DELETE
	d
FROM
	HSP_DIMENSION d
INNER JOIN
	HSP_OBJECT o ON d.DIM_ID = o.OBJECT_ID
WHERE
	o.OBJECT_NAME = 'ToBeDeleted'

DELETE
FROM
	HSP_OBJECT
WHERE
	OBJECT_ID IN (SELECT OBJECT_ID FROM #DeleteChildren)

DELETE
FROM
	HSP_OBJECT
WHERE
	OBJECT_ID IN (SELECT OBJECT_ID FROM #DeleteParents)

I’ll have a post on that command at some point, but basically it deletes the dimension from the repository.  Now let’s go look again at our dimension list and make sure that it still shows up:

Still there…as expected.  Now let’s try this little query, courtesy of Tjien Lie:

INSERT INTO HSP_ACTION (FROM_ID, TO_ID, ACTION_ID, OBJECT_TYPE, MESSAGE, ACTION_TIME) VALUES (0, 0, 2, -999, 'CACHE RESET',GETDATE())

And let’s take a look at the HSP_ACTION table and make sure that we have the row inserted:

We can also check out this table to see if our cache has been updated yet.  As long as the row is here, we know that the cache hasn’t yet been updated.  After a little while, I checked the table again:

Now that our table is empty, Planning will tell us that it did in fact refresh the cache:

That takes the guess work out of it!  So how about our dimension…is it gone?

And just like that, the dimension is gone.  I can make all of the change that I want and I no longer need to restart Planning.  Special thanks to Tjien Lie from The Four Seasons for providing the code.  Information exchange like this is why I love Kscope and can’t wait to see everyone in my home state of Texas next year!  That’s it for now!