![excel slicer excel slicer](https://i.ytimg.com/vi/cJSFBPp-qEo/maxresdefault.jpg)
So, this is yet another reason to consider using a separate, smaller table that just contains all unique dates, and then relating that back to your large table.
#Excel slicer for free
That doesn’t happen for free – the PowerPivot engine is gonna have to work pretty hard just to generate that distinct list, and it might have to do that every time you interact with the report (click a slicer, change a page filter, etc.) When you park that Date column on a slicer, you are demanding that the slicer populate itself from all of the distinct values of the Date column. Say you have a 2 million row table, and one of the columns is Date. Tip #1: Are you using a field from your measure table as a slicer? I don’t know how often it checks to see if something has changed in the underlying model – I’ll have to run some tests. What I will give you, though, is an intuitive sense of what a slicer has to do.įirst of all, it has to populate itself. Well, I won’t go into great detail here, because 1) I don’t know every detail and 2) Vidas covered the tech details quite well here. In those cases, one of the first places you should look is your usage of slicers. When a client recently told us “Reports are dead, now we have Pivotstream!” there was no way we would have received that reaction without slicers.īut every now and then you will find yourself with a report that doesn’t operate quickly enough for your purposes, and slow response times can drain the value and utilization out of a report quickly (well, slowly I guess). Remember, slicers are the difference between report consumers loving your work and dismissing it as just more nerdy junk. It is not worth monkeying around with your set of slicers to trim half a second. Hey, if your report is fast, don’t obsess. Slicers can, if used improperly in a report, end up slowing a report down by a factor of 5, 10, or more. I kinda missed the next couple minutes of what Amir was saying, as I digested the implications for our work at Pivotstream.Īnd, I was kicking myself for forgetting this, because I had once known most of this in Redmond: “Now look at this report! It has 4 pivotcharts and 6 slicers, each of which issues 2 queries whenever I click a slicer, meaning this report is actually querying the 2 Billion rows twenty times!”Įach slicer issues 2 queries against the data source? It was revelation time. In an effort to explain that what we were seeing (split-second pivot performance against said 2 Billion rows), he said the following: He was showing off a 2 Billion row model, and its amazing performance, and while that was eye-opening, something he said in passing caught my attention in a big way. OK, I was sitting at the 2nd-day keynote down in New Orleans last month, watching Amir Netz do a demo on some PowerPivot goodies we can expect in the next release.