May 29, 2015 at 10:26 pm
Hi,
SQL Server Express 2014
Excel 2007
A bit of preamble: I've been tasked to create a data entry application with Excel as the front end. (I think the FE should be Access, not Excel, but that's not my call). A nightly query will run to augment the data with transactions from our CRM system. The end users then have to edit that data via Excel.
After investigating different data stores (Excel, external files (TDF), and SQL Server), I believe SS for the backend (BE) is the best approach.
My SQL Server tables are normalised and I believe have the correct data modelling. All tables have an identity column as the PK, with FK constraints on the dimension tables. The overall design is 6 fact tables, 12 dimension tables, star schema for fact-dim, with one snowflake for one of the dim tables (view). I'm using views to provide the user interface to Excel. I'm using Excel's builtin support for SS external data access to display the data.
The updates to SS are largely based on this link: http://www.toadworld.com/platforms/sql-server/w/wiki/10392.editing-an-sql-server-table-in-excel.aspx, although I am extending it to support upsert, not just updates.
The data volumes aren't huge: the largest table is 500K rows, and this is spit between 4 analysts (SS view based on login id), so say 150K rows in the largest table, 1.5K - 4K rows for 6 other tables. In my POC the data load is IMO quite fast - about 4 seconds for the 150K rows. After the initial load, updates are a cell at a time and again quite fast. (Hmmm...I'll need to test a big cut-and-paste update, like changing 100 cells from "N" to "Y").
Ok, sorry for the long preamble. However, if you have any suggestions or feedback about the architecture and general approach, please let me know.
Now for my "real" question: There is one view that is a star-schema join of 8 dimension tables to 1 fact table. The view is a simple inner join (left join would also work) and is "update-able".
However, those 8 dimensions need data validation in Excel (dropdown list via Excel's builtin functionality). When I first mocked this up in Access, an OOTB feature in Access I can use is to select the text but the surrogate key (identity column) is returned under the covers. Excel doesn't provide this functionality.
When the user changes the dropdown column, what I want to do is change the FK in the fact table, rather than changing the text in the dim table. I would then refresh the view in Excel.
I think there are (at least) two approaches to this issue: 1) I can write VBA code to lookup based on the dropdown text, retrieve the FK, and update the fact table, or 2) come up with an approach in SS. #2 is my strongly preferred option.
I found this link describing triggers on views: http://michaeljswart.com/2012/10/triggers-on-views-what-for/. Could I use this approach (instead of triggers on views) to accomplish what I need to do?
I've got some sample code, which I'll post in a separate post since this one is long enough already. (See http://www.sqlservercentral.com/Forums/Topic1690201-3412-1.aspx).
Thanks for any help you can provide...
Regards,
Scott
June 1, 2015 at 12:10 pm
I can't entirely digest all what you've said above. But taking a step back, here is what I'd propose:
Have the raw daily transactions from the CRM imported into an Excel sheet (not SQL Server). Rather than linking the Excel sheets with SQL Server, the users can then edit the data entirely offline within Excel. Data coding validation can be done using Excel's VLOOKUP function and reference data contained within additional sheets. Merge the edited transactional records and reference data from Excel to SQL Server a SSIS package and Slow Changing Transformation tasks.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply