November 13, 2018 at 5:32 pm #530FrankKeymaster
- Topics Started - 51
- Replies Written - 45
What is the fastest way to export from Stratum to Excel?
Soooo….my first response is always, do you really need to export? Are you just looking for a calc that you are more comfortable with in excel vs. Stratum. If so, it might save you time overall to do the calc once in Stratum then you can use all the flexibility of Stratum to see what you are looking for. That all said there are many other reasons to export to excel and Stratum provides a number of ways and techniques to get stuff to excel.
First, if the question is what takes the least clicks to get data to excel….then it’s hands down just click the excel button! But I don’t think that is what people are really looking for when we get this “…fastest way to excel” question. I think generally folks are looking for how to export large amounts of data as fast as possible to excel. And they are looking to use the data in excel, not for a WYSIWYG report. I’ll list some of my suggestions below:
1. Make sure your view has Use Paging turned on. You can set this in the Display Options settings for the view – it’s the icon with the wrench in it on the menu bar. You can find a good description of all the menu icons here. You want to do this so that Stratum does not have to retrieve all the data back to your session and display it – the objective is only have all the data come back once we are ready to export to excel and Stratum does this automatically regardless of the Use Paging setting.
2. Turn off “Lock Rows and Columns” and “Show Totals” Both of these are in also in the Display Options settings for the view. The reason you want these off is that both take time when Stratum is creating the HTML to return to your browser – again our objective is to get the data to excel. The “Show Totals” remove the totals from the excel output which you probably don’t want anyway if you are going to use the data for sorting/filtering in excel.
3. Don’t Sort the data in Stratum unless you need it that way prior to sorting it in excel – if Stratum needs to sort the data or retrieve the All Others Row – then we’ll have to process that while we are getting the data ready for export to excel – taking more time in Stratum.
4. If you have access to the Broadcast Server you will have the options (starting with version 6.9 which I suggest everyone go up to – even if you don’t have the Broadcast Server – the new Measures window is great and you can get a free trial of the Broadcast Server to try it out) to select an “Export Type” in the Display Options settings. If you have this select “Excel Data”. If you don’t have the option for Export Type you will be exporting an Excel Report and will need to turn on “Repeat Item Labels”.
5. Don’t have any conditional formatting in your view – this can cause some awkward formatting in the excel output if you are trying to do calcs in excel with the data used for the conditional formatting. There is a separate post about this.
At this point you are ready to export – fire away….but wait there are some further considerations…..
So – if you are exporting very large data sets and you don’t have the Broadcast Server and the new Excel Data option I suggest you consider using an Action to export since these can be run in the background via a scheduler. The new Excel Data option for exports is many many many …times faster than the Excel Report option.
The downside of the Excel Data export option – you get raw data, not nicely formatted data (this is the main difference between an Excel Report and Excel Data). You can also get some goofy headings if you are exporting with a dimension across the top – such as months. But IMHO these are good trade offs to get the better performance.
Most of the items I listed will help with any kind of an export and don’t require 6.9 or the Broadcast server, but the fastest way to excel data does include getting 6.9 and the Broadcast Server.
Confused by all this?? Because I may of confused my self – just post something here and I’ll leave a response.
- This topic was modified 6 months, 1 week ago by Frank.
You must be logged in to reply to this topic.