SQL Server DBA with Power BI

Role - SQL Server DBA with Power BI

Location: NJ preferred but must be based in the USA

Duration: 3 to 6 Months+

Role summary

Owns everything on the SQL Server side: the replication target, the operational data store, the dimensional data warehouse, Power BI Report Server and the reports built on it, plus performance, availability and recovery. Data warehousing is a must-have: this role designs and builds the facts, dimensions and history that 30 years of Client’s data and roughly 250 reports will rely on.

Key responsibilities

Dimensional data warehouse (must-have)

·       Design the warehouse with Kimball dimensional modeling: define the grain of each fact table, build the bus matrix and conform dimensions across subject areas.

·       Model the business areas confirmed in discovery. Likely areas include members, eligibility, plans, groups, providers, claims and payments.

·       Build transaction, periodic snapshot and accumulating snapshot fact tables as each measure requires.

·       Build dimensions with surrogate keys and slowly changing dimension handling (Type 1 and Type 2), so reports can see eligibility and plan as of a past date if Client’s confirms that need.

·       Handle a date dimension, degenerate and junk dimensions, bridge tables for many-to-many relationships, late-arriving facts and unknown members.

·       Load roughly 30 years of history and design for it: partitioning, columnstore indexes, compression, and an archive approach for older data.

·       Build the data preparation pipeline from the operational store into the warehouse with T-SQL, SSIS or the agreed tooling, scheduled with SQL Server Agent, with restartable and auditable loads.

Operational data store and replication target

·       Design the replication target and operational data store schemas, separated from the warehouse so report refreshes do not slow API reads.

·       Work with the Db2 for i Specialist on target data types, keys and conversions for packed decimal, legacy dates and padded CHAR fields.

·       Configure and monitor the CDC tool’s SQL Server target, including apply performance and lag.

·       Build SQL Server-side reconciliation and data-quality checks, and report results to Client’s.

·       Provide read-only views or schemas for the API services, sized for roughly 50–60 requests at peak during 9:00 AM to 5:00 PM.

Power BI Report Server and report migration

·       Install and configure Power BI Report Server on-premises: data sources, security, folders, scheduled refresh and subscriptions.

·       Advise on licensing. On SQL Server 2022 and earlier, Microsoft requires Enterprise edition with active Software Assurance for Power BI Report Server; on SQL Server 2025, any paid Standard or Enterprise edition qualifies. A Power BI Pro license is needed to publish Power BI reports; viewing does not need one.

·       With the Principal Architect, inventory and classify the roughly 250 reports into exact-match, improve and retire.

·       Rebuild exact-match and contractual reports as paginated (RDL) reports with fixed, print-ready layouts, and test them against current output.

·       Build interactive Power BI reports, using Power BI Desktop for Report Server, where Client’s wants more data elements or better analysis.

·       Implement row-level security, and test report performance during business-hours peaks.

Database administration, availability and security

·       Specify SQL Server servers, storage, edition and version for Client’s procurement.

·       Design and implement high availability and DR with Client’s’s infrastructure team, such as Always On availability groups and a DR instance, because the APIs cannot fall back to IBM i.

·       Set backup, restore and recovery procedures to the agreed recovery targets, and test them.

·       Secure protected health information: least-privilege roles, encryption in transit and at rest, and SQL Server Audit.

·       Tune performance with indexing, statistics, Query Store and resource governance.

Handover

·       Write the SQL Server, pipeline and report runbooks Client’s will use after go-live.

·       Train Client’s’s shadow staff to operate the databases, the warehouse loads, reconciliation and Power BI Report Server.

Deliverables

·       Warehouse bus matrix, dimensional model and data dictionary

·       Operational data store and warehouse schemas, with load pipelines

·       30-year history load plan and execution records

·       Reconciliation and data-quality checks on SQL Server, with reporting

·       Power BI Report Server installation and configuration record

·       Report inventory and classification

·       Migrated paginated reports and new Power BI reports, with test evidence

·       SQL Server sizing, licensing input, availability and recovery design

·       Runbooks and knowledge-transfer sessions

Required qualifications

·       8+ years as a SQL Server DBA on SQL Server 2019 or later.

·       5+ years designing and building dimensional data warehouses: fact and dimension design, grain, conformed dimensions, surrogate keys and slowly changing dimensions (Type 1 and 2).

·       Built warehouse load pipelines with T-SQL and SSIS or equivalent, including incremental loads and history.

·       Power BI development and administration, including on-premises Power BI Report Server or SQL Server Reporting Services.

·       Paginated (RDL) reports built in Report Builder or Visual Studio, including pixel-exact operational and contractual reports.

·       SQL Server high availability and disaster recovery, including Always On availability groups, backup and restore.

·       Performance tuning for large tables: partitioning, columnstore, indexing and Query Store.

·       Security and auditing for regulated data such as protected health information.

Preferred qualifications

·       Experience with data replicated from IBM i or Db2, including EBCDIC, packed decimal and legacy date conversion.

·       Experience as the target-side owner of a CDC tool such as IBM Data Replication, Precisely or Qlik.

·       Health insurance or benefits domain experience: eligibility, enrollment, claims, providers.

·       Report migration from IBM i, Query/400 or web-application reports.

·       DAX and Power BI semantic models.

·       Microsoft certifications such as Azure Database Administrator or Power BI Data Analyst.

Technical environment

·       Microsoft SQL Server (version and edition set in discovery), SQL Server Agent, SSIS

·       Power BI Report Server, Power BI Desktop for Report Server, Report Builder

·       CDC tool SQL Server target, to be selected in discovery

·       On-premises servers (platform to be agreed with Client’s’s infrastructure team)

How success is measured

·       Dimensional model signed off by Client’s report owners.

·       Pilot reports in daily use, with exact-match reports matching current output.

·       Warehouse and history loads complete inside agreed windows.

·       Reconciliation shows the SQL Server copy matches Db2.

·       Availability and recovery tested against agreed targets.

·       Client’s’s team runs the databases and Power BI Report Server after handover.

Location, compliance and working arrangements

·       Must be located in the United States and authorized to work in the US without sponsorship.

·       Client’s administers vision benefits, so the data includes protected health information. Must be willing to complete HIPAA training, background checks and any confidentiality or Business Associate terms Client’s requires. Client’s has said it may revisit vendor staffing requirements.

·       Remote delivery from within the US, with occasional on-site work in, New Jersey when workshops or go-live call for it.