Setting up cube explore on Oracle

[zehavibarak]

Cube and Indices database tables reflects data from both BizDoc Entries and Oracle app. You may want to enable BizDoc to explore Oracle app when the user explores figures.

a. Declare a PO on your EF context that maps to the PO table on your Oracle app.

    public class OracleDb : DbContext
    {
        public DbSet<PO> POs { get; set; }
    }

b. On your cube backend object, implement IExplore for your PO

    public class TxCube : CubeBase, CubeBase.IBrowsable<PO> {}

c. Implement cube IExplore QueryAsync explicitly, and query POs using the SQL phrase

    async Task<IEnumerable<PO>> IBrowsable<PO>.QueryAsync(params Axis[] axes)
    {
        var sql = new StringBuilder("SELECT * FROM PO WHERE ");
        var sqlArgs = new List<string>();
        FormatAxes("EXTRACT(YEAR FROM Date)", axes.ElementAtOrDefault(0), sql, sqlArgs);
        FormatAxes("TO_CHAR(Date, 'Q')", axes.ElementAtOrDefault(1), sql, sqlArgs);
        ...
        var pos = _oracle.POs.FromOracleRaw(sql.ToString(), sqlArgs.ToArray());
        return pos;
    }

Use the OracleExtensions.FormatAxes static method to translate Axis into SQL phrase.

Alternately, you can query Oracle using ExecuteReaderAsync extension.

using var conn = new OracleConnection(OracleDb.ConnectionString);
var pos = await conn.ExecuteReaderAsync<PO>(sql.ToString(), sqlArgs.ToArray());
conn.Close();

Requires BizDoc.Core.Oracle Nuget. Read more on enabling cube explore in the documentation.