Databasfrågorna för Fortnox-datakällorna i Google Looker Studio

Hej,

Finns det möjlighet att få tillgång till de queries som används för Fortnox-datakällorna i mallen för Google Looker Studio?

Hej @Philip! Absolut. Vi är 100% transparenta med våra datamodeller. Se efterfrågade queries (databasfrågor) i SQL nedan.

Hör av dig om du behöver något mer.

Bokföring (vouchers)

select
  v.OrgId,
  c.OrgName,
  fy.FinancialYear,
  fy.FyCounter,
  format_date("%Y%m%d", v.TransactionDate) as TransactionDate,
  v.Description as VoucherDescription,
  v.VoucherSeries,
  v.VoucherNumber,
  vs.Description as VoucherSeriesName,
  v.Account,
  a.AccountName,
  a.AccountClassId,
  case
  when CAST(v.Account as INTEGER) between 1000 and 1999 then "1. Tillgångar" --1. Assets
  when CAST(v.Account as INTEGER) between 2000 and 2999 then "2. Eget kapital och skulder" --2. Equity and Liabilities
  when CAST(v.Account as INTEGER) between 3000 and 3999 then "3. Intäkter" --3. Operating income/revenue
  when CAST(v.Account as INTEGER) between 4000 and 4999 then "4. Materialkostnader" --4. Cost of goods
  when CAST(v.Account as INTEGER) between 5000 and 6999 then "5-6. Övriga kostnader" --5-6. Other external costs
  when CAST(v.Account as INTEGER) between 7000 and 7999 then "7. Personal" --7. Personnel costs
  when CAST(v.Account as INTEGER) between 8000 and 8999 then "8. Finansiella intäkter/kostnader" --8. Financials
  ELSE CAST(v.Account as STRING)
  END AccountClass,
  -- a.AccountClass,
  a.MainAccountId,
  a.MainAccount,
  a.SubAccountId,
  a.SubAccount,
  cc.Description as CostCenter,
  p.Description as Project,
  v.Balance,
from
  `enhanza-elt.fortnox_bi.fact_vouchers` v
  left join `enhanza-elt.fortnox_bi.dim_company` c on v.OrgId = c.OrgId
  left join `enhanza-elt.fortnox_bi.dim_financial_years` fy on v.FinancialYearId = fy.FinancialYearId
  left join `enhanza-elt.fortnox_bi.dim_voucher_series` vs on v.VoucherSeriesId = vs.VoucherSeriesId
  left join `enhanza-elt.fortnox_bi.dim_accounts` a on v.AccountId = a.AccountId
  left join `enhanza-elt.fortnox_bi.dim_cost_centers` cc on v.CostCenterId = cc.CostCenterId
  left join `enhanza-elt.fortnox_bi.dim_projects` p on v.ProjectId = p.ProjectId

Fakturor (invoices)

SELECT
  i.OrgId,
  cs.OrgName,
  i.InvoiceNo,
  format_date("%Y%m%d", i.InvoiceDate) as InvoiceDate,
  i.OurReference,
  c.Name as CustomerName,
  c.CustomerNumber,
  c.City as CustomerCity,
  c.Country as CustomerCountry,
  i.Net,
  i.Gross,
  i.Freight,
  i.TotalVAT,
  i.TotalToPay,
  i.Sent,
  i.Balance,
  format_date("%Y%m%d", i.DueDate) as DueDate,
  IF (DueDate < current_date(), true, false) Due,
  case
    when Sent IS false then "NotSent"
    when Sent IS true then case
      when Balance = 0 then "Paid"
      when Balance <> 0 then case
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 0
        and 15 then "0-15d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 16
        and 30 then "16-30d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 31
        and 45 then "31-45d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 46
        and 60 then "46-60d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 61
        and 90 then "61-90d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) between 91
        and 120 then "91-120d"
        when ABS(DATE_DIFF(current_date(), DueDate, day)) > 120 then ">120d"
        when DueDate is null then 'NO_DUE_DATE'
        else 'WHAT?'
      end
      else 'WOW??'
    end
    else "[calc_error]"
  end DueStatus,
  i.Reminders,
  format_date("%Y%m%d", i.LastRemindDate) as LastRemindDate,
from
  `enhanza-elt.fortnox_bi.fact_invoices` i
  left join `enhanza-elt.fortnox_bi.dim_company` cs ON i.OrgId = cs.OrgId
  left join `enhanza-elt.fortnox_bi.dim_customers` c ON i.CustomerId = c.CustomerId
where
  i.InvoiceDate between date_sub(current_date(), interval 3 year)
  AND date_add(current_date(), interval 1 year)

Fakturarader (invoice rows)

select
  i.OrgId,
  cs.OrgName,
  i.InvoiceNo,
  format_date("%Y%m%d", i.InvoiceDate) as InvoiceDate,
  i.OurReference,
  c.Name as CustomerName,
  c.CustomerNumber,
  c.City as CustomerCity,
  c.Country as CustomerCountry,
  i.ArticleNumber as ArticleNumber,
  i.DeliveredQuantity,
  a.Description as ArticleName,
  -- i.AccountId as AccountNumber,
  i.SalesValue,
  i.ContributionValue as ContributionValue,
  cc.Description as CostCenter,
  p.Description as Project
from
  `enhanza-elt.fortnox_bi.fact_invoice_rows` i
  left join `enhanza-elt.fortnox_bi.dim_company` cs ON i.OrgId = cs.OrgId
  left join `enhanza-elt.fortnox_bi.dim_customers` c ON i.CustomerId = c.CustomerId
  left join `enhanza-elt.fortnox_bi.dim_articles` a ON i.ArticleId = a.ArticleId
  left join `enhanza-elt.fortnox_bi.dim_cost_centers` cc on i.CostCenterId = cc.CostCenterId
  left join `enhanza-elt.fortnox_bi.dim_projects` p on i.ProjectId = p.ProjectId
where
  i.InvoiceDate between date_sub(current_date(), interval 3 year)
  and date_add(current_date(), interval 1 year)

Saldo & budget

with accounts as (
  with months as (
    -- create an array of dates per the end of each month from all financial years.
    select
      OrgId,
      FinancialYearId,
      FinancialYear,
      FyCounter,
      last_day(m) month_last_day
    from
      `enhanza-elt.fortnox_bi.dim_financial_years`
      cross join unnest(
        GENERATE_DATE_ARRAY(FromDate, ToDate, interval 1 month)
      ) as m
  )
  select
    -- List all accounts with their BalanceBroughtForward and sum of balance from vouchers.
    m.OrgId,
    m.FinancialYearId,
    m.FinancialYear,
    m.FyCounter,
    m.month_last_day Month,
    a.Number Account,
    a.AccountName,
    concat(a.MainAccountId,". ", a.MainAccount) as MainAccount,
    -- last_day(v.TransactionDate) VoucherMonth,
    ifnull(sum(Balance) * -1, 0) Balance,
    BalanceBroughtForward
  from
    months m
    left join `enhanza-elt.fortnox_bi.dim_accounts` a on m.FinancialYearId = a.FinancialYearId
    left join `enhanza-elt.fortnox_bi.fact_vouchers` v on v.Account = a.Number
    and v.FinancialYearId = m.FinancialYearId
    and last_day(v.TransactionDate) = m.month_last_day
  where
    a.Number between 1000
    and 2999
  group by
    m.OrgId,
    m.FinancialYearId,
    m.FinancialYear,
    m.FyCounter,
    a.Number,
    a.AccountName,
    MainAccount,
    Month,
    a.BalanceBroughtForward
)
select
  a.OrgId,
  cs.OrgName,
  a.FinancialYear,
  a.FyCounter,
  format_date("%Y%m%d", a.Month) as Month,
  a.Account as AccountNumber,
  a.AccountName,
  a.MainAccount,
  a.BalanceBroughtForward,
  sum(a.balance) over (
    partition by a.FinancialYearId,
    a.Account
    order by
      Month
  ) + a.BalanceBroughtForward - a.balance as PeriodOpeningBalance,
  balance PeriodBalance,
  sum(balance) over (
    partition by FinancialYearId,
    Account
    order by
      Month
  ) + BalanceBroughtForward as BalanceCarriedForward
from
  accounts a
  left join `enhanza-elt.fortnox_bi.dim_company` cs on a.OrgId = cs.OrgId
  -- ignore empty rows.
  -- where a.BalanceBroughtForward <> 0 and PeriodOpeningBalance <> 0 and PeriodBalance <> 0 and BalanceCarriedForward <> 0
1 gillning