One Day BI
ONE DAY BI

Blog

Technical insights, industry trends, and lessons from our data transformation journey.

Skills: Shared Knowledge Without Duplicated Instructions

SelfServiceBI· 04 Mar 2026

As I progressed with my project of building an automated data engineering solution, I got more and more frustrated that every time I tried to repeat a step the outcome could be totally different than the last time. I needed to tame the nondeterministic nature of LLMs and create a more consistent output and handover between agents. The business analyst agent should always profile the source data the same way. The test writer should always apply the same coverage rules. The fact builder should always generate surrogate keys using the same pattern. The naive approach was to duplicate those shared instructions across every agent definition. My data-profiler skill, for example, contains the logic for detecting primary key candidates, calculating column statistics, identifying data quality issues, and recommending dbt tests. Without skills, that entire block would need to live inside the business analyst agent, staging builder agent, the test writer agent, and any other agent that needs to understand source data — repeated verbatim each time. That duplication is both a token problem and a consistency problem. Update the profiling logic in one agent, forget to update it in another, and you get different results from the same data. Skills solve this. They are on-demand instruction modules that any agent can invoke when needed. Define the profiling logic once as a skill, reference it from every agent that needs it, and the instructions load only when that agent actually profiles data — not at startup, not in every session, not duplicated across definitions. This is Part 2 of the Context Window Optimization series, and it is about making specialized knowledge modular, consistent, and token-efficient. ℹ️ Note: This is Part 2 of 6 in the “Context Window Optimization” series. While examples use Claude Code, the pattern of “on-demand knowledge loading” applies broadly to any agentic workflow where instruction overhead is a concern. Quick Recap: Where We Are in the Series Part 0 - Hidden Cost of MCPs and Custom Instructions on your context window established that a typical Claude Code session burns roughly 51% of the 200K token context window before any real work begins — MCP tools alone consume 16%, and memory files add another 2.7%. That cost multiplies across every project you work in. Part 1 - Subagent - How delegating work solves the context window problem showed how subagents solve dynamic overhead — the tokens consumed by work in progress — by isolating verbose task output in separate context windows. Each specialized agent gets a narrow focus, reducing drift and hallucinations. This post tackles static overhead from duplicated knowledge: the same instructions repeated across multiple agent definitions, bloating every session and drifting out of sync over time. Skills make that knowledge modular and on-demand. What Are Skills? Skills are reusable instruction modules stored as markdown files. When a skill is invoked—either by you typing /skill-name or by Claude detecting relevance—its full content loads into context. Until then, only the skill’s description is present (a few dozen tokens), not the full instruction set. Think of CLAUDE.md as a whiteboard always visible in the room. Skills are a filing cabinet: the labels are always readable, but you only pull out a folder when you actually need it. Skills are stored in .claude/skills/skill-name/SKILL.md (project-level) or ~/.claude/skills/skill-name/SKILL.md (user-level). Each skill has YAML frontmatter followed by instruction content, and can include supporting files like templates, examples, and scripts. Here’s a different skill in action — /frontend-design loads its full instructions on invocation and responds with context-aware suggestions: Skills vs. CLAUDE.md vs. Subagents The three mechanisms solve different problems and complement each other: Feature CLAUDE.md Subagents Skills When loaded At startup When delegated On demand Context window Main Separate, isolated Main, inline Content in context Always, fully loaded Always, in own window Only when invoked Startup cost Full file loads Nothing until spawned Description only (~40 tokens) Reduces static overhead No No Yes Reduces dynamic overhead No Yes No Best for Core project rules Verbose builds, test runs Shared knowledge, templates Subagents keep your context clean by isolating work output. Skills keep your context lean by loading knowledge only when relevant. Anatomy of a Skill File A skill lives in its own directory with SKILL.md as the required entrypoint: .claude/skills/ └── data-profiler/ ├── SKILL.md # Main instructions (required) └── scripts/ └── profile_data.py # Profiling script Notice the scripts/ directory. Skills go beyond shared instructions — they can bundle executable code. My data-profiler skill includes a Python script (profile_data.py) that every agent calls through Bash — same script, same parameters, same output format every time. The LLM doesn’t interpret profiling instructions and hope for consistency; it runs the script and gets deterministic results. The SKILL.md tells the agent when and why to profile; the script handles the how. --- name: data-profiler description: > Automatically profile SQL Server tables and CSV files with intelligent analysis. Detects primary key candidates, infers data types from CSV data, calculates column statistics (nulls, cardinality, data types), identifies data quality issues, and recommends appropriate dbt tests. Use when exploring source data, creating staging models, or validating data quality before transformation. disable-model-invocation: false allowed-tools: Read, Write, Bash, Grep, Glob --- The name field becomes the /slash-command (defaults to directory name if omitted). The description is what Claude reads to decide when to auto-invoke—write it precisely. allowed-tools restricts which tools Claude can use when the skill is active — this profiler needs: Read and Grep for source exploration, Bash to run the profiling script, Write to save profile reports to disk. No Edit since it generates new files rather than modifying existing ones. disable-model-invocation and user-invocable (covered below) control who triggers the skill. How Invocation Works When a skill is defined, only its description sits in context—roughly 30-60 tokens. The full content loads only on invocation, whether triggered by you or by Claude’s auto-detection. State What’s in context Skill defined, not invoked Description only (~30-60 tokens) Skill invoked by you (/skill-name) Full SKILL.md content loads Skill auto-invoked by Claude Full SKILL.md content loads One exception: subagents with preloaded skills inject full skill content at startup because they start fresh without conversation history. Control Who Can Invoke a Skill Two frontmatter flags give precise control. disable-model-invocation: true means only you can invoke the skill by typing /skill-name—use this for workflows with side effects you want to control explicitly, like /deploy or /send-release-notes. user-invocable: false hides the skill from the / menu so only Claude can invoke it—useful for background knowledge like naming conventions that Claude should apply silently. A Practical Walkthrough: Converting CLAUDE.md Sections to Skills Here’s a section that was living in my CLAUDE.md: ## Data Profiling Rules When profiling source data, always follow this process: ### Connection - Server: localhost, Database: Agentic - Authentication: SQL Server Authentication (read-only user) - CSV files: standard format with header row from `2 - Source Files/` ### What to Analyze - Data type and precision for every column - Null count and percentage - Distinct value count and cardinality - Min/max values for numeric and date columns - Primary key candidates: 100% distinct + 0% nulls - Foreign key candidates: column name ends with `_id` or `_key` ### Test Recommendations - Primary keys: `unique` + `not_null` - Low cardinality columns (< 10 values): `accepted_values` - Foreign keys: `relationships` to parent table - Required fields: `not_null` ### Output - Save profiles to `1 - Documentation/data-profiles/` - Use JSON format for agent consumption - Include: table stats, column profiles, quality issues, recommendations - Generate dbt YAML scaffold with recommended tests That section is roughly 300 tokens, loading every conversation regardless of whether data profiling is involved. The migration: create .claude/skills/data-profiler/, move the content into SKILL.md with the YAML frontmatter shown in the Anatomy section, delete the section from CLAUDE.md. The skill’s description (~117 tokens) replaces the full 300 tokens at startup — and the instructions only load when an agent actually needs to profile data. The result: every agent that invokes the data-profiler skill gets the same profiling logic, the same output format, the same test recommendations — without any of them carrying those instructions at startup. And every profile gets saved to the same location, accessible to all agents without re-profiling: Before and After: Token Savings I applied this same migration pattern across my project — moving the data profiling rules, blog writing template, and several other instruction blocks out of CLAUDE.md and into skills. Here’s what the startup cost looks like now:   Before (in CLAUDE.md) After (as skill) Data profiling rules ~300 tokens ~117 tokens (description only) Deployment checklist ~250 tokens ~30 tokens Code review guidelines ~350 tokens ~35 tokens Release notes template ~200 tokens ~30 tokens Total for these sections ~1,100 tokens ~212 tokens That’s an 81% reduction in startup overhead for these four sections alone. The full skill content only loads when you’re actually doing that type of work — skills you don’t touch stay cold. Skill Design Principles A few clear rules for what belongs where: Skills: specialized domain knowledge for a subset of your work (dbt conventions, data modelling best practices, deployment checklists); step-by-step workflows you want to invoke deliberately; templates and output formats that are token-heavy but situational CLAUDE.md: universal project rules; short frequently-referenced facts; meta-instructions about how Claude should behave Rough test: if you’d say “I only care about this on release days,” it belongs in a skill For large reference material, keep SKILL.md under 500 lines and reference supporting files from within the skill directory. Skills with Arguments Skills can accept arguments when invoked. Running /fix-issue 847 substitutes $ARGUMENTS with 847 throughout the skill content; individual arguments are accessible as $0, $1, etc. --- name: fix-issue description: Fix a GitHub issue by number disable-model-invocation: true --- Fix GitHub issue $ARGUMENTS following our project coding standards. 1. Read the issue description using `gh issue view $ARGUMENTS` 2. Understand the requirements 3. Implement the fix in the appropriate files 4. Write tests covering the change 5. Create a commit following our commit message conventions Running Skills in Isolation: context: fork Setting context: fork runs the skill inside an isolated subagent instead of inline—combining knowledge-on-demand with context isolation. The skill content becomes the subagent’s task prompt, and results come back as a concise summary to your main conversation. --- name: deep-research description: Research a topic thoroughly using codebase exploration context: fork agent: Explore --- Research $ARGUMENTS thoroughly: 1. Find relevant files using Glob and Grep 2. Read and analyze the code 3. Summarize findings with specific file references 4. Return a concise summary for the main conversation Limitations Skill descriptions consume a shared budget. Claude Code loads all descriptions at 2% of the context window (~4,000 tokens for 200K)—if you create many skills, some may be excluded. Run /context to check, and keep descriptions concise. Skills are not magic compression. Invoking five large skills in one conversation loads all their content—savings come from not loading skills you don’t use, not from reducing the ones you do. Auto-invocation can surprise you. Without disable-model-invocation: true, Claude may load a skill automatically when its description matches—make descriptions specific if you get unexpected invocations. context: fork skills don’t have conversation history. Forked subagent skills start clean and need any relevant context passed explicitly as arguments. Key Takeaways Skills are the on-demand alternative to always-loaded memory files—only descriptions load at startup; full content loads only when invoked. The savings come from specialization—instructions that apply to 20% of your work shouldn’t consume 100% of startup overhead. Start by auditing your CLAUDE.md—look for blocks of specialized instructions that only apply in specific contexts; those are your skill candidates. Design for invocation clarity—use disable-model-invocation: true for deliberate workflows, user-invocable: false for background knowledge, and default settings for knowledge you’re happy to invoke either way. Skills and subagents are complementary—subagents reduce dynamic overhead (what work produces), skills reduce static overhead (what knowledge you carry). Getting Started: Need Inspiration? You don’t have to start from scratch. Anthropic maintains an open-source skills repository with ready-to-use skills you can drop into your .claude/skills/ directory. It includes a template/ folder showing the recommended structure, a spec/ folder documenting the skill format, and a growing skills/ collection contributed by the community. Browse the repo for patterns worth adopting — or fork it and contribute your own. If you’ve built a skill that solves a common problem (data profiling, code review, deployment checklists), others are likely duplicating the same instructions you just extracted. What’s Next Skills and subagents keep your context lean during work — but the instructions that load before work begins matter just as much. Part 3 covers CLAUDE.md files — the global, project, and folder-level instruction hierarchy. A bloated CLAUDE.md taxes every conversation. Part 3 shows how to split instructions across levels so each file stays focused, nothing gets duplicated, and your agent behaves consistently across every repo. Resources Official Documentation Extend Claude with Skills - Claude Code Docs Anthropic Skills Repository - GitHub Create Custom Subagents - Claude Code Docs Memory Files (CLAUDE.md) - Claude Code Docs Claude Code Best Practices - Anthropic Engineering Previous Posts in This Series The Hidden Cost of MCPs and Custom Instructions on Your Context Window Subagents: How Delegating Work Solves the Context Window Problem Community Resources Agent Skills Open Standard (agentskills.io) Claude Code Skills: Structure and Invocation — Mikhail Shilkov Have you started breaking your CLAUDE.md into skills? What instruction blocks did you find were most worth extracting? I’d love to hear what works in your setup—and what surprised you about the process.

Read on selfservicebi.co.uk

Subagents: How Delegating Work Solves the Context Window Problem

SelfServiceBI· 02 Mar 2026

I was experimenting building an automated dbt data engineering solution—a SQL Server + dbt + Power BI pipeline—and I wanted Claude Code to help me build it layer by layer. The problem I kept running into: the agent would drift. When I asked it to build staging models, it would start making assumptions about fact table logic. When writing tests, it would forget the naming conventions from three messages earlier. Errors multiplied. The work got messier the longer the session ran. My first instinct was to write a more detailed CLAUDE.md. But the real issue wasn’t instructions—it was focus. A single agent trying to hold the entire dbt project in its head at once was too much context, too many concerns at the same time. That’s when I started creating specialized subagents: one for staging models, one for fact tables, one for writing tests. Each agent got a narrow, specific set of instructions. The hallucinations dropped dramatically. And as a bonus I didn’t expect—my main context window stayed clean, letting me sustain longer and higher quality sessions. In my previous post about MCPs and custom instructions, I covered static overhead—the 51% of your context window that disappears before you type a single message. This post is about the second problem: dynamic overhead, the tokens consumed by the actual work. And how subagents solve both. ℹ️ Note: This is Part 1 of 6 in the “Context Window Optimization” series. While examples use Claude Code, these concepts apply broadly to any AI agent system that supports task delegation. Quick Recap: The 50% Tax The previous post showed how a typical Claude Code setup burns roughly 51% of the 200K token context window before any real work begins: Category Tokens % of Window System prompt 3.0k 1.5% System tools 14.8k 7.4% MCP tools 32.6k 16.3% Memory files 5.4k 2.7% Autocompact buffer 45.0k 22.5% Free space 99k 49.3% That’s static overhead—it happens just from launching Claude Code. But the dynamic overhead from real work adds up just as fast. Reading 20 files to understand a codebase? That’s ~40,000 tokens. Running a test suite with verbose output? Another 15,000. Combine both types and you can hit 80%+ context usage without writing a single line of implementation code. This is why long sessions often degrade. It’s not the AI getting tired—it’s arithmetic. What Are Subagents? Subagents are specialized AI assistants that run in their own isolated 200K token context window. When Claude delegates a task to a subagent, that work happens inside the subagent’s context—all the file reads, search results, and command output stay there. Only a concise summary returns to your main conversation. Think of it like offloading work to a separate process. Each process has its own memory space. Only the final result comes back. Without subagents: Main conversation reads 25 files to understand a module Those files consume ~40,000 tokens in your main context That’s 40% of your remaining working space, gone With subagents: Subagent reads 25 files in its own 200K context window Subagent returns a 500-token summary to your main conversation Main context barely moves You get the same information. Your main context stays clean. Built-in Subagent Types Claude Code ships with several built-in subagents: Type Model Access Best For Explore Haiku (fast/cheap) Read-only Codebase searches, finding patterns, understanding structure Plan Inherits from main Read-only Research during planning mode (/plan) General-purpose Inherits from main Full read/write Tasks requiring both exploration and code modification Bash Inherits from main Terminal access Running builds, tests, git operations The Explore agent is particularly useful for the dynamic overhead problem. When you ask Claude to understand how a module works, it can delegate that research to the Explore agent (running on fast, cheap Haiku). The Explore agent reads all the relevant files and returns a summary—keeping potentially tens of thousands of tokens of file content out of your main context. The Bash agent handles terminal verbosity. A full test suite might produce 15,000 tokens of output. The Bash agent processes that and returns a 300-token summary of which tests failed and why. Foreground vs Background Subagents run in two modes: Foreground (default): Blocks your main conversation until complete. You can watch progress in real time, and any permission prompts pass through to you for approval. Best for focused tasks where you want to see what’s happening. Background: Runs concurrently while you keep working. Permissions are requested upfront before the agent starts, then auto-denied for anything not pre-approved. Use /tasks to check status. Best for long-running work—web research, large test suites, multi-file builds. To run a background agent: ask Claude to “run this in the background,” or press Ctrl+B to background a task that’s already running. ⚠️ Background agents do not have access to MCP tools. If your task requires an MCP server (database queries, browser automation), use a foreground agent instead. ⚠️ Custom Subagents: The dbt Example Built-in subagents cover generic workflows. For specialized projects, you can define your own. Custom subagents are markdown files with YAML frontmatter stored in .claude/agents/ (project-level) or ~/.claude/agents/ (user-level, available across all projects). Going back to my dbt pipeline: I created a dedicated subagent for building staging models. Here is the actual frontmatter and opening from my dbt-staging-builder agent: --- name: dbt-staging-builder description: > Build staging models (stg_*) that transform raw source data with basic cleaning, renaming, and type casting. Create source definitions in YAML with freshness checks. Handle null values and standardize column names. Use when creating the first transformation layer from raw source tables. tools: Read, Write, Edit, Grep, Glob model: haiku skills: dbt-runner, data-profiler, sql-server-reader --- # Staging Builder Agent You are a specialist in creating staging models (stg_*) - the first transformation layer in dbt projects. Two things stand out in this frontmatter: model: haiku — this subagent runs on the fastest, cheapest model. Staging models follow rigid patterns (rename columns, cast types, filter nulls). Haiku handles that well. No need to pay for Opus reasoning on templated SQL. skills: dbt-runner, data-profiler, sql-server-reader — the agent has access to three Skills (covered in Part 2) that give it on-demand access to dbt commands, data profiling, and database queries. Those skill definitions only load when the agent invokes them — they are not in the main context either. The full agent definition runs to nearly 400 lines. It contains column naming rules, a sanitization reference table, a standard staging SQL template, a profile-driven workflow, and a six-step development process. Here is a taste of the specificity: ## Staging Model Principles **What staging models DO**: - ✅ Select specific columns (no SELECT *) - ✅ Rename columns for consistency - ✅ Cast data types explicitly - ✅ Handle nulls with COALESCE - ✅ Filter out invalid records (null keys) **What staging models DON'T do**: - ❌ Join to other tables - ❌ Aggregate data - ❌ Add complex business logic - ❌ Create derived metrics And the naming convention section: ## Naming Convention **Model**: `stg_<source>__<entity>` - Examples: stg_erp__customers, stg_sales__orders - Double underscore separates source from entity **Columns**: - Primary keys: <entity>_id - Foreign keys: <related_entity>_id - Dates: <event>_date - Timestamps: <event>_at - Booleans: is_<condition> All 400 lines live inside the subagent’s context — not the main agent’s. When I ask Claude to “create a staging model for the customers table,” it spawns the staging builder agent, which loads all these instructions into its own 200K window, does the work (profile the source, write SQL, compile, run, test), and returns a concise summary. My main conversation sees something like: Created stg_erp__customers — 12 columns, materialized as view, 3 tests passing (unique, not_null on customer_id, not_null on customer_name). 4,230 rows. The compilation logs, run output, and test results (which can easily total 15,000+ tokens) stay isolated. My main conversation sees only the summary. Why this works for focus and quality: The subagent’s 400 lines of instructions cover staging models specifically — naming conventions, column sanitization rules, the CTE template, the right dbt commands. It does not know about fact tables or reporting layers. That narrow scope is what prevents the drift and hallucinations I experienced with a single all-knowing agent. Why this works for context: Every token of verbose dbt output stays in the subagent’s 200K window. My main context stays clear for the orchestration work — deciding what to build next, reviewing summaries, planning the next layer. Key Benefits of Custom Subagents Instructions live in the subagent’s context, not yours. Detailed staging model conventions, source system quirks, test patterns—all of that lives in the subagent’s system prompt. It doesn’t load into your main conversation until you invoke the agent. This is the opposite of stuffing everything into CLAUDE.md. Tool restrictions prevent accidents. You can limit a subagent to read-only tools if it’s only supposed to research. A code-review subagent with no write access can’t accidentally modify files. Model selection for cost control. The model: haiku field means this subagent runs on Haiku even if your main session uses Opus. Expensive Opus reasoning for coordination; cheaper Haiku for focused execution tasks. When to Delegate vs Stay in Main Context Not every task should be delegated. Subagents add startup latency and the subagent needs to gather its own context fresh each time. Delegate when: The task will produce verbose output you don’t need line-by-line (test results, build logs, grep output across many files) The work is self-contained with a clear deliverable Multiple independent research paths can run in parallel The task will take several minutes and you want to keep working Stay in main context when: You need tight back-and-forth iteration The task is quick and targeted (a small edit, a single question) The output of one step feeds directly into the next You need interactive clarification mid-task A useful heuristic: if a task will produce multiple pages of output you don’t need to read line by line, it’s a good candidate for delegation. Invocation quality matters Subagents start fresh — they don’t inherit your conversation history. A vague prompt like “build the staging model” forces the subagent to guess at which source table, which naming pattern, and which tests to add. A well-specified prompt tells the subagent what is unique to this task — not what is already in its instructions: Create a staging model for the raw.customers table in the ERP source. The primary key is customer_id. The email column has known nulls — use COALESCE with 'unknown'. Return the row count and test results. The agent already knows to profile the source, follow the stg_<source>__<entity> naming pattern, add primary key tests, and run the compile → run → test workflow. The prompt only needs to provide context the agent cannot infer: which table, which source system, and any data quirks specific to this table. This is where your project CLAUDE.md becomes valuable in a different way. You can instruct the main agent on how to delegate — telling it what context to pass when spawning subagents. For example, my CLAUDE.md includes a note to “always consider subagents and skills” and my standard implementation process (research → plan → tests → implement). The main agent reads those instructions and knows to provide the subagent with the right table name, source system, and any known data issues — rather than firing off a bare “build the staging model” prompt. The CLAUDE.md shapes the orchestrator; the agent definition shapes the worker. Limitations Subagents can’t spawn other subagents. There’s no nesting. If you need multiple subagents coordinating, that coordination happens from your main conversation. Background agents lose MCP access. Any task requiring an MCP tool (browser, database connection) must run as a foreground agent. Coordination overhead grows with scale. Running 3-4 parallel subagents works well. Beyond that, the coordination complexity and the summaries flowing back can start to add clutter of their own. Key Takeaways Subagents keep your main context clean. Verbose output—file reads, build logs, test results—stays isolated in the subagent’s 200K window. Only concise summaries return to your main conversation, preserving your working space. Narrow focus reduces hallucinations. A specialized subagent with domain-specific instructions stays on task and makes fewer mistakes than a general agent trying to hold everything at once. This was the original reason I started using them. Custom subagents move knowledge out of your CLAUDE.md. Instead of stuffing every project convention into a file that loads every session, put specialized instructions in subagents that load only when needed. Choose the right type. Explore for codebase research (fast Haiku, read-only). Bash for terminal-heavy workflows. General-purpose for tasks needing both exploration and implementation. Custom subagents for domain-specific work. Use background agents for long-running tasks. Research, test suites, and builds can run while you keep working. Press Ctrl+B to background a running task, and check status with /tasks. Delegate when the output is verbose and self-contained. The heuristic: >5,000 tokens of output you don’t need line-by-line is a strong signal to delegate. What’s Next: Skills Subagents solve the dynamic overhead problem—the tokens consumed by work in progress. But what about the static overhead from the previous post? A big chunk of that 51% baseline comes from CLAUDE.md and other memory files loaded into every session. What if you could make that modular—loading specialized knowledge only when you actually need it? That’s what Skills do. In the next part of this series, I’ll explore how Skills complement subagents by making static context modular. If subagents are about keeping work isolated, Skills are about keeping knowledge on-demand. Resources Official Documentation Create custom subagents - Claude Code Docs Context windows - Claude API Docs Research How we built our multi-agent research system - Anthropic Building Effective AI Agents - Anthropic How Input Token Count Impacts LLM Latency - Glean Previous Posts in This Series The Hidden Cost of MCPs and Custom Instructions on Your Context Window Have you started using subagents in your AI workflows? I’d especially love to hear from anyone building data engineering pipelines—what tasks do you delegate, and how have you structured your custom agents?

Read on selfservicebi.co.uk

The Hidden Cost of MCPs and Custom Instructions on Your Context Window

SelfServiceBI· 23 Nov 2025

Large context windows sound limitless—200K, 400K, even a million tokens. But once you bolt on a few MCP servers, dump in a giant CLAUDE.md, and drag a long chat history behind you, you can easily burn over 50% of that window before you paste a single line of code. This post is about that hidden tax—and how to stop paying it. Where This Started This exploration started when I came across a LinkedIn post by Johnny Winter featuring a YouTube video about terminal-based AI tools and context management. The video demonstrates how tools like Claude Code, Gemini CLI, and others leverage project-aware context files—which got me thinking about what’s actually consuming all that context space. Video by NetworkChuck ℹ️ Note: While this post uses Claude Code for examples, these concepts apply to any AI coding agent—GitHub Copilot, Cursor, Windsurf, Gemini CLI, and others. The Problem: You’re Already at 50% Before You Start Think of a context window as working memory. Modern AI models have impressive limits (as of 2025): Claude Sonnet 4.5: 200K tokens (1M beta for tier 4+) GPT-5: 400K tokens via API Gemini 3 Pro: 1M input tokens A token is roughly 3-4 characters, so 200K tokens equals about 150,000 words. That sounds like plenty, right? Here’s what actually consumes it: System prompt and system tools MCP server tool definitions Memory files (CLAUDE.md, .cursorrules) Autocompact buffer (reserved for conversation management) Conversation history Your code and the response being generated By the time you add a few MCPs and memory files, a large chunk of your context window is already gone—before you’ve written a single line of code. Real Numbers: The MCP Tax Model Context Protocol (MCP) servers make it easier to connect AI agents to external tools and data. But each server you add costs tokens. Here’s what my actual setup looked like (from Claude Code’s /context command): MCP tools alone consume 16.3% of the context window—before I’ve even started a conversation. Combined with system overhead, I’m already at 51% usage with essentially zero messages. The Compounding Effect The real problem emerges when overhead compounds. Here’s my actual breakdown: Category Tokens % of Window System prompt 3.0k 1.5% System tools 14.8k 7.4% MCP tools 32.6k 16.3% Custom agents 794 0.4% Memory files 5.4k 2.7% Messages 8 0.0% Autocompact buffer 45.0k 22.5% Free space 99k 49.3% Total: 101k/200k tokens used (51%) You’re working with less than half your theoretical capacity—and that’s with essentially zero conversation history. Once you start coding, the available space shrinks even further. Why This Matters: Performance and Quality Context consumption affects more than just space: Processing Latency: Empirical testing with GPT-4 Turbo shows that time to first token increases by approximately 0.24ms per input token. That means every additional 10,000 tokens adds roughly 2.4 seconds of latency to initial response time. (Source: Glean’s research on input token impact) Cache Invalidation: Modern AI systems cache frequently used context. Any change (adding an MCP, editing instructions) invalidates that cache, forcing full reprocessing. Quality Degradation: When context gets tight, models may: Skip intermediate reasoning steps Miss edge cases Spread attention too thinly across information Fill gaps with plausible but incorrect information Truncate earlier conversation, losing track of prior requirements I’ve noticed this particularly in long coding sessions. After discussing architecture early in a conversation, the agent later suggests solutions that contradict those earlier decisions—because that context has been truncated away. Practical Optimization: Real-World Example Let me share a before/after from my own setup: Before Optimization: 10+ MCPs enabled (all the time) MCP tools consuming 32.6k tokens (16.3%) Only 99k tokens free (49.3%) Frequent need to summarize/restart sessions After Optimization: 3-4 MCPs enabled by default MCP tools reduced to ~12k tokens (~6%) Memory files trimmed to essentials (~3k tokens) Over 140k tokens free (70%+) Results: More working space, better reasoning quality, fewer context limit issues, and faster responses. Optimization Checklist Before adding another MCP or expanding instructions: Have I measured my current context overhead? Is my custom instruction file under 5,000 tokens? Do I actively use all enabled MCPs? Have I removed redundant or outdated instructions? Could I accomplish this goal without consuming more context? In Claude Code: Use the /context command to see your current context usage breakdown. Specific Optimization Strategies 1. Audit Your MCPs Regularly Ask yourself: Do I use this MCP daily? Weekly? Monthly? Could I accomplish this task without the MCP? Action: Disable MCPs you don’t use regularly. Enable them only when needed. Impact of Selective MCP Usage By selectively disabling MCPs you don’t frequently use, you can immediately recover significant context space. This screenshot shows the difference in available context when strategically choosing which MCPs to keep active versus loading everything. In Claude Code, you can toggle MCPs through the settings panel. This simple action can recover 10-16% of your context window. 2. Ruthlessly Edit Custom Instructions Your CLAUDE.md memory files, .cursorrules, or copilot-instructions.md should be: Concise (under 5,000 tokens) Focused on patterns, not examples Project-specific, not general AI guidance Bad Example: When writing code, always follow best practices. Use meaningful variable names. Write comments. Test your code. Follow SOLID principles. Consider performance. Think about maintainability... (Continues for 200 lines) Good Example: Code Style: - TypeScript strict mode - Functional patterns preferred - Max function length: 50 lines - All public APIs must have JSDoc Testing: - Vitest for unit tests - Each function needs test coverage - Mock external dependencies 3. Start Fresh When Appropriate Long conversations accumulate context. Sometimes the best optimization is: Summarizing what’s been decided Starting a new session with that summary Dropping irrelevant historical context 4. Understand Autocompact Buffer Claude Code includes an autocompact buffer that helps manage context automatically. When you run /context, you’ll see something like: Autocompact buffer: 45.0k tokens (22.5%) This buffer reserves space to prevent hitting hard token limits by automatically compacting or summarizing older messages during long conversations. It maintains continuity without abrupt truncation—but it also means that 22.5% of your window is already taken. You can also see and toggle this behavior in Claude Code’s /config settings: In this screenshot, Auto-compact is enabled, which keeps a dedicated buffer for summarizing older messages so long conversations stay coherent without suddenly hitting hard context limits. Claude Code Specific Limitations: The Granularity Problem Claude Code currently has a platform-level limitation that makes fine-grained control challenging, documented in GitHub Issue #7328: “MCP Tool Filtering”. The Core Issue: Claude Code loads ALL tools from configured MCP servers. You can only enable or disable entire servers, not individual tools within a server. The Impact: Large MCP servers with 20+ tools can easily consume 50,000+ tokens just on definitions. If a server has 25 tools but you only need 3, you must either: Load all 25 tools and accept the context cost Disable the entire server and lose access to the 3 tools you need Build a custom minimal MCP server (significant development effort) This makes tool-level filtering essential for context optimization, not just a convenience. The feature is under active development with community support. In the meantime: Use MCP servers sparingly Prefer smaller, focused servers over large multi-tool servers Regularly audit which servers you actually need enabled Provide feedback on the GitHub issues to help prioritize this feature Key Takeaways You’re burning a huge portion of your context window before you even paste in your first file. MCP tools alone can consume 16%+ of your window. System tools add another 7%. The autocompact buffer reserves 22%. It adds up fast. Optimization is ongoing. Regular audits of MCPs and memory files keep your agent running smoothly. Aim to keep baseline overhead under 30% of total context (excluding the autocompact buffer). Measurement matters. Use /context in Claude Code to monitor your overhead. You can’t optimize what you don’t measure. Performance degrades subtly. Latency increases roughly 2.4 seconds per 10,000 tokens based on empirical testing. Reasoning quality drops as context fills up. Start minimal, add intentionally. The best developers using AI agents: Start minimal Add capabilities intentionally Monitor performance impact Optimize regularly Remove what isn’t providing value The goal isn’t to minimize context usage at all costs. The goal is intentional, efficient context usage that maximizes response quality, processing speed, and available working space. Think of your context window like RAM in a computer. More programs running means less memory for each program. Eventually, everything slows down. It’s not about having every tool available. It’s about having the right tools, configured optimally, for the work at hand. Resources Official Documentation Claude Code MCP Documentation Model Context Protocol (MCP) Overview Claude Code Best Practices Claude Code Cost Management Claude Context Windows Research & Performance How Input Token Count Impacts LLM Latency - Glean Community Resources Model Context Protocol Documentation GitHub Copilot Custom Instructions Johnny Winter’s LinkedIn Post on Terminal AI You’ve Been Using AI the Hard Way (Use This Instead) - YouTube Video Have you optimized your AI agent setup? What context window challenges have you encountered? I’d love to hear your experiences and optimization strategies.

Read on selfservicebi.co.uk

Guiding AI Agent - Power BI Report Development Example

SelfServiceBI· 12 Aug 2025

How LLMs Mirror Human Pattern Recognition LLMs are not thinking machines, but pattern matching algorithms. The reason we feel like they are thinking is because most of our brains is to do pattern matching. It works most of the time. Daniel Kahneman described in his book “Thinking, Fast and Slow” about “System 1” that is fast, instinctive and emotional; “System 2” that is slower, more deliberative, and more logical. My view is that the current LLMs are representing our brain using “System 1”. Which can be used if you have ample experience with the topic you want to use it on. (this is the famous 10,000 Hours principle) If the challange you would like to solve does not have a huge literature or you want to solve problems in a novel way, then that is the time to use “System 2”. But AI does not have a “System 2” option that works well. The relatively newer reasoning models are one attempt to try to solve this problem but they still require ample data in their training set to not start hallucinating. Power BI Report Development in Code Developing Power BI reports using the new PBIR format is relatively new, there is not a lot of data available how to do it. There is not really a best practice because Report development is as much art than it is science. Even using AI for DAX requires “Carefully designed instructions and examples” according to Jeffrey Wang. Using LLM with DAX: LinkedIn Post on NL2DAX Benchmark view this post on LinkedIn. AI Agent Research - Audio blog I started researching how to use the tools available to guide the agents to improve the code they are generating.. I encourage you to listen to it because you will find a lot of useful information in this! AI Agents Unveiled Harnessing Power Dodging Pitfalls And Protecting Your Brain In The Age Of AI by Mihaly Kavasi (generated by NotebookLM) New to NotebookLM? Agent instructions Almost all agents have a prompt file you can adjust to guide you Agent how you want it to interact with the code you are working on. AI Agent Name Instruction File Name Claude claude.md GitHub Copilot copilot-instructions.md Gemini gemini-config.yaml Llama llama-instructions.md Mistral mistral-prompt.md Lovable knowledge.md Cursor .cursorrules These are the files you can populate, here is an example of teaching the AI to understand the PBIR format. Notice that you need to have a detailed understanding of how Power BI works, in order to be able to validate and correct the information the AI generates for itself. Prompt steps example for claude.md 1. Init 2. Extend claude.md with Static Resources containing the images and icons added to Power BI, as well as the Base Theme and the Applied Theme. The way Power BI decides how to display a report element is based on the following hierarchy: attribute specified in the visual; if not, attribute specified in the registered theme; if not, attribute specified in the base theme; if not, the default value. This is relevant to understand where certain design settings are set within the report. 3. Extend claude.md to include: • report.json contains the report-level filters • pages.json contains the settings for the opening page when opening the report • page.json contains the page-level filters • visual.json contains visual-level filtering 4. Extend claude.md with the information about where to find the names of entities such as pages, visuals, and bookmarks. 5. In claude.md, clarify the definition of visual names by checking multiple visuals, since some visuals have names and others do not... Join me: I will show you in a live demo how to work with AI Agents in Power BI Join the London FABUG Community Interested in learning more about AI agents and Power BI development? Join us at the next London Fabric and Power BI User Group meetup! Recommend you to watch this video to understand where AI Agents and LLMs in general are heading. How to change your prompts for GPT-5 watch on YouTube. Conclusion Working with AI agents in specialized domains like PBIR development requires bridging the gap between pattern matching and true reasoning. We must act as the “System 2” - providing detailed instructions, validating outputs, and correcting misconceptions through instruction files that transfer our domain expertise. The key takeaway? AI agents can accelerate Power BI development when paired with human expertise. Success requires understanding both the technology (Power BI, DAX, PBIR) and how to effectively communicate this knowledge to AI partners. The future isn’t about replacing human expertise - it’s about amplifying it through thoughtful human-AI collaboration. As tools evolve, our role shifts from coding everything to skillfully guiding AI agents toward desired outcomes. Resources Claude Code Best Practices (Obsidian Publish) Why GitHub Copilot Custom Instructions Matter — Thomas Thornton

Read on selfservicebi.co.uk

Creating a Power BI Knowledge base with NotebookLM

SelfServiceBI· 20 Jun 2025

Power BI documentation is extensive, but finding the right information when you need it can be challenging. What if you could have an AI assistant that knows all the Power BI documentation inside and out? That’s exactly what we can achieve using Google’s NotebookLM. NotebookLM is Google’s AI-powered research assistant that can analyze and understand large collections of documents, making them searchable and queryable through natural language. In this post, I’ll show you how to create a comprehensive Power BI knowledge base using NotebookLM. Why Create a Power BI Knowledge Base? Power BI has thousands of pages of documentation scattered across Microsoft Learn, community forums, and various resources. Finding specific information often involves: Searching through multiple documentation sites Reading lengthy articles to find relevant sections Trying to remember where you saw that specific feature explanation Piecing together information from different sources A centralized knowledge base powered by AI can solve these problems by providing instant, contextual answers to your Power BI questions. Isn’t just using LLM not good enough? Large language models (LLMs) are incredibly versatile because they’re trained on massive amounts of data. However, this also means they can sometimes struggle to find specific details within a huge web of information. Plus, since LLMs generate responses based on patterns rather than exact facts, they might occasionally provide outdated info or even make things up (a phenomenon known as “hallucination”). Getting Started with Power BI Documentation The first step is gathering the Power BI documentation. Microsoft provides comprehensive documentation on Microsoft Learn, covering everything from basic concepts to advanced features. Power BI Official documentation You’ll might also want to collect documentation covering: Community Tools – Add-ons, custom visuals, and utilities for Power BI. Code Repositories – GitHub/GitLab samples, DAX, Power Query, and scripts. Related Technology Docs – Docs for Entra, Dataverse, Fabric, Synapse, SQL Server. Community Blogs & Videos – Tutorials and tips from the Power BI community. Official Power BI Blog – Updates and best practices from the product team. Conference Materials – Presentations and slides from Power BI events. Sample Datasets & Templates – Example datasets, PBIX files, and templates. FAQ & Troubleshooting – Common issues and solutions from forums. API & Developer Docs – Resources for REST API, embedding, and automation. Security & Compliance – Guides on governance and data protection. You can also take advantage of NotebookLM’s built-in Discover feature to search for additional resources and further enrich your knowledge base. Setting Up NotebookLM Once you have your documentation organized: Visit NotebookLM - Go to notebooklm.google.com Create a new notebook - Start a new project for your Power BI knowledge base Upload documents - Add your collected documentation files Let NotebookLM process - The AI will analyze and index your content NotebookLM supports various file formats including PDF, text files, and even Google Docs. It can handle substantial amounts of content, making it perfect for comprehensive documentation collections. Querying Your Knowledge Base Once your knowledge base is set up, you can start asking questions in natural language: NotebookLM will provide detailed answers with citations, showing you exactly which documents contain the relevant information. Important reminder: Verify! Cross-reference answers with source Test suggested solutions before implementing Keep your documentations up-to-date Conclusion Creating a Power BI knowledge base with NotebookLM transforms how you access and use Power BI documentation. Instead of spending time searching through multiple resources, you can get instant, contextual answers to your questions. Start building your Power BI knowledge base today, and experience the difference of having all Power BI information at your fingertips, ready to answer any question you might have.

Read on selfservicebi.co.uk

How to find which visual generated the query in Log Analytics?

SelfServiceBI· 11 Mar 2024

Model metadata in Power BI is getting more and more accessable and there are a fair amount of tool that we can use to work with it. Report metadata on the otherhand is not easy to work with. In my previous blog post, I showed how you can use Log Analytics to improve the performance of models and reports. The only missing piece was how you can find the visual that you need to work on. This post will try to cover all the different ways we can extract and interact with report metadata. Manual method If you have access to the PBIX file for the report, you can extract the information for the file. Rename the file from .pbix to .zip Open the zip file and find the Layout file Open it in an editor like VS Code Format file to JSON Search for the VisualID (CTRL+F) Search for the Display name that you can look up in the Selection View in Power BI Desktop To make your life easier with larger report, you can also the find the Page name. You need to search for the ReportSection that encompasses the visual Limited Python knowledge & GitHub Copilot method :) I long wanted to find a meaningful task, where I could see how to use GitHub Copilot to enhance my limited python knowledge. It was quite an interesting learning experience that helped me understand how to interact with Copilot in a way that is productive. Python Script: SelfServiceBIBlog/Governance/ExportReportMetadata.py at main · KavasiMihaly/SelfServiceBIBlog (github.com) This script is currently extracts the most essential information to help with the analysis in Log Analytics. Feel free to add to this script and update with additional information extracted. Happy for any code contributions in GitHub. ⚠️You might need to open the Layout file in VS Code first and format the file to JSON.⚠️ Output: Script will create a csv file containing the Visual and Page details. Adding the results to Log Analytics When you add this extracted information to the Log Analytics template report, we discussed in my previous post, it greatly improve the insights and makes it really easy to find the visuals in a report. Also enables you to do more generally analytics about the type of visualisations used and their performance. Community tools available Power BI Helper "Power BI Helper is an external tool for Power BI Desktop. Using this tool, you can analyze your Power BI file, explore the data sources, the Power Query script, the data model, DAX expressions, and visualizations. This is a tool for Power BI developers and analysts and administrators, architects, and managers to create documentation of their work and have a better Power BI development experience." https://powerbihelper.org/ https://powerbihelper.org/ This tool give you an overview of the visuals and the ability to export this information. Measure Killer https://en.brunner.bi/measurekiller Analyzing the model and report to find unused measures and column Entreprise version allow tenant level analysis The UI doesn't show the visual information but after exporting the data you can get to the visual details. Microsoft Fabric APIs With the appearance of Fabric APIs, we now have a Microsoft supported way to access Power BI Report metadata among a lot of other items available in Azure. As you can see in the image the API could be also used to manage these items. Fabric Report Definition API: https://learn.microsoft.com/en-us/rest/api/fabric/articles/item-management/definitions/report-definition Here are the information you can access through the API. The visual and page metadata is stored in the report.json. Conclusion Report metadata is useful for performance monitoring and integrating it with log analytics data can improve the usability.

Read on selfservicebi.co.uk

Log Analytics for Power BI: How to start and improve insights for better model performance

SelfServiceBI· 05 Mar 2024

I had a task to help a managed services team in monitoring multiple production datasets and reports in Power BI. These resources, accessed by thousands of users, demanded optimal performance.  For this we had to streamline information gathering, implement an intuitive platform, and empower the team to explore both high-level overviews and granular query details. This was a perfect opportunity to work with Log Analytics for Power BI and to dig deep into the data and find ways to advance the available solutions. What is Azure Log Analytics? Azure Log Analytics (LA) is a service within Azure Monitor that Power BI uses to save activity logs. Azure Monitor enables you to collect, analyze, and act on telemetry data from both Azure and on-premises environments. Key features of Azure Log Analytics include long-term storage, an ad-hoc query interface, and API access for data export and integration with other systems. How does the integration work? Power BI integrates with Log Analytics to expose events from the Analysis Services engine. These events are derived from existing diagnostic logs available for Azure Analysis Services. Once connected to Power BI, data is sent continuously and becomes available in Log Analytics in approximately 5 minutes. Integration is on the Workspace level, which allows selective data collection and better cost control. Only work with Workspaces in a Premium capacity By default 30 days of data is preserved. This can be modified and there are options for archiving. If you need further guidance on setting up Log Analytics, refer to the official documentation. 🚀 Analyzing collected data In Azure In the Azure Portal you can analyse the data in the Logs section of the Log Analytics Workspace. Here is the official tutorial that help getting familiar with the https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-tutorial ⚠️There is a big limitation of analysing data from Log Analytics which is the limited query resultset size. It is the same if you are analysing the data in Azure or connecting through the API via Power BI. You can see that in the notification in the picture above. ⚠️ In Power BI Fortunately Rui Romano, Bravik Merchant and other from Microsoft contributors created a really useful Power BI report template to analyse Log Analytics data. Power BI Log Analytics Template Reports: https://github.com/microsoft/PowerBI-LogAnalytics-Template-Reports This is a brilliant report that provided a wide range of insights about both model query and refresh performance. I highly recommend to use if you plan to implement Log Analytics at your organization. The solution also resolved the data volume issue by querying Log Analytics in increments: But something was missing... As I implemented the solution at one of the projects I noticed that there was no way in the template report to attribute a query to a particular visual. The reports that connected to the models had multiple pages and potentially more than 100 visual elements, this made finding the problematic visuals quite hard. So I started searching through the logs to see if there is any information available that would help. As you can see in the image the Visual details were buried in the Application Context column. How to add the missing piece? Because the report is really sophisticated it took me a while to understand its inner workings and also received some help from Will Cisler, thank you for that! Add Visual ID to the data First we need to modify the Power Query and embedded KQL code to add the missing element. As a starting point extend fnGetOperationsExceptRefreshes function: | extend Sources = parse_json(ApplicationContext).Sources | mv-expand Sources | extend ReportId = tostring(parse_json(Sources).ReportId) | extend VisualId = tostring(parse_json(Sources).VisualId) | extend TextDataHash = hash(EventText) | extend DurationMs = coalesce(DurationMs, 0) | extend User = iff(toupper(PowerBIWorkspaceId) in (workspaceIdList), hash_md5(User), User) | extend ExecutingUser = iff(toupper(PowerBIWorkspaceId) in (workspaceIdList), hash_md5(ExecutingUser), ExecutingUser) | distinct ApplicationName, CpuTimeMs, ArtifactId, ArtifactName, ArtifactKind, DatasetMode, DurationMs, EventText, ExecutingUser, OperationDetailName, OperationName, PremiumCapacityId, ReportId, SourceSystem, Status, StatusCode, TenantId, TextDataHash, TimeGenerated, Type, User, PowerBIWorkspaceId, PowerBIWorkspaceName, XmlaRequestId, XmlaSessionId, _ResourceId, _SubscriptionId, VisualId | extend QuerySubeventKey = case( OperationName == 'QueryEnd' and isnotempty(XmlaRequestId), XmlaRequestId, // add rootActivityId as the key if this is a QueryEnd. This joins to the Suboperation table. dynamic(null)) // if we are not expecting subevents then make this null. In PowerQuery we will add a dummy index value for null values to keep this column unique for the 1:* relationship | project-rename DatasetId = ArtifactId, Dataset = ArtifactName, StartTime = TimeGenerated, ResourceId = _ResourceId, SubscriptionId = _SubscriptionId, ['Duration (ms)'] = DurationMs, VisualGUID = VisualId "],Timeout=#duration(0,0,60,0)])), ⚠️There might be other parts of the solution need to be adjusted to let this new column to flow through the pipeline.⚠️ Add Visual Details Page to the Report Finding the visuals that cause the performance issue was our main goal, so I created a page dedicated to showcase query summary by visuals. Also added some time analysis to show how changes in usage or improvements in the solution affect the performance. To connect the page with the rest of the report a drill through was added from the Workspace summary page to the Visuals details. Also from the Visual details to the Query details to understand how each query performs. Real life examples of performance improvements using Log Analytics Here are some examples that we were able to achive with analysing Log Analytics data with our current setup. Slow table visual (40s+) that was designed for exporting data running more than 3.000 time in 2 weeks, was moved to a separate drill through page (Progressive Disclosure). Turns out nobody really wanted to export data because it only run less then 20 times in the next 2 weeks. Complex DAX issue causing visual to failed after running for more than 4 minutes. Corrected the calculation, now it runs under 10 seconds. Legacy, currently unnecessary filters in some visuals for "data cleansing" removed improving query performace from 60 sec to 2 sec. What's next? Unfortunately currently in Power BI there is no option to extract report metadata information from the published reports through the Power BI APIs. In my next blog post I will show how you can find the exact details of a visual and page in a PBIX file manually, what are the community built options and how Fabric APIs will help.

Read on selfservicebi.co.uk

New Page Available: Explore Power BI features with Power BI

SelfServiceBI· 04 Aug 2022

I spend a lot of time going through the Power BI documentation to find answers about Power BI features. As always, I look for opportunities to make my life easier when I need to look through of data. What better tool for this than Power BI itself? So, I created a few reports to help me and you to find easier and faster what we search for. Questions like: Is it mandatory to use Data Gateway if I need to connect to Amazon Athena? Will I be able to connect live to Essbase? What are the parameters I need to use if I want to get all the reports using the Rest APIs? Is there an API call to cancel a dataset refresh? What options admin have to manage external user access? I saw a feature in a blog post/video, but I cannot see it in my tenant, can the admin turn it off? Link to the Page: Power BI Features Explore Power BI features with these reports Hope you find these reports useful for finding answers to your questions faster.

Read on selfservicebi.co.uk

Hidden features of Analyze in Excel - Part 3 - Focus

SelfServiceBI· 15 Jul 2022

In the first two parts (part 1 and part 2) of the blog we explored the Show details functionality of a Pivot table in Excel. Now we look at another hidden feature that will help you limit distractions. Whenever you connect to a Power BI dataset you immediately see the whole model. The issue is that many models contain a lot of tables, columns, and measures. Most of them irrelevant to your current analysis. Which model would you rather work with? Full data model Housing Market submodel When creating the dataset there is usually a desire to make it as comprehensive as possible, but it comes with a cost. Using it can become confusing and complicated for the business users. I have a blog post talking about the differences between making a data model for yourself and others, which talks about these challenges. In that post I talk about the different approach that you need to take when designing a dataset for someone else to use. This is even more true to the Excel users. But we have a "secret" tool to make their life much easier called perspective. What is a perspective? "Perspectives, in tabular models, define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model." - AAS documentation. In Power BI it is used for supporting the Personalized Visuals feature. By default, you cannot use them when creating Power BI Report. Creating them also not available in PBI Desktop. You need to use Tabular Editor. Tip: In Premium, using the Analysis Services connector, the perspectives are accessible in Power BI Desktop. Using perspectives in Excel In Excel, when connecting to a Power BI Dataset you can modify some properties of that connection that is not available in Power BI. By opening the connection properties and navigating to the Definition section you can define the perspective you would like to use. Tip: The Perspective name cannot have spaces in it. Once you defined it, in the pivot table you will only see the tables, columns and measures that are added to that perspective. This can greatly simplify the work of an analyst because they do not need to spend so much time finding the pin in the haystack. Connecting to the Housing Market Data perspective Creating a perspective connection file? You can export the connection into a connection file and this property will be saved. Summary Perspectives are a really fantastic way to create different simpler views from a complex data model without the need to split it up. This helps both the Power BI developer and the end users. Hope you liked this blog post, see you in the next one!

Read on selfservicebi.co.uk

Hidden features of Analyze in Excel - Part 2 - Give me all the data

SelfServiceBI· 11 Jul 2022

In the previous blog post we discussed how to define the details of the drill through feature of a Pivot table. Show Details - default results As we learned how to define the results of the Show Details capability, we encounter another potential issue. As you can see in the image below the cell selected represented almost 2.000 rows, but by default you only get the first 1.000 rows. This way this feature it not that useful. Default Show Details functionality - 1.000 rows In an ideal world you would expect to see all the data and if you miss the first row you might believe you actual have it. This can cause a lot of confusion. Fortunately, there is a solution to get all the details. Connection Properties - OLAP Drill Through If you go to the properties of the connection, you will see an option that most people ignored so far. It is called Maximum number of records retrieve. Connection properties You can increase this number to anything that an excel sheet can cope with. Of course, getting more data require an efficient data model, good internet connection and a suitable hardware spec. Show Details made more useful Together with the Details Rows Expression and increasing number of rows retrieved in Show Details, we can increase the value of these standard Excel functions. Modified Show Details functionality - 10.000 rows Considerations Because this setting is in Excel it cannot be set in the Power BI model. You need to open the file in the desktop version of Excel you cannot use or set this feature in the Excel Online. It is possible to create a connection file to save the details, but it does not save this setting. This will be useful in the next part of this series. For now, you need to save the excel file created from Analyze in Excel with this setting modified to a shared drive for the excel users to use. Currently searching for a solution to change this setting globally for all excel files so it is not a manual effort every time. If you know one, please share it with me. Export Connections into an .ODS file Summary This and the previous post not just ensure that the Excel users do not feel any negative impact by using a Power BI dataset but instead adds value to them. In the next blog I will show how you can reduce distractions and complexity when working with a Power BI dataset in Excel. Hope you like this blog post, see you in the next one.

Read on selfservicebi.co.uk