SharePoint & OneDrive
as the last mile
for master data.

# ── Notebook: export_cost_centres.ipynb ──────────────
import pyodbc, json, datetime
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.user_credential import UserCredential

# 1. Query source DB ──────────────────────────────────
conn   = pyodbc.connect("DSN=FinanceDW;Trusted_Connection=yes")
cursor = conn.cursor()
cursor.execute("SELECT * FROM vw_CostCentres_RTM")
cols   = [c[0] for c in cursor.description]
rows   = [dict(zip(cols, row)) for row in cursor.fetchall()]

# 2. Serialise ────────────────────────────────────────
payload = json.dumps(rows, default=str, indent=2)

# 3. Upload to SharePoint ─────────────────────────────
ctx = ClientContext("https://contoso.sharepoint.com/sites/Finance")\
      .with_credentials(UserCredential(SP_USER, SP_PASS))

lib = ctx.web.get_folder_by_server_relative_url(
    "/sites/Finance/MasterData")
lib.upload_file("cost-centres.json", payload.encode()).execute_query()

# 4. Optional: dated snapshot for SCD Type 2 ─────────
today = datetime.date.today().strftime("%Y-%m")
lib.upload_file(f"cost-centres-{today}.json", payload.encode())\
    .execute_query()

print(f"✓ {len(rows)} rows published · snapshot: cost-centres-{today}.json")

-- vw_CostCentres_RTM — the view RTM reads via the pipeline
CREATE OR ALTER VIEW dbo.vw_CostCentres_RTM
AS
SELECT
    cc.CostCentreCode  AS CostCentreCode,  -- PK for RTM row identity
    cc.Description     AS Description,
    cc.DepartmentCode  AS Department,
    d.DepartmentName   AS DepartmentName,
    cc.CostCentreType  AS Type,
    cc.Manager         AS ManagerEmail,
    cc.EffectiveFrom   AS EffectiveFrom,
    cc.IsActive        AS IsActive
FROM   dbo.CostCentres cc
JOIN   dbo.Departments d ON d.DepartmentCode = cc.DepartmentCode
WHERE  cc.IsActive = 1
  AND  cc.EffectiveFrom <= GETDATE();

-- RTM auto-detects arrays; no dataRootPath needed here.
-- For nested envelopes, set dataRootPath = "data" in the wizard.

// cost-centres.json — auto-detected by RTM as an array
[
  {
    "CostCentreCode": "CC-1001",
    "Description":    "Finance Operations",
    "Department":     "FIN",
    "DepartmentName": "Finance",
    "Type":           "Overhead",
    "ManagerEmail":   "[email protected]",
    "EffectiveFrom":  "2023-07-01",
    "IsActive":       true
  },
  // ... more rows
]

// Nested envelope — use dataRootPath = "data" in the RTM wizard:
{
  "version": "2025-05",
  "source":  "FinanceDW",
  "data": [ /* RTM resolves this path automatically */ ]
}