It seems to happen for me after something culturally cool happens, like when the lady tells me my citizens' are becoming more sophisticated or when I just get a great person or something. I don't know specifically, but I'm interested in an answer too.
Yes, it certainly happens when you get to the status of being sophisticated as a civilization and then the individual cities do also need to be sophisticated for the celebration to start in that particular city. That is my take at it.
My question is, does it have any kind of value in the game or is it just another "show off" like the gifts with dancing bears and stuff.
I think you get it when your city or cities yield the most culture out of any other. AND when you do get it you also get a culture boost...... dont take my word for it though I just noticed it on the ds.
The text suggests some sort of culture bonus, I've never looked at the numbers because I seldom go culture. But it has always happened for me right after building a culture building, wonders and churches.