Power BI

Ushbu qo’llanma /v1 ma’lumotlarini Power BI’ga o’qishning boshdan-oxirigacha bo’lgan namunasidir. Unda Power Query (M) orqali ulanish namunasi, /v1/activities asosida qurilgan star-schema semantik modeli, incremental refresh hamda har bir shaxs bo’yicha fishing natijalari uchun reporting preview datasetlaridan qachon foydalanish kerakligi yoritilgan. Namunalarda AQShdagi production host (https://api.us-east-1.livingsecurity.ai/v1) ishlatiladi. Yevropa Ittifoqidagi tashkilot uchun Fn_FetchAll va Fn_GetJson ichida https://api.eu-west-1.livingsecurity.ai/v1 manzilidan foydalaning, so’ngra ma’lumotlar manbasi hisob ma’lumotlarini (credential) shu hostga bog’lang. Regions bo’limiga qarang. Ushbu qo’llanmada API kaliti bilan saqlangan Basic hisob ma’lumotlari ishlatiladi. OAuth klienti talab qilinmaydi. Hisob ma’lumotlarini tanlash bo’yicha Authentication bo’limiga, kirish va sozlash bo’yicha esa Developer Hub bo’limiga qarang.

Ushbu namuna nima uchun mo’ljallangan

Quyidagi hollarda foydalaning:

/v1 ro’yxat endpointlarini to’g’ri cursor pagination va rate-limit qayta urinishi bilan Power BI datasetiga yuklash kerak bo’lganda.
O’lchovlar (“active entities”, “activities per active entity”) tekis jadvaldan sanalmasdan, dimensional tarzda hisoblanadigan semantik model qurish kerak bo’lganda.
Rejalashtirilgan Power BI refresh’ni butun tarixni to’liq o’qishdan incremental refresh yordamida kichik kunlik oynagacha qisqartirish kerak bo’lganda.
To’g’ri fishing grain’ini tanlash kerak bo’lganda: kampaniya yig’indilari (operatsion) yoki har bir shaxs bo’yicha natijalar va tiplangan hodisalar (reporting preview).

Talablar

So’rovlaringiz uchun zarur ruxsatlarga ega API kaliti:
- Operatsion namuna: activity:read, entity:read va simulation:read.
- Reporting datasetlari orqali har bir shaxs bo’yicha fishing: reporting:read. API keys bo’limiga qarang.
Power BI Desktop yoki semantik model yaratish va refresh’ni rejalashtirish mumkin bo’lgan Power BI / Fabric workspace.

To’g’ri grain’ni tanlang

/v1 fishing uchun ikkita o’zaro bog’liq sirtni taqdim etadi. Ular bir-birini almashtirmaydi.

Ehtiyoj	Foydalaning	Ruxsat	Grain
Kampaniya voronkasi yig’indilari	`GET /v1/simulations/{id}/results`	`simulation:read`	Har bir simulyatsiya uchun bitta qator (delivered, opened, clicked, …)
Har bir shaxs bo’yicha natijalar (operatsion)	`GET /v1/simulation_results`	`simulation:read`	Har bir entity × simulyatsiya uchun bitta qator
Har bir shaxs bo’yicha natijalar (BI / reporting)	`GET /v1/reporting/simulation_results`	`reporting:read`	Xuddi shu grain, BI maydon nomlari (`entity_id`, `simulation_id`), `updated_after` watermark
Tiplangan o’zaro ta’sir faktlari	`GET /v1/reporting/simulation_events`	`reporting:read`	Har bir o’zaro ta’sir uchun bitta fakt (`simulation.link_clicked`, …), `occurred_after`
Joriy Workforce dimensiyasi	`GET /v1/reporting/entities`	`reporting:read`	Har bir entity uchun bitta joriy qator. `reporting.simulation_results.entity_id = reporting.entities.id` bo’yicha bog’lang
Joriy entity versiyalari	`GET /v1/reporting/entity_versions`	`reporting:read`	Har bir entity uchun bitta ochiq joriy interval. Versiyalar tarixi jadvali mavjud emas
Joriy Cohort a’zoliklari	`GET /v1/reporting/cohort_memberships`	`reporting:read`	Har bir `(cohort_id, entity_id)` uchun bitta joriy fakt. A’zoliklar tarixi jadvali mavjud emas
Barcha faoliyat turlari uchun bog’lovchi o’q	`GET /v1/activities`	`activity:read`	Har bir faoliyat uchun bitta hodisa, ichma-ich `participants[]`

Quyidagi Power BI namunaviy so’rovlari operatsion marshrutlarni (/v1/simulations, /v1/simulations/{id}/results, /v1/activities, /v1/entities) chaqiradi. Har bir shaxs bo’yicha fishing hisobotlari uchun quyidagi reporting preview oilasini afzal ko’ring. Faqat reporting:read ruxsati berilgan kalit operatsion /v1/simulation_results’ni chaqira olmaydi (API 403 qaytaradi).

Qiyinlik shkalasi

Simulyatsiya qiyinligi /v1/simulations (difficulty_level), /v1/simulation_results (difficulty_level), /v1/activities xususiyatlari (difficulty_tier) va /v1/reporting/simulation_events (difficulty_tier) bo’yicha 1–5 oralig’idagi butun son hisoblanadi. 1 — eng oson, 5 esa eng qiyin daraja. Adaptiv va progressiv simulyatsiyalarda kampaniya darajasida yagona qiymat bo’lmaydi, shuning uchun /v1/simulations.difficulty_level null bo’ladi, natijalardagi har bir qabul qiluvchi uchun difficulty_level esa baribir belgilanishi mumkin. Eski low/medium/high eksportlariga moslashtirish uchun 1–2 ni low, 3 ni medium, 4–5 ni esa high deb hisoblang.

Yangi BI integratsiyalarini hujjatlashtirilmagan Dashboard tarmoq chaqiruvlari yoki eski, kuratsiya qilingan /api/v1 Public API umumiy ko’rinishi asosida qurmang. Tashkilotning mintaqaviy /v1 hostiga Developer Hub API kaliti bilan autentifikatsiyadan o’ting. Regions bo’limiga qarang.

Ulanish namunasi (Power Query)

Namunadagi har bir so’rov to’rtta qoidaga tayanadi. Ularning har birini uslub bo’yicha afzalliklar emas, Power BI Service talab qiladi.

Web.Contents bazaviy URL'ini statik saqlang va /v1 bilan tugating

Barcha dinamik qismlarni RelativePath va Query ichiga joylang. Web.Contents ning birinchi argumenti tashkilotning mintaqaviy /v1 ildizi bilan tugaydigan literal satr bo’lishi kerak:

US: https://api.us-east-1.livingsecurity.ai/v1
EU: https://api.eu-west-1.livingsecurity.ai/v1

Shundan so’ng chaqiruvchilar faqat resursni uzatadi ("simulations", "v1/simulations" emas). Yevropa Ittifoqidagi tashkilot uchun AQSh hostini qo’ymang.Agar birinchi argument satrlarni birlashtirish orqali hosil qilinsa, Power BI Service ma’lumotlar manbasini statik aniqlay olmaydi va so’rov Power BI Desktop’da ishlasa ham, rejalashtirilgan refresh “dynamic data source” xatosi bilan muvaffaqiyatsiz tugaydi.

API kalitini so'rov matnida emas, saqlangan Basic hisob ma'lumoti sifatida kiriting

Power BI /v1 ma’lumotlar manbasi uchun hisob ma’lumotlarini so’raganda Basic ni tanlang va API kalitini username sifatida kiriting. password maydoniga x kabi o’rinbosar qiymat qo’ying. API bu maydonni e’tiborsiz qoldirsa ham, Power BI Service bo’sh parolni rad etadi. Kalitni parol, o’rinbosar qiymatni esa foydalanuvchi nomi sifatida kiritish ham ishlaydi.M ichida Authorization headerini qo’shmang. U kompyuteringizda ishlaydi, ammo kalitni so’rov fayliga kiritib yuboradi va rejalashtirilgan refresh bilan ziddiyatga tushadi.

Hisob ma'lumotlari oynasidagi ulanishni tekshirish bajarilsin

GET /v1 autentifikatsiyadan o’tgan holda ishlaydi: to’g’ri kalit bilan 200, kalitsiz esa 401 qaytaradi. Ma’lumotlar manbasini …/v1 ga yo’naltiring va tekshirishni o’tkazib yubormang — yaroqli kalit saqlanadi, bekor qilingan yoki noto’g’ri shakldagi kalit esa darhol xato beradi.Eski namunalarda ma’lumotlar manbasi sifatida oddiy host ishlatilgan va GET / doim 404 qaytargani uchun tekshirishni o’tkazib yuborish tavsiya etilgan. Bunday modellarni …/v1 ga qayta yo’naltiring.

Har bir ro'yxat so'rovida limit=500 ni aniq belgilang

Har bir /v1 ro’yxat endpointida sahifa hajmi odatda 25 ga teng. Uni standart holatda qoldirish bir xil ma’lumotlar uchun so’rovlar sonini ko’paytiradi. So’rov qiymatlari matn bo’lishi kerak — M’da limit = "500", limit = 500 emas.

Umumiy pagination funksiyasi

Namunadagi har bir so’rov next_page_token null bo’lguncha uni aylanib chiqadigan yagona Fn_FetchAll ni chaqiradi (pagination bo’limiga qarang). Uning muhim xususiyatlari:

Statik /v1 bazaviy URL, dinamik RelativePath va Query. Rejalashtirilgan refresh va hisob ma’lumotlarini tekshirish uchun zarur.
ManualStatusHandling = {429} cheklangan qayta urinish bilan. Funksiya Power Query ochib bersa Retry-After ni o’qiydi, aks holda 30 soniya kutadi va ketma-ket 5 ta 429 javobidan so’ng refresh vaqti tugaguncha aylanib yurmasdan, aniq xato bilan to’xtaydi. 5xx ataylab qayta urinilmaydi — server xatosi jimgina to’liq bo’lmagan datasetni hosil qilmasdan, yuzaga chiqishi kerak.
60 soniyalik Timeout. Sahifalar odatda soniyadan ancha tez javob beradi, shuning uchun bir daqiqalik sukunat so’rov o’lganini bildiradi.
Json.Document dan oldin Binary.Buffer. Buferlashsiz mashup dvigateli javob oqimini bir necha marta o’qisa, uni qayta so’rashi mumkin.
baseQuery har bir iteratsiyada o’zgarishsiz uzatiladi. Sahifa tokenlari tashkilot, hisob ma’lumoti, kontrakt versiyasi hamda filtrlar va saralash izi bilan bog’langan noshaffof, imzolangan kursorlardir. O’tish o’rtasida filtrni o’zgartirish birinchi sahifadan qayta boshlanmaydi, balki yaroqsiz token sifatida rad etiladi.

Fn_FetchAll.pq — base and call shape

let
    // Literal /v1 root. Callers pass the resource alone ("simulations").
    // US: "https://api.us-east-1.livingsecurity.ai/v1"
    BaseUrl = "https://api.eu-west-1.livingsecurity.ai/v1",

    Fn_FetchAll = (relativePath as text, baseQuery as record) as list =>
        // Walk next_page_token until null. Full pagination + 429 retry lives
        // in examples/powerbi/queries/Fn_FetchAll.pq.
        ...
in
    Fn_FetchAll

Har bir endpoint uchun tiplangan so’rovlar

Fn_FetchAll ning har bir iste’molchisi bir xil shaklga amal qiladi: endpointni chaqiradi, ustunlarning qat’iy ro’yxatini ajratib oladi, object diskriminatorini olib tashlaydi va vaqt belgilarini datetimezone sifatida tiplaydi, shunda sana o’lchovlari refresh quvvatining vaqt zonasi tufayli jimgina siljimaydi.

let
    Source = Fn_FetchAll("simulations", [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "name", "description", "status", "vectors",
            "difficulty_level", "difficulty_mode",
            "started_at", "completed_at", "created_at", "updated_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Flattened = Table.TransformColumns(
        Dropped,
        {
            {
                "vectors",
                each if _ = null then null
                     else Text.Combine(List.Transform(_, Text.From), ", "),
                type text
            }
        }),

    Typed = Table.TransformColumnTypes(
        Flattened,
        {
            { "id",           type text },
            { "name",         type text },
            { "description",  type text },
            { "status",       type text },
            { "started_at",   type datetimezone },
            { "completed_at", type datetimezone },
            { "created_at",   type datetimezone },
            { "updated_at",   type datetimezone }
        })
in
    Typed

let
    Source = Fn_FetchAll("entities", [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        { "id", "object", "type", "status", "display_name", "email",
          "created_at", "updated_at" },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",           type text },
            { "type",         type text },
            { "status",       type text },
            { "display_name", type text },
            { "email",        type text },
            { "created_at",   type datetimezone },
            { "updated_at",   type datetimezone }
        })
in
    Typed

Table.FromRecords da ustunlarning aniq ro’yxati endpoint nol qator qaytarganda ham sxemani barqaror saqlaydi. Oddiy Table.FromRecords({}) nol ustunli jadval hosil qiladi va keyingi barcha qadamlar yetishmayotgan maydonlar sababli xato beradi.

Excel

Xuddi shu Power Query (M) Excel’ning Get & Transform bo’limida ham ishlaydi — bu Power BI Desktop bilan bir xil dvigatel. Semantik model o’rniga varaqdagi jadval kerak bo’lganda foydalaning. Excel for Windows’da (Microsoft 365, Excel 2024, 2021, 2019 yoki 2016):

Data → Get Data → From Other Sources → Blank Query.
Power Query Editor’da Home → Advanced Editor ni oching. Avval Fn_FetchAll ni joylashtiring, so’ng ushbu so’rov nomini Fn_FetchAll ga o’zgartiring (Query Settings → Name yoki Queries panelida sichqonchaning o’ng tugmasi → Rename).
Xuddi shuni Fn_GetJson uchun, so’ng har bir hisobot so’rovi uchun takrorlang.
Home → Close & Load → Close & Load To… Import Data oynasini ochadi. Fn_FetchAll va Fn_GetJson uchun Only Create Connection ni tanlang (ular jadval emas, funksiyalardir). Varaqda ko’rinishi kerak bo’lgan har bir hisobot so’rovi uchun Table ni tanlang.
Birinchi refresh paytida Basic ni tanlang: API kaliti User name sifatida, x kabi o’rinbosar qiymat Password sifatida. Excel har bir ma’lumotlar manbasi URL’i uchun bitta hisob ma’lumotini saqlaydi; xuddi shu /v1 hostiga murojaat qiladigan keyingi so’rovlar uni qayta ishlatadi.

Kitobda birinchi marta so’rov yaratilganda Close & Load To… xira bo’lishi mumkin. Close & Load dan foydalaning, so’ng Excel’da Data → Queries & Connections ni tanlang, so’rov ustida sichqonchaning o’ng tugmasini bosing va Load To ni tanlang. Bu yo’l Microsoft tomonidan ushbu holat uchun hujjatlashtirilgan.

Autentifikatsiyalangan manbada Power Query’dan Living-Security-Version yuborib bo’lmaydi — bu Power BI’dagi cheklov bilan bir xil. Xizmat hisobini qayd qiling yoki api_version ni so’rov yozuvida uzating. Contract versions and stored credentials bo’limiga qarang. Tayyor hisobot so’rovlari (ustunlar moslamasi va avvalgi provayder eksportidan farqlar):

Per-person compliance

Shaxs × topshiriq grain’idagi Training ro’yxatdan o’tishlari.

Email phishing log

Hodisa darajasidagi fishing o’zaro ta’sirlari, oxirgi 12 oy.

Most susceptible users

Har bir shaxs bo’yicha 12 oylik bosish, credential va xabar berish yig’indisi.

Reporting preview datasetlari (har bir shaxs bo’yicha fishing)

Reporting preview oilasi har bir shaxs bo’yicha fishing uchun datasetlarni taqdim etadi. U reporting:read hamda tashkilotingiz uchun reporting ruxsatini talab qiladi. Sxemalar va ma’lumotlar siyosatlarini ko’rish uchun quyidagi dataset katalogidan foydalaning. Joriy katalogni aniqlang:

GET /v1/reporting/datasets
GET /v1/reporting/datasets/simulation_results
GET /v1/reporting/datasets/simulation_events
GET /v1/reporting/datasets/entities
GET /v1/reporting/datasets/entity_versions
GET /v1/reporting/datasets/cohort_memberships

Har bir dataset grain, birlamchi kalit, bog’lanishlar, watermark, tuzatish va o’chirish siyosatini e’lon qiladi.

simulation_results

Yo’l: GET /v1/reporting/simulation_results
Grain: entity × simulyatsiya
Filtrlar: simulation, entity, updated_after, shuningdek limit / page_token
Wire ob’ekti: reporting.simulation_result
Bog’lovchi kalitlar: entity_id, simulation_id

ReportingSimulationResults.pq (sketch)

let
    Source = Fn_FetchAll(
        "reporting/simulation_results",
        [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "entity_id", "simulation_id", "status",
            "targeted_at", "delivered_at", "opened_at", "clicked_at",
            "credential_submitted_at", "reported_at",
            "email_subject", "sender_email", "difficulty_level",
            "created_at", "updated_at", "deleted_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",            type text },
            { "entity_id",     type text },
            { "simulation_id", type text },
            { "status",        type text },
            { "targeted_at",   type datetimezone },
            { "updated_at",    type datetimezone }
        })
in
    Typed

So’nggi partitsiyalarni yangilashda updated_after ni quyi watermark (chegarani o’z ichiga oladi) sifatida ishlating. O’zgaruvchan natija dimensiyalari o’sha id ostida joyida qayta yoziladi.

simulation_events

Yo’l: GET /v1/reporting/simulation_events
Grain: har bir kuzatilgan o’zaro ta’sir uchun bitta tiplangan fakt
Filtrlar: simulation, entity, event_type, occurred_after, shuningdek limit / page_token
Wire ob’ekti: reporting.simulation_event
Hodisa turlari: simulation.delivered, simulation.opened, simulation.link_clicked, simulation.credentials_submitted, simulation.reported kabi yopiq ommaviy tokenlar — ClickHouse’ning xom phishing.simulation.* faoliyat turlari emas
Qurilma: device_type — hosil qilingan sinf (kuzatilgan tokenlar orasida desktop, mobile, tablet, unknown bor). Xom user_agent /v1 da mavjud emas. Xom IP hech qachon /v1 da bo’lmaydi (saqlanmaydi; faqat tuzlangan ip_hash).

ReportingSimulationEvents.pq (sketch)

let
    Source = Fn_FetchAll(
        "reporting/simulation_events",
        [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "entity_id", "simulation_id",
            "event_type", "occurred_at",
            "email_subject", "sender_email", "sender_domain", "difficulty_tier",
            "device_type",
            "deleted_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",            type text },
            { "entity_id",     type text },
            { "simulation_id", type text },
            { "event_type",    type text },
            { "occurred_at",   type datetimezone }
        })
in
    Typed

Incremental hodisa yuklashlari uchun occurred_after dan foydalaning. Faktlar faqat qo’shiladi (append-only); kech kelgan yozuvlar yangi qatorlar bo’ladi.

entities

Yo’l: GET /v1/reporting/entities
Grain: har bir entity uchun bitta joriy qator
Filtrlar: type, updated_after, shuningdek limit / page_token
Wire ob’ekti: reporting.entity
Bog’lovchi kalit: id / entity_id (bir xil qiymat)

Fishing faktlarini odamlar bilan reporting.simulation_results.entity_id = reporting.entities.id orqali bog’lang. Operatsion GET /v1/entities entity:read da qoladi va bu boshqa ruxsatdir.

ReportingEntities.pq

let
    Source = Fn_FetchAll("reporting/entities", [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "entity_id", "type", "status",
            "display_name", "created_at", "updated_at", "deleted_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",          type text },
            { "entity_id",   type text },
            { "type",        type text },
            { "status",      type text },
            { "display_name", type text },
            { "created_at",  type datetimezone },
            { "updated_at",  type datetimezone }
        })
in
    Typed

entity_versions va cohort_memberships

Ushbu ikki dataset nomlangan, faqat joriy holatni ko’rsatuvchi istisnoga ega:

Versiyalar yoki a’zoliklar tarixining ishonchli jadvali mavjud emas. Ushbu dataset joriy qatorni bitta ochiq interval sifatida aks ettiradi (valid_to null, is_current true). valid_from entity versiyalari uchun created_at yoki aks holda updated_at bo’ladi, Cohort a’zoliklari uchun esa so’rovning kuzatuv vaqti bo’ladi. Yopiq tarixiy intervallar o’ylab topilmaydi.

entity_versions: GET /v1/reporting/entity_versions. Filtrlar entities bilan bir xil. valid_from — created_at yoki aks holda updated_at. Ikkala vaqt belgisi ham null bo’lgan entity chiqarib tashlanadi.
cohort_memberships: GET /v1/reporting/cohort_memberships. Filtrlar: cohort, entity, updated_after. updated_at — Cohort’ning oxirgi yangilanishi, shunda incremental refresh to’g’ri bo’lib qoladi. valid_from — so’rovning kuzatuv vaqti. entity_id ni reporting.entities.id bilan bog’lang.

ReportingEntityVersions.pq

let
    Source = Fn_FetchAll("reporting/entity_versions", [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "entity_id", "type", "status", "display_name",
            "valid_from", "valid_to", "is_current",
            "created_at", "updated_at", "deleted_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",         type text },
            { "entity_id",  type text },
            { "valid_from", type datetimezone },
            { "is_current", type logical }
        })
in
    Typed

ReportingCohortMemberships.pq

let
    Source = Fn_FetchAll("reporting/cohort_memberships", [ limit = "500" ]),

    AsTable = Table.FromRecords(
        Source,
        {
            "id", "object", "cohort_id", "entity_id",
            "valid_from", "valid_to", "is_current",
            "updated_at", "deleted_at"
        },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",         type text },
            { "cohort_id",  type text },
            { "entity_id",  type text },
            { "valid_from", type datetimezone },
            { "is_current", type logical },
            { "updated_at", type datetimezone }
        })
in
    Typed

/v1/activities asosidagi star-schema semantik modeli

/v1/activities — tashkilotning vaqt belgili hodisalar oqimi. Unda son o’lchovlari yo’q — hisobotdagi har bir miqdor qatorlar soni hisoblanadi — bu star schema uchun tabiiy mos keladi.

Dates (dimension)
      │  1
      ▼  ∗
  Activities (fact)   1  ∗  ActivityParticipants (bridge)   ∗  1   Entities (dimension)

Activities — haqiqiy grain’dagi hodisa fakti. Har bir faoliyat uchun bitta qator. Ishtirokchilar bu yerdan chiqarib tashlanadi va alohida jadval sifatida qayta quriladi.
ActivityParticipants mavjud, chunki ishtirokchilar haqiqatan ham bir-ko’pga aloqadorlikda bo’lib, actor va target kabi rollarga ega. Activities ga tekislash yo faoliyat qatorlarini takrorlab (har bir sanoqni oshirib), yo qo’shimcha ishtirokchilarni yo’qotishga olib keladi. Activities bilan aloqa modeldagi yagona ikki yo’nalishli aloqadir, shuning uchun entity slicer fakt jadvaliga yetib boradi.
Entities — ishtirokchilarni nomlaydigan dimensiya. Faoliyatdagi ishtirokchi qatorlarida entity_id va rol bor, ko’rsatiladigan nom esa yo’q.
Dates M’da yaratiladi (API chaqiruvlarisiz) va modelning sana jadvali sifatida belgilanadi. Aynan shu DAX time intelligence (DATESINPERIOD, DATEADD) ni amal qiladigan qiladi. Oy va hafta kuni nomi ustunlari o’zlarining raqamli juftlarida sortByColumn ni e’lon qiladi, shunda vizualizatsiyalar oylarni alifbo tartibida saralamaydi.

Barcha o’lchovlar aniq ko’rsatiladi (model discourageImplicitMeasures ni o’rnatadi): faoliyatlarning umumiy soni, faol entitylar, bitta faol entity uchun faoliyatlar, faoliyat bo’lgan kunlar va 7 kunlik kunlik o’rtacha qiymat.

Bridge jadvali shakli

ActivityParticipants /v1/activities ni aylanib chiqadi va participants massivini har bir ishtirokchi uchun bitta qatorga yoyadi:

ActivityParticipants.pq

let
    WindowStart = DateTime.AddZone(RangeStart, 0),
    WindowEnd   = DateTime.AddZone(RangeEnd, 0),
    ToIso = (value as datetimezone) as text =>
        DateTimeZone.ToText(value,
            [ Format = "yyyy-MM-dd'T'HH:mm:ss'Z'", Culture = "en-US" ]),

    Source = Fn_FetchAll(
        "activities",
        [
            limit      = "100",
            start_date = ToIso(WindowStart - #duration(0, 0, 1, 0)),
            end_date   = ToIso(WindowEnd + #duration(0, 0, 1, 0))
        ]),

    AsTable = Table.FromRecords(
        Source,
        { "id", "object", "activity_type", "event_ts",
          "service_id", "participants" },
        MissingField.UseNull),

    Selected = Table.SelectColumns(AsTable, { "id", "event_ts", "participants" }),
    Renamed  = Table.RenameColumns(Selected, { { "id", "activity_id" } }),
    Expanded = Table.ExpandListColumn(Renamed, "participants"),
    NonNull  = Table.SelectRows(Expanded, each [participants] <> null),
    Fields   = Table.ExpandRecordColumn(
        NonNull, "participants",
        { "role", "entity_id", "display_name" }),

    Typed = Table.TransformColumnTypes(
        Fields,
        {
            { "activity_id",  type text },
            { "event_ts",     type datetimezone },
            { "role",         type text },
            { "entity_id",    type text },
            { "display_name", type text }
        }),

    Filtered = Table.SelectRows(
        Typed,
        each [event_ts] >= WindowStart and [event_ts] < WindowEnd)
in
    Filtered

ActivityParticipants Activities so’rovига murojaat qilmasdan, /v1/activities ni mustaqil ravishda aylanib chiqadi. Power BI’da ikkita jadval sifatida yuklangan ikkita so’rov refresh vaqtida har biri mustaqil ma’lumot oladi. Ularni mustaqil saqlash xizmatga ikkala jadval partitsiyalarini parallel yangilash imkonini beradi.

Incremental refresh

Ikkala fakt grain’idagi jadval (Activities va ActivityParticipants) Power BI incremental refresh siyosatini e’lon qiladi: 5 yillik yillik partitsiyalarni saqlash, har bir refresh’da so’nggi 14 kunni qayta import qilish. Mexanizm — RangeStart va RangeEnd DateTime parametrlari jufti bo’lib, Power BI service ularni refresh vaqtida har bir partitsiya uchun qayta bog’laydi. Har bir fakt so’rovi ushbu parametrlarni API’ning start_date va end_date filtrlariga joylaydi, shuning uchun partitsiya darajasidagi so’rovlar hajmi butun tarixga emas, partitsiyaga mutanosib bo’ladi. So’rov API oynasini har tomondan bir daqiqaga kengaytiradi va aniq yarim ochiq [RangeStart, RangeEnd) chegarasini lokal ravishda qayta qo’llaydi, shuning uchun partitsiya to’g’riligi server chegaralarini o’z ichiga oladimi yoki yo’qligiga bog’liq emas.

Activities.pq — the fold that makes incremental refresh work

let
    WindowStart = DateTime.AddZone(RangeStart, 0),
    WindowEnd   = DateTime.AddZone(RangeEnd, 0),
    ToIso = (value as datetimezone) as text =>
        DateTimeZone.ToText(value,
            [ Format = "yyyy-MM-dd'T'HH:mm:ss'Z'", Culture = "en-US" ]),

    Source = Fn_FetchAll(
        "activities",
        [
            limit      = "100",
            start_date = ToIso(WindowStart - #duration(0, 0, 1, 0)),
            end_date   = ToIso(WindowEnd + #duration(0, 0, 1, 0))
        ]),

    AsTable = Table.FromRecords(
        Source,
        { "id", "object", "activity_type", "event_ts",
          "service_id", "participants" },
        MissingField.UseNull),

    Dropped = Table.RemoveColumns(AsTable, { "object", "participants" }),

    Typed = Table.TransformColumnTypes(
        Dropped,
        {
            { "id",            type text },
            { "activity_type", type text },
            { "event_ts",      type datetimezone },
            { "service_id",    type text }
        }),

    Filtered = Table.SelectRows(
        Typed,
        each [event_ts] >= WindowStart and [event_ts] < WindowEnd),

    WithDate = Table.AddColumn(
        Filtered, "event_date", each Date.From([event_ts]), type date)
in
    WithDate

Deploy’dan keyingi birinchi refresh siyosatni qo’llaydi: to’liq tarixiy yuklash, sekin. Undan keyingi har bir refresh faqat incremental oynaga tegadi. ~158 000 faoliyat va ~2 300 entity’dan iborat datasetda o’lchangan:

Refresh	Real vaqt
Birinchi (siyosatni qo’llash, to’liq tarixiy yuklash)	~31 daq
Undan keyingi har bir refresh	~15 s

Power BI’ning klassik refresh REST endpointi (faqat notifyOption bilan POST .../refreshes) refresh siyosatlarini jimgina e’tiborsiz qoldiradi va har safar to’liq o’tishni bajaradi. Siyosatni REST API orqali qo’llash uchun enhanced refresh’dan foydalaning: tanada type, commitMode va applyRefreshPolicy ni yuboring. Power BI Service rejalashtiruvchisi ishga tushirgan refresh’lar siyosatlarni doim qo’llaydi — bu xususiyat faqat REST chaqiruviga xos.

14 kunlik oyna ataylab keng olingan. Faoliyatlarni qabul qilish faqat “hozirgi vaqtga qo’shish” tarzida emas: connector backfill’lari hodisalarni o’tmishdagi vaqt belgilari bilan yuklaydi. 1–2 kunlik oyna ularni butunlay o’tkazib yuborgan bo’lardi.

Refresh oynasini API’ga kengaytirish

GET /v1/activities Power BI parametrlari joylanadigan start_date va end_date filtrlarini qabul qiladi va event_ts ni chegaralar orasida filtrlaydi. Entities — dimensiya, shuning uchun ushbu namuna uni incremental emas, har safar to’liq yangilaydi. Reporting datasetlari faoliyatlar sana oynasi o’rniga o’zlarining watermarklaridan (updated_after, occurred_after) foydalanadi.

Refresh vaqtidagi rate limitlar

/v1 har bir kalit va har bir IP uchun alohida budjetlarni qo’llaydi (rate limits bo’limiga qarang). 429 + Retry-After ga rioya qiling; so’rovlar budjetini modelga qat’iy yozmang. Power BI refresh’larining ikki xususiyati muhim:

Bitta jadvalni o’qish ketma-ket bajariladi. Har bir sahifa so’rovi oldingi sahifa tokenini talab qiladi, shuning uchun sahifa hajmidan qat’i nazar, bitta jadval kechikish bilan cheklanadi.
Bir vaqtdagi jadval refresh’lari ko’payadi. Bir nechta yirik jadvalning parallel yangilanishi chegaraga urilish holatidir, shuning uchun Fn_FetchAll dagi 429 qayta urinish yo’li muhim. Cheksiz parallellikni taxmin qilgandan ko’ra, kamroq parallel jadvallarni afzal ko’ring.

Qayta urinish haqiqiy to’yingan limiter bilan sinovdan o’tkazilgan. Parallel yuklama kalitni ataylab chegarada ushlab turgan paytda ishlagan refresh baribir yakunlandi va cheklovsiz bazaviy holat bilan bir xil qatorlar sonini berdi — qayta urinishlar bo’shliqlarni emas, to’liq sahifalarni qaytardi.

Kontrakt versiyalari va saqlangan hisob ma’lumotlari

/v1 e’lon qilingan kontrakt versiyasini qayd qilish uchun Living-Security-Version so’rov headerini qabul qiladi (API quickstart bo’limiga qarang). Ma’lumotlar manbasi saqlangan hisob ma’lumotlaridan foydalansa, Power Query bu headerni yubora olmaydi — maxsus headerlarga faqat anonim ma’lumotlar manbalari ruxsat beradi. Uni qo’shish refresh’ni quyidagi xato bilan muvaffaqiyatsiz tugatadi:

Expression.Error: The 'Living-Security-Version' header is only supported when
connecting anonymously.
Microsoft.Data.Mashup.ErrorCode = 10348.

Kalitni Power BI’ning saqlangan hisob ma’lumotlarida qoldiring va maxsus headerni qo’shmang. So’rovda aniq versiya ko’rsatilmasa, API xizmat hisobining qayd qilingan contract_version versiyasidan yoki qayd qilinmagan bo’lsa, e’lon qilingan so’nggi versiyadan foydalanadi. Refresh’ni e’lon qilishdan oldin hisobning kontrakt versiyasini API administratoringiz bilan tasdiqlang. Qo’llab-quvvatlanadigan versiyalar uchun API changelog bo’limiga qarang. Agar refresh kutilmagan tarzda ishlay boshlasa, so’rovga aynan qaysi kontrakt xizmat ko’rsatganini bilish uchun API’ning Living-Security-Version javob headerini tekshiring.

Modelingizni yangilang va tekshiring

Semantik modelni Power BI workspace’ingizga e’lon qiling, saqlangan hisob ma’lumotini mintaqaviy /v1 manbasiga bog’lang va jadvalni yoqishdan oldin refresh’ni ishga tushiring. Refresh tarixida xatolar yoki to’liq bo’lmagan o’qishlar bor-yo’qligini ko’rib chiqing. Qatorlar sonini xuddi shu filtrlar bilan API’dan mustaqil o’qish natijasi bilan solishtiring. Pagination guide to’liq natijalar to’plamini o’qish uchun Python va cURL namunalarini o’z ichiga oladi. Faqat birinchi sahifani o’qiydigan refresh kichik datasetda muvaffaqiyatli ko’rinishi mumkin; hisobotga tayanishdan oldin sonlarni solishtiring.

Model bo’yicha tanlovlar

Kampaniya yig’indilari va har bir shaxs natijalari turli grain’ga ega. SimulationResults GET /v1/simulations/{id}/results ni chaqiradi (har bir ishga tushirish uchun bitta GET). Har bir shaxs bo’yicha fishing uchun GET /v1/reporting/simulation_results va GET /v1/reporting/simulation_events (yoki agar sizda simulation:read bo’lsa, operatsion GET /v1/simulation_results) uchun so’rovlar qo’shing.
/v1/simulations dagi vectors vergul bilan birlashtirilgan satrga tekislanadi. Ro’yxat tipidagi ustunni haqiqiy dimensiya sifatida slice qilib bo’lmaydi. Agar hisobot sahifasi vektor bo’yicha filtrlashi kerak bo’lsa, simulyatsiya-vektor bridge jadvalini qo’shing.

Keyingi qadamlar

Developer Hub

Power BI uchun xizmat hisobi va API kaliti yarating.

API quickstart

/v1 so’rovlarini Basic auth orqali autentifikatsiya qiling.

Pagination

Ro’yxat envelope’i, kursor tokenlari va o’tish davomida filtr barqarorligi.

Rate limits

Har bir kalit va har bir IP uchun budjetlar, 429 kontrakti va qayta urinish bo’yicha tavsiyalar.

Dasturchilar markazi

Ochiq API v1

API v1 ma'lumotnomasi

Ushbu namuna nima uchun mo’ljallangan

Talablar

To’g’ri grain’ni tanlang

Qiyinlik shkalasi

Ulanish namunasi (Power Query)

Har bir endpoint uchun tiplangan so’rovlar

Excel

Per-person compliance

Email phishing log

Most susceptible users

Reporting preview datasetlari (har bir shaxs bo’yicha fishing)

simulation_results

simulation_events

entities

entity_versions va cohort_memberships

/v1/activities asosidagi star-schema semantik modeli

Bridge jadvali shakli

Incremental refresh

Refresh oynasini API’ga kengaytirish

Refresh vaqtidagi rate limitlar

Kontrakt versiyalari va saqlangan hisob ma’lumotlari

Modelingizni yangilang va tekshiring

Model bo’yicha tanlovlar

Keyingi qadamlar

Developer Hub

API quickstart

Pagination

Rate limits

Dasturchilar markazi

Ochiq API v1

API v1 ma'lumotnomasi

​Ushbu namuna nima uchun mo’ljallangan

​Talablar

​To’g’ri grain’ni tanlang

​Qiyinlik shkalasi

​Ulanish namunasi (Power Query)

​Umumiy pagination funksiyasi

​Har bir endpoint uchun tiplangan so’rovlar

​Excel

Per-person compliance

Email phishing log

Most susceptible users

​Reporting preview datasetlari (har bir shaxs bo’yicha fishing)

​simulation_results

​simulation_events

​entities

​entity_versions va cohort_memberships

​/v1/activities asosidagi star-schema semantik modeli

​Bridge jadvali shakli

​Incremental refresh

​Refresh oynasini API’ga kengaytirish

​Refresh vaqtidagi rate limitlar

​Kontrakt versiyalari va saqlangan hisob ma’lumotlari

​Modelingizni yangilang va tekshiring

​Model bo’yicha tanlovlar

​Keyingi qadamlar

Developer Hub

API quickstart

Pagination

Rate limits

Ushbu namuna nima uchun mo’ljallangan

Talablar

To’g’ri grain’ni tanlang

Qiyinlik shkalasi

Ulanish namunasi (Power Query)

Umumiy pagination funksiyasi

Har bir endpoint uchun tiplangan so’rovlar

Excel

Reporting preview datasetlari (har bir shaxs bo’yicha fishing)

simulation_results

simulation_events

entities

entity_versions va cohort_memberships

/v1/activities asosidagi star-schema semantik modeli

Bridge jadvali shakli

Incremental refresh

Refresh oynasini API’ga kengaytirish

Refresh vaqtidagi rate limitlar

Kontrakt versiyalari va saqlangan hisob ma’lumotlari

Modelingizni yangilang va tekshiring

Model bo’yicha tanlovlar

Keyingi qadamlar