Atis. Pharmaceutical Product Availability Analytics Data Integration and Business Intelligence
Duration: 2025 - 2025
Business-to-business (B2B) is a business model where a business sells or purchases products or services to and from another business. This type of relationship is common among manufacturers, wholesalers, distributors, and retailers that will resell the products or services.
Case Study: Pharmaceutical Product Availability Analytics Data Integration and Business Intelligence
Built an SSIS pipeline and SQL Server Analysis Services models to turn pharmaceutical product reports into pharmacy availability analytics. The pipeline reads product IDs and report URLs from an Excel workbook, downloads per-product spreadsheets, extracts report dates and data, maintains product records, and bulk-loads the results into SQL Server. Stored procedures prepare the data, while an optional timestamped archive preserves the source files. SSAS cubes make availability and record-count measures explorable by product and enterprise, with report fields including date, pharmacy code, and location.
Automated Excel-based report retrieval and ingestion using SSIS Script Tasks and C#. Loaded report data into SQL Server with SqlBulkCopy; used stored procedures to remove duplicate product/date data and prepare reporting data. Built multidimensional SSAS cubes and product/enterprise dimensions for pharmacy availability analysis. Technologies: SSIS, C#, SQL Server, ADO.NET, SqlBulkCopy, stored procedures, SSAS Multidimensional, MOLAP.