Can I use Master Data Services to import data via Excel add-in mainly Measures (Numbers/Values)
Shortversion:
Looking for the best way to comfortably input data to an SQl-Server table with immediate feedback for the user.
Set-up:
We have a Datawarehouse (dwh) based on SQL Server 2012.
Everything is set up with the Tools from MS BI Suite (SSIS, SSAS, SSRS and so on)
The Departments access the BI-Cubes via Excel. They prefer to do everything in Excel if possible.
Most sources for the DWH are databases but one use-case has Excel-files as a source.
Use-Case with Excel files as a source
As-Is:
We have several Excel-files placed in a network folder. Each Excel file is edited by a different user. The files are ingested by an SSIS process looping through the files on a daily base.
The contents of the Excel-files is like this (fake data):
Header: Category | Product | Type | ... | Month | abc_costs | xyz_costs | abc_budget | xyz_budget | ...
Data: A Soup Beta 2017-06 16656 89233 4567 34333
Data Flow:
source.Excel -> 1.-> dwh.Stage -> 2.-> dwh.intermediateLayer -> 3.-> dwh.FactTable
- Step 1 to 3 are SSIS ETL-Packages.
- Step 3 looks-up the the Surrogate-Keys from the Dimensions and saves them as Foreign-Keys in Fact-table based on the "Codes" provided by the Excel (Code e.g. can be 'A' for Category).
Problems:
- Step 1 "ingesting the Excel-files" is very error-prone.
- Users can easily misstype the codes and numbers can be in the wrong format.
- Error messages regarding excel-sources are often missleading & debugging Excel-sources in SSIS becomes a pain.
- Sometimes Users leave Excel file open and a temporary Lock-File blocks the whole ingestion process.
Requirements
- I want to avoid the problems coming up when ingesting Excel-files.
- It should be possible to validate data input and give a quick feedback to the user
- As BI-Developers we will try to avoid a solution that would involve webdevelopment in the first place.
- Excel-like input is preferred by the users.
Idea:
As Master Data Services comes with an Excel- addin that allows data manipulation we thought that could be used for this data-input-scenario as well. That would give us the oppurtunity to Test MDS at the same time.
But I'am not sure if this use-case fits to Master-Data-Services.
Doing a research I could not find any MDS example showing how measures are entered via Excel-addin [samples are about modelling and and managing entities].
- Can anybody clarify if this Use Case fits to MDS?
- If it does not fit to MDS ? What can be a good choice that fits into this BI-ecosystem? (preferrable Excel-based). [Lightswitch, Infopath, Powerapps or if no ther option Webdevelopment -> I am a bit confused about the options]