Recently I have been doing some work with SSAS KPI’s and I found myself in a pickle. Most of my work on the KPI’s had been in a local development database and I was a t a stage where the KPI’s needed to be tested against a larger dataset. Since I had about 25 KPI’s I thought I would just script them out and add them to the larger cube. To my amazement and chagrin I found there wasn’t any built in way to script out KPI’s.
So after some thought I decided to attack this from the XMLA side. I scripted out my development cube and then started searching. I found what I was looking for at the bottom of the XMLA script. Tucked between <KPIs> </KPIs> was all of the XML for the 25 KPIs I had defined.
I copied the entire section between the <KPIs> tags and pasted that into notepad. Next I went to my new cube and scripted it out as an ALTER (very important). I then scrolled to the bottom of it and pasted my KPI definitions between the <KPI> tags. Syntax checked the file and then clicked execute.
Next I opened that new cube in BIDS and clicked on the KPI tab and voila! all of my KPI’s were there. I did notice some formatting issues with things like the status definitions,etc… but nothing that would prevent the KPI from running.
So, it wasn’t pretty but I was able to move the 25 KPIs to the new cube without having to redefine them again one at a time.