3

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].

  1. Can anybody clarify if this Use Case fits to MDS?
  2. 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]
Patrick B.
  • 157
  • 11
  • 1
    So, using the MDS Excel plug-in as a front end to just any sql server table will not work quite the way you want. MDS will create a database full of tables according to the architecture of your MDS data model, and you can update your data warehouse from those tables. MDS has a lot of functionality that you won't use, but you'll need to understand and handle when you write those transfers. Getting it to work in near real time will probably take some effort too. Personally, I think you're going to be trading Excel headaches for MDS headaches, but what you describe is at least imaginable. – Ryan B. Jul 17 '17 at 13:38

2 Answers2

2

I have worked on a DWH project in which an MDS instance was used as a single source of truth for many dimensions. Most of the data have been rather read-only (lists of states, countries, currencies, etc.) and were maintained via the Excel plug-in. There was also some more volatile stuff which was imported via MDS import procedures.

In order to expose the MDS data to the warehouse, views were created that pointed directly to the MDS database. I have even written a SQL script that refreshed these views, depending on the MDS metadata and settings stored in the warehouse. Unfortunately, I don't have it around anymore, but it's all quite transparent there.

Everything was very much alive. Can't recall any problems with queries that involved these MDS views.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
2

Keep in mind, an Entity in MDS does not represent a table in the database. This means when you load data in MDS, there are underlying tables populated with the data and metadata to keep track of changes, for example.

Using the Excel plugin to import data into MDS, and then expose the data to another system can work, considering the following:

  • Volume of data. The excel plugin handles large volumes in batches. So the process can become tedious.
  • Model setup. You need to configure the model properly with the Entities and Attributes well defined. The MDS architecture is 'pseudo data warehouse' where the entities can be considered 'facts' and the domain based attributes 'dimensions'. This is an oversimplification of the system but once you define a model you will understand what I mean.
  • A nice functionality is subscription views. Once you have the data in MDS, then you can expose it with subscription views which combines entities with domain based attributes in one view.

Considering your requirements:

  • I want to avoid the problems coming up when ingesting Excel-files.

    This is possible, just keep in mind the Excel plugin has its own rules. So Excel effectively becomes the 'input form' of MDS, where data is input and committed. The user will need to have a connection set up to MDS using the credential manager etc.

  • It should be possible to validate data input and give a quick feedback to the user

    This can easily be handled with domain based attributes and business rules

  • 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.

    Keep in mind, the MDS plugin determines how the excel sheet looks and feels. No customization is possible. So your entity definitions need to be correct to facilitate a good user experience.

Schoeman Loubser
  • 96
  • 4