dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Yes, I casted the Value field to number using the val() function,
val(Value). I  modified the Query used by the pivot table in:
Pivot Tables top menu->Change Data Source->Connection
Properties->Definition->Command text

Not sure where this query is stored as these changes did not show in the
.odc after saving the Excel file.
There is probably connection information also in Excel somewhere, since the
query is on top of the .odc.

If possible, it sounds like a better deal to modify this in the .odc file
and just make sure Excel gets the data in the correct format.

----------------------------------
Ola Hodne Titlestad (Mr)
HISP
Department of Informatics
University of Oslo

Mobile: +47 48069736
Home address: Vetlandsvn. 95B, 0685 Oslo, Norway. Googlemaps
link<http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=Vetlandsvn.+95B,+0685+Oslo,+Norway>


2010/10/27 Bob Jolliffe <bobjolliffe@xxxxxxxxx>

> 2010/10/27 Lars Helge Øverland <larshelge@xxxxxxxxx>:
> >
> >
> > 2010/10/27 Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> >>
> >> Attached is a simple skeleton overview diagram of how it *can* be
> >> done.  I'm not sure if I'm 100% comfortable with the approach yet but
> >> I can verify that it works (caveat below).
> >>
> >> From the diagram, the point ultimately is to populate the
> >> pivottablecache of the pivottable within the local excel spreadsheet
> >> with the data from the view in the remote database.  This is what you
> >> would normally do (in a connected setting) by setting an odbc type
> >> connection to the postgres database.
> >>
> >> What I have done is instead to create a connection to a local csv
> >> file.  This is done by creating a odc connection description file and
> >> pointing the pivottable at that.  Selected nippet from example odc
> >> connection file:
> >> <xml id='msodc'>
> >>  <odc:OfficeDataConnection
> >>  xmlns:odc="urn:schemas-microsoft-com:office:odc"
> >>  xmlns="http://www.w3.org/TR/REC-html40";>
> >>  <odc:Connection odc:Type="OLEDB">
> >>   <odc:ConnectionString>Provider=MSDASQL.1;Persist Security
> >> Info=False;Data Source=csv;Extended
> >>
> >>
> Properties=&quot;DSN=csv;DefaultDir=C:\Users\bobj\Documents\excel;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;&quot;;Initial
> >> Catalog=C:\Users\bobj\Documents\excel</odc:ConnectionString>
> >>   <odc:CommandType>Table</odc:CommandType>
> >>
> >>
> <odc:CommandText>`C:\Users\bobj\Documents\excel\test.csv`</odc:CommandText>
> >>  </odc:Connection>
> >>  </odc:OfficeDataConnection>
> >> </xml>
> >>
> >> I've taken a 30MB csv file dumped from the
> >> _VIEW_PIVOTSOURCEROUTINEDATAOU3ALL view of Ola's sample data.
> >> (Grabbed from h2 with: CALL CSVWRITE('test.csv', 'SELECT * FROM
> >> _VIEW_PIVOTSOURCEROUTINEDATAOU3ALL');  )
> >>
> >> Using the odc above, refreshing the pivottable causes a copy from the
> >> csv file into the pivot cache which seems pretty quick (4-5 seconds) -
> >> certainly quicker than pulling it via tcp-odbc-jdbc or whatever.
> >>
> >> Note that worksheet can have more than one pivottable and more than
> >> one pivotcache and that more than one pivottable can share the same
> >> pivot cache.  I haven't gone into those details.
> >>
> >> So the upside is local pivottable refresh is quick and appending
> >> monthly csv snippets is trivial.  Another positive spinoff in havng
> >> the datset as a local csv is that you can also pivot with all kinds of
> >> other tools other than excel (ranging from primitive awk to fancy 'r'
> >> reshaping - both of which I think I prefer to excel :-) ).  But
> >> managing this setup is fairly straightforward - 3 files:  the excel
> >> file, the odc and the csv files which grow with monthly updates (I
> >> know there must also be a facility to amend changed data but we'll
> >> cross that bridge ..) .   And there are of course downsides.
> >>
> >> These csv files are really pretty big and hugely sub optimal.  The
> >> normalization of the database has been flattened out for convenience
> >> of pivoting so we are dealing with tens of thousands of repeated
> >> strings (datelement names, ou names etc).  From a transport
> >> perspective this is maybe not too bad - I'm guessing a reasonable
> >> compression might factor out some of this.   And appending monthly
> >> chunks wouldn't be too bad.  But they are pretty big files to manage.
> >>  But I think they are maybe more robust than depending solely on the
> >> pivotcache as a local primary store.
> >>
> >> That's it for now.  Thought to sleep on is whether we can't somehow
> >> just maintain a local database which replicates the necessary tables
> >> from the online server.  But its complicated.  Maintaining a couple
> >> (hopefully not too many) csv files might be easier.
> >
> >
> > Nice. Explicitly separating presentation from data with csv sounds more
> > manageable. I think we can live with "yearly" pivot tables, in that these
> > things won't keep growing forever.
> >
> >>
> >> Bob
> >>
> >> PS.  The caveat.  My csv source for pivottables sort of works but all
> >> my datavalues are being treated as zero :-(  I think this is probably
> >> to do with setting the datatype of the field somehow.  I think its
> >> fixable.
> >
> > Might be related to the fact that DataValue.value is a String. Maybe Ola
> has
> > some experience?
>
> Yes it has to do with interpreting fields as numeric rather than
> string.  csv driver defaults to string assumption.  Haven't figured
> the best place to tell it otherwise yet.  But I think Ola has ..
>
> >
>