> -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Petite Abeille > Sent: 14 October 2011 03:58 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and > pivottables) > > Pivot tables can be populated from another worksheet, a data source, an > OLAP cube, etc...
Yes, indeed. > That number is pretty high these days, but yes there is no point using an > intermediary worksheet. Get the data directly from source. Also, you may > want to pre-process, e.g. summarize, your data in the database already as > much as you can before hand. Excel is not a speed daemon when confronted > with a truck load of data. > > You can also create and save the pivot table as an offline OLAP cube, with > hierarchical dimensions & all (Microsoft Query + OLAP Cube Wizard). Row max is slightly more than a million rows now which is a lot better than in Excel 2003. However, the data sets that I use often go above that number of rows. Pre-processing is an option that I sometimes use. But it then freezes the representation of the data in that form. The ability to change the view of the data in Pivottables is a very nice feature. I find that Excel has decent performance in Pivottable calculations, especially so with the newer versions. I might look into OLAP cubes, although I find it adds yet another computational layer. Probably the best option for now is to stick with the ODBC driver and just accept the hassle of creating a new DSN for every individual database which requires some registry manipulation which is a bit messy but can be done. I am still dreaming and hoping that someone might have the perfect solution for just using the SQLite database as a proper OLE DB data source which seems to be the way to do it nowadays or using the generic ODBC driver as the data source without having the create an individual DSN. /Frank _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
