Connect decisions to delivery
Explain requirements, priorities, dependencies, governance, and operating costs. Show how technical choices support a platform that a team can understand and maintain.
Enterprise Analytics & BI Leader · Nashville, TN
I lead the work.
I build it, too.
I'm Bill Smith, a BI manager and analytics architect who stays hands-on across data engineering, SQL, and Power BI. I'm building this personally funded lab while seeking my next role: applying lessons from previous jobs, exploring unfamiliar capabilities, and preparing for Microsoft certifications.
The purpose of this project
Practical experience. Continued learning. Work you can inspect.
This is my personal learning and portfolio project. I am developing it toward enterprise readiness, using the problems I encountered in previous roles to guide the design. It also gives me room to try approaches and features I have not used before, document what I learn, and build evidence of my technical work.
Build across the stack
Work directly on ADF pipelines, SQL procedures, ingestion, data models, and reporting. Pair architecture explanations with implementation examples and validation evidence as the lab develops.
Extend my experience
Use the lab to explore unfamiliar capabilities and prepare for Microsoft certifications. I have not yet earned Microsoft certifications; this project supports that learning journey.
Current focus: build and validate the data foundation first. Next, use that data to create Power BI dashboards and explore current reporting capabilities. The roadmap below separates those stages.
The core idea: Azure Data Factory determines when work starts and executes activities. Azure SQL holds the configuration, processing rules, dependencies, and run history that describe the work.
Experience behind the lab
Leadership backed by direct implementation.
My professional background combines enterprise reporting, data engineering, platform administration, and team development. The lab builds on that experience while giving me space to learn capabilities I have not used before.
Senior BI Developer
On a short-term remote staffing contract, I supported reporting modernization into a governed Power BI environment. I built and enhanced dashboards, paginated reports, visuals, slicers, and layouts, and optimized SQL to align reporting with dimensional models and enterprise semantic datasets.
BI Manager / Enterprise Analytics Architect
I architected and implemented ingestion, ETL/ELT, dimensional models, governed semantic models, and Power BI reporting. I delivered reporting for 100+ users across finance, supply chain, HR, and operations, established standards, and mentored developers in modeling, DAX, and SQL performance.
Business Intelligence Developer
I consolidated reporting across restaurant brands, built executive sales reporting and cross-concept Power BI dashboards, and partnered with finance, operations, and marketing on useful business insights.
Prior professional results at Fidelitone: reduced data load times by 50% and compute costs by more than 40% through ETL and SQL Server optimization, as documented in my resume.
These outcomes describe my previous professional work. Lab performance and cost results will be added after measurement.
| Experience I bring | How I will demonstrate it here |
|---|---|
| Architecture and delivery leadership | Document requirements, priorities, design tradeoffs, governance, and a phased delivery roadmap. |
| Hands-on engineering | Show sanitized pipeline definitions, SQL procedures, model designs, and end-to-end validation. |
| Operational ownership | Demonstrate failure diagnosis, recovery, refresh monitoring, performance, and cost measurement. |
| Mentoring and stakeholder alignment | Explain the design in clear walkthroughs and connect dashboards to business questions and decisions. |
My resume also includes earlier BI, data engineering, database, and IT leadership roles at Ingram Barge, Pfizer, Security Check, and the University of Mississippi. My technical background includes Power BI, Azure, SQL Server, Oracle, and Excel.
Technology and technical skills
The tools I work with.
A quick view of the technologies and techniques in my lab and professional experience. Power BI dashboard development is the next lab phase; the broader list reflects my resume and does not imply every tool is part of this lab.
Azure, SQL, and integration
Azure Data Factory (ADF), Azure SQL, SQL stored procedures, REST APIs, Microsoft Graph, metadata-driven ingestion and orchestration, ETL/ELT, staging tables, incremental loads and watermarks, schema validation, dimensional processing, data quality rules, execution logging, and monitoring.
Development and operations
Azure DevOps, Git, CI/CD, service-principal authentication, configuration management, and automated Power BI semantic model refresh. Power BI dashboards and exploration of newer reporting features are planned next.
| Area | Technologies and techniques |
|---|---|
| Power BI and reporting | Power BI, Power BI Service, Power Query (M), DAX, semantic models, dashboards, visuals, slicers, interactive reports, paginated reports, on-premises data gateway, Tabular Editor, Report Builder, Report Server, Excel, certified datasets, and deployment pipelines. |
| Azure and platform administration | Azure Data Factory, Azure Data Lake, Azure SQL, Azure DevOps, workspace governance, security, Premium capacity management, and Microsoft Fabric administration support. |
| SQL Server | SQL Server, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), database administration, indexing, schema design, query optimization, and performance tuning. |
| Database development | T-SQL, PL/SQL, stored procedures, window functions, common table expressions (CTEs), Oracle development and administration, production deployments, and AI-assisted development and query optimization. |
| Architecture and data engineering | Kimball dimensional modeling, data warehousing, ETL/ELT, cloud and on-premises ingestion, Oracle-to-SQL Server migration, SQL Server-to-Azure migration, compute scaling, vCore management, and read-replica strategies. |
| Business systems and integrations | ERP, warehouse management systems (WMS), labor management systems, ADP, PeopleSoft, Dynamics AX, Datex Footprint, Dash Logistics, and API integrations. |
| Modernization and governance | Cognos, Tableau, and legacy-reporting modernization into Power BI; data quality, lifecycle governance, SOX/compliance reporting, audit reporting, data access controls, KPI definitions, and documentation standards. |
| Development and delivery practices | Git version control, CI/CD, Azure DevOps, Agile delivery, requirements scoping, stakeholder alignment, mentoring, and cross-functional delivery. |
Microsoft Fabric experience includes administration support; deeper Fabric capabilities remain an area for continued learning. This portfolio webpage uses HTML and CSS and is packaged for Cloudflare hosting.
01 / Platform map
Separate the data path from the control plane.
Source data moves into staging, passes through SQL transformations, and becomes available to Power BI. Alongside that path, control tables describe what to execute and run tables record what happened.
Source systems
SQL databasesConfigured database objects and extract definitions.
REST APIsEndpoint, authentication, and load behavior defined by configuration.
Microsoft GraphAn API source used in the personal lab.
SQL and API sources feed the ingestion activities in Azure Data Factory.
Orchestration + processing
ADF triggers → pl_mainSelect one extract or a configured schedule group.
Azure SQL stagingLanding tables receive SQL and API extracts.
SQL transformationsProcess configured procedures and dependencies into analytical structures.
SQL control planeConnection · Extract · Transformation · DataQualityRule · SemanticModel
Consumption + visibility
Power BI semantic modelsRefresh configured models after the required processing completes.
Reports and analyticsBusiness measures and interactive reporting built on the model.
Operational run historyPipeline, extract, transformation, quality, and refresh telemetry.
Cloudflare hosts this explanatory webpage. Data processing remains in Azure and Power BI.
Scope: the agreed SQL/API replacement design. File ingestion and file-specific pipelines are intentionally excluded from this version.
02 / A run, explained
One entry point. A traceable execution path.
The selected entry-point contract keeps timing in ADF and selection in SQL. A schedule name identifies a group of extracts; it does not ask SQL to wait until a future time.
Invoke the pipeline
An ADF trigger or manual invocation starts
pl_main. WhenExtractNameis supplied, select that named extract. Otherwise, useScheduleNameto select the configured group.Resolve the configured work
Read active extract and connection metadata. Establish the pipeline run identity so downstream activity can be associated with the same invocation.
Ingest into staging
ADF executes the SQL or API ingestion path. Extract metadata describes source and target objects, load type, and watermark configuration. Record row counts, duration, schema-change information, and errors where available.
Validate and transform
Apply configured quality rules at their execution stages and process SQL transformations according to dependency and execution settings. The metadata includes enforcement, priority, parallelism, and timeout controls.
Refresh the semantic model
Request the configured Power BI refresh when its prerequisites are satisfied. Track the request and completion separately; accepting a refresh request does not establish successful completion.
Close the run and surface failures
Capture terminal status and diagnostic details. The replacement design preserves
SendFailureEmailbehavior so failure notification remains part of the execution contract.
Staging is a landing area. Full-load staging tables may be truncated and temporarily incomplete during a load. That is an accepted lab design choice; downstream processing still needs to respect extract outcomes and dependencies.
03 / Configuration and evidence
Metadata describes intent. Telemetry records outcomes.
The supplied SQL schema separates reusable definitions from individual run records. This makes the orchestration understandable without duplicating every source-specific decision across pipelines.
| Object | Responsibility | Examples of configuration |
|---|---|---|
ctl.Connection | Describe how a source or service is reached. | Connection type, server/database, base URL, authentication type, secret reference, OAuth scope. |
ctl.Extract | Describe an ingestion task. | Source object, target schema/table, load type, watermark column. |
ctl.Transformation | Register SQL processing. | Procedure schema/name, data layer, execution priority, parallelism, change-based execution, timeout. |
ctl.DataQualityRule | Describe a validation rule. | Target object/column, rule type or SQL, threshold, enforcement, execution stage. |
ctl.SemanticModel | Identify a Power BI refresh target. | Workspace and dataset identifiers, refresh mode, timeout, maximum attempts. |
PipelineRun
Connects the ADF run identifier, pipeline name, schedule group, timestamps, status, and error details.
ExtractRun
Records source and target context, rows read/written, bytes, copy duration, watermarks, and schema-change diagnostics.
TransformationRun
Tracks queue and execution times, attempts, affected rows, inserted/updated/deleted counts, and block reasons.
DataQualityRun
Associates a rule and pipeline run with observed values, failure counts, timing, and outcome.
SemanticModelRefreshRun
Tracks queued/requested/completed timestamps, attempts, HTTP status, refresh identifiers, and failure context.
PipelineRunId
Provides the shared key for following an invocation across extraction, transformation, validation, and semantic refresh.
04 / Next phase: Power BI
Turn the data foundation into useful decisions.
Once the data platform is ready, I plan to build dashboards on top of it, explore the latest Power BI capabilities, and document where those capabilities improve the reporting experience. These are planned deliverables.
Business reporting
Build a dashboard around a clear business question using the available lab data. Define its KPIs, model relationships, and calculations, then explain the decisions the report helps a user make.
Operational reporting
Use the platform's run history to explore success, failures, duration, throughput, quality outcomes, and refresh health. Give a reader a path from a summary to the underlying run evidence.
Useful operational filter dimensions
These are reporting possibilities supported by the run metadata, rather than a verified inventory of slicers in a live report.
| Filter dimension | What it isolates | Interpretation |
|---|---|---|
| Run date / time | A processing window. | Run timestamps are stored in UTC. A report should explicitly identify any display-time conversion. |
| Pipeline / schedule | An entry point or configured extract group. | Compare like-for-like workloads when assessing duration. |
| Run status | Successful, running, failed, or other recorded outcomes. | Retain blocked and incomplete outcomes when diagnosing a run. |
| Extract / connection | A source or ingestion task. | Rows read and written describe movement; differences require load-specific interpretation. |
| Transformation / rule | A SQL processing step or validation. | Queue time and execution time answer different performance questions. |
| Semantic model | A configured Power BI refresh target. | Use completed refresh outcomes to assess delivery, alongside source processing status. |
Planned reporting evidence: add report screenshots or walkthroughs, metric definitions, a model diagram, filter explanations, and reconciliation examples as the dashboards are built.
05 / Why it is designed this way
Keep the execution reusable and the behavior inspectable.
Why put orchestration metadata in SQL?
A shared configuration model provides a consistent place to describe connections, extracts, procedures, rules, and model targets. ADF can reuse execution patterns while SQL holds the changing task definitions.
Why consolidate ADF pipelines?
The replacement direction favors fewer reusable pipelines with short names. The documented entry point is pl_main; additional pipeline names and counts are not asserted here without the current deployment artifacts.
Where do security and secrets belong?
The connection schema includes secret references and authentication metadata. Actual credentials belong outside this webpage. The lab also uses service-principal authentication; access enforcement remains with the configured Azure and Power BI services.
How does development fit in?
The personal lab uses Azure DevOps and Git CI/CD to develop transferable analytics patterns. Changes to orchestration and SQL should be reviewed together because their contracts determine how work is selected, executed, and logged.
What is the maturity of this platform?
This is a personal learning and portfolio environment. It demonstrates an architecture direction and schema capabilities; this page does not certify a deployed system, production reliability, or completed end-to-end validation.
06 / My learning and delivery roadmap
Build it. Validate it. Explain it.
My current focus is the data platform. The following checklist is a proposed sequence for the remaining work; individual tasks are not marked complete until their evidence is available.
| Phase | To do | Evidence to produce |
|---|---|---|
| 1 · Data foundation Current focus |
| A repeatable end-to-end run, a failure/recovery example, and recorded validation results. |
| 2 · Operational readiness |
| A deployment walkthrough, access summary, cost baseline, and recovery runbook. |
| 3 · Semantic model |
| A model diagram, measure dictionary, reconciliation examples, and refresh results. |
| 4 · Power BI dashboards |
| Dashboard walkthroughs that explain the audience, question, insight, and resulting action. |
| 5 · Certification preparation Alongside the build |
| An objective-to-exercise checklist and an accurate record of earned credentials. |
| 6 · Interview portfolio |
| A concise case study showing both management judgment and direct technical execution. |
Project status and documentation
A working portfolio that grows with the lab.
The data platform is in development; Power BI dashboard work follows. Architecture details on this page draw on the SQL/API replacement design and the database schema exported on November 5, 2026. The roadmap identifies work to validate and demonstrate as the project progresses.
I'm seeking opportunities where I can lead an analytics team while continuing to design, build, troubleshoot, and mentor. Based in Nashville, Tennessee; open to remote or hybrid opportunities.