April 23, 2013 at 12:05 pm
I have the following issue:
1. I work for a company that has boutique stores in various locations around the world. We also sell our products to other retailers, our trading partners. In some cases, we have small kiosks within our trading partners' stores.
2. I have built a data warehouse in SQL Server 2008 R2 for the sales of our products, both wholesale and retail. Actually, version 1 of the data warehouse was already underway when I was hired.
3. In the staging database of our system, we have a table that contains information about both our boutiques and our kiosks inside our trading partners' stores. This table ("Call Cycle") contains the stores status (open/closed), Trading Partner's name and ID if applicable, store name within the retail chain, store opening date, store closing date, etc.
4. The Call Cycle table also includes our sales organization's hierarchy that covers the particular store (VP, regional manager, etc.).
5. Currently, a business user creates a Excel spreadsheet (.xlsx) that contains the information described above. Then an SSIS package loads the .xlsx into the table in our staging database. The spreadsheet is updated intermittently, so the SSIS package is launched manually. Once the .xlsx file is loaded, the business user who modified the file can't look at the table himself to see what was loaded.
Needless to say, this process is inefficient. The question is, what is a good way to modify or replace this process? I was considering Master Data Services since we are using SQL Server 2008 R2. But is MDS overkill for one table? Is there another way that requires less overhead and can be up and running quickly? I am the only developer working on this and I have project manager duties along with my SQL Server developer duties (I think we are woefully understaffed). May I please have some advice on this?
April 23, 2013 at 12:50 pm
If it's acceptable, you could let someone edit/add information in Access linked to the table.
If locks are an issue, you could have them use Access in staging table and then copy the complete staging table to the real table inside SQL with a job or proc call.
April 23, 2013 at 2:40 pm
Can I just create a view and let someone update the underlying table using the view?
Locking shouldn't be an issue. This is just a staging table we're talking about.
April 23, 2013 at 5:25 pm
You could do that. Give you some abstraction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply