Question
· Dec 26, 2016

Is it possible to build cube(source class:DataConnector) during source table updating?

 Hello,

I have a question regarding Data Connector cube.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_connector

Is it possible to build cube which using data connector for a listing during source table updating?
I defined the simple select query for the SourceQuery in the cube class(Extends %DeepSee.DataConnector)

e.g.
<sql>
 SELECT nid, d1, d2 FROM Test GROUP BY nid
 </sql>

It was no problem that data inconsistency would occur between fact table and source table data.
It'll update(build cube) once a day.
However, it's a serious problem that source table(e.g.Test) update failed or error.

It looks no problem for building DataConnector cube during source table updating,
because the DataConnector class just run select query for a source table.

Is it possible to build cube(source class:DataConnector) during source table updating?
If it's not possible, what kind of problem will occur?


Additional info:

The table is located same database with the DataConnector class.

It's not a big issue whether the data is updated or not.
Since the cube builds once every late at night, so it does not matter whether the data is one day ago or not.
The important thing is that building a cube at the same time as updating the table does not corrupt the table data(or fail to run the query).

Discussion (2)0
Log in or sign up to continue

Hi Megumi,

The worst-case scenario during the cube build would be the fact that the update to the source needs exclusive lock to perform the update, and the cube build will be taking out a read lock. This is a pretty standard SQL concurrency consideration. Your question also mentions listings, which wouldn't have any effect on the cube build since those are only run by the user when querying.

The bigger question is whether or not a DataConnector is actually needed. It takes a bit of extra work to make DataConnectors work with listings, most notably the use of <sql> is not sufficient to make listings work. The full instructions for creating ataConnectors that support listings can be found here:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_connector#D2IMP_connector_callback

If the goal is to use a restricted view of the a single source table, adding a Build Restriction to the cube is far preferable to using a DataConnector. Implmenting the %OnProcessFact callback in the cube is also a good option.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2MODADV_ch_advanced#D2MODADV_advanced_restricting_records
If these can accomplish the task, they should be used. DataConnectors are only truly necessary when tables are JOINed or UNIONed to manufacture a logical dataset that is not a subset of a single existing source table.

Hi Dan,

Thank you for your response.

As long as I checked with a simple query, it did not seem to be using locks while building the cube.
In this time, I don't need detail listings and partial updates. I'll build the whole cube every time.
However, I'll try to use the way of your link(Defining the Query at Runtime) and set isolation level before running select query to avoid the worst case.

As for using buildRestriction and %OnProcessFact, I need to use DataConnector in my case.
I'm using aggregate function in my select item.
e.g. SELECT nid, MAX(CASE WHEN x='a' THEN d ELSE NULL END) as d1,  ...

I'll try to use "buildRestriction and %OnProcessFact" not DataConnector if the view can be controlled with the where clause in the future.