Skip to main content

Arquero Patterns & Best Practices

Farseer Client uses Arquero extensively for data manipulation. This guide covers best practices and common patterns.

Best Practices​

1. Use export.exportFormula() for Variable Data​

// Good - Get variable DATA
const blob = await client.export.exportFormula({
    format: ExportRequestFormatEnum.Csv,
    formula: 'sum("Revenue", "Y2024")'
});
const data = aq.fromCSV(await blob.text());

// getVariable() is only for METADATA (ID, dimensions, formula)
const variable = await client.getVariable('Revenue');
console.log(variable.id);              // Just the ID
console.log(variable.dimensionTagIds); // Linked dimension IDs

2. Calculate On-Demand Cells Before Exporting​

// Ensure calculated fields are up-to-date
await client.cells.calculateOnDemandCells();
const data = await client.export.exportFormula({...});

// Or with timeout for long calculations
const calc = client.cells.calculateOnDemandCells();
const timeout = new Promise(r => setTimeout(r, 10 * 60 * 1000));
await Promise.race([calc, timeout]);

3. Use Batch Processing for Large Imports​

const batchSize = 1000;
for (let i = 0; i < largeDataset.length; i += batchSize) {
    const batch = largeDataset.slice(i, i + batchSize);
    await importJob.addRows(batch);
}
await importJob.flushRows();

4. Use Labels for Import Job Management​

const labels = [
    'auto',
    'sales-import',
    `sales-import_${year}-${month}`,
    'source:erp'
];

// Labels enable undoPrevious() to find matching imports
await importJob.undoPrevious();
await importJob.commit();

5. Validate Data Before Import​

await client.initTagMap();
const products = await client.getDimensionMembersForTable('Product');
const validNames = products.map(p => p.name);

const rows = table.objects();
const validRows = rows.filter((r: any) => validNames.includes(r.product));
console.log(`${rows.length - validRows.length} invalid rows removed`);

Common Patterns​

Dimension Member Lookup Map​

async function getDimensionTableMap(
    client: FarseerClient,
    dimensionTableName: string
): Promise<Map<string, TagRepresentation>> {
    const map = new Map<string, TagRepresentation>();
    const members = await client.getDimensionMembersForTable(dimensionTableName);
    members.forEach(m => map.set(m.name, m));
    return map;
}

// Usage
const companiesMap = await getDimensionTableMap(client, 'Companies');
if (!companiesMap.has('Company A')) {
    console.log('Company A does not exist');
}

Tag Navigation​

await client.initTagMap();

// Get dimension name from table ID
const tableName = Array.from(client.tagMaps.dimensionTableMapByName.values())
    .find(t => t.id === tableTagId)?.name ?? '';

// Get dimension names from variable's dimension tag IDs
const variable = await client.getVariable('Revenue');
const dimensionNames = variable.dimensionTagIds?.map(dimId =>
    client.tagMaps?.mapById.get(dimId)?.name
).filter(Boolean);

console.log(`Revenue has dimensions: ${dimensionNames.join(', ')}`);

Using aq.escape() for Modern JavaScript​

Arquero's expression parser doesn't support optional chaining (?.) or nullish coalescing (??). Use aq.escape():

import * as aq from 'arquero';

// Without escape (simple expressions only)
const filtered = table.filter(d => d.value > 0);

// With escape (modern JS syntax)
const filtered = table.filter(aq.escape((d: any) => d?.value !== null && d?.value !== 0));

// Derive with escape
const transformed = table.derive({
    Years: aq.escape((d: any) => d.Years?.substring(1)),
    Months: aq.escape((d: any) => d.Months?.substring(1))
});
tip

After .objects(), you get a plain JavaScript array. No aq.escape() needed:

const rows = table.objects().filter((d: any) => d?.value > 0);

Accessing Dimension Connections​

Dimension connections in Arquero tables are arrays:

const { table } = await client.data.loadFarseerDimensionTable('Product');

// Always use [0] for connections
const filtered = table.filter(d => d?.['Category'][0] === 'Electronics');

const enriched = table.derive({
    CategoryName: d => d?.['Category'][0] || 'Unknown',
    SupplierName: d => d?.['Supplier'][0] || 'N/A'
});