January 22, 2014 at 10:05 am
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 22, 2014 at 10:25 am
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
OMG thats awesome! (or obvious to everybody, depending on your sarcasm detector's sensitivity 😉 )
So then given that we've been connecting Excel to databases for all this time, what would be the advantage of putting these constraints into Excel?
January 22, 2014 at 10:45 am
Gary Varga (1/22/2014)
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
I disagree.
Excel can be considered a database but not a Database Management System (DBMS). Often, particularly in technology circles, we treat the term database as though it is synonymous with DBMS but that is not technically true.
But this just really dillutes the message Jim P is trying to get across, with absolutely no advantage. When you talk to end users or non IT management, you really NEED to use the word "database", because thats the common phrase, "I can't connect to the database", you NEVER hear "I can't connect to the DBMS".
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
And when you go out there with this "well, TECHNICALLY Excel can be considered..." their eyes glaze over and you've lost them.
January 22, 2014 at 10:50 am
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
OMG thats awesome! (or obvious to everybody, depending on your sarcasm detector's sensitivity 😉 )
So then given that we've been connecting Excel to databases for all this time, what would be the advantage of putting these constraints into Excel?
Excel is not always the front end for a RDMS. For example, we may acquire reference data from the web or some other 3rd party source, which is then imported into the RDMS. A company with a small product line may maintain their price list in Excel, which is then periodically loaded into the RDMS.
Even if the Excel sheet is initially loaded from a RDMS, the users may perform edits, which are then subsequently loaded back into the RDMS. Without strongly typed columns (schema) and input constraints in Excel, there can be data entry errors which cause problems for the ETL process.
Typing columns (rather than just individual cells) and enforcing check and null-ability constraints isn't so hard to imagine in a spreadsheet. It's not about using Excel as database, it's about insuring data entry is valid before submitting it to the database ETL process.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 22, 2014 at 11:04 am
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
OMG thats awesome! (or obvious to everybody, depending on your sarcasm detector's sensitivity 😉 )
So then given that we've been connecting Excel to databases for all this time, what would be the advantage of putting these constraints into Excel?
Excel is not always the front end for a RDMS. For example, we may acquire reference data from the web or some other 3rd party source, which is then imported into the RDMS. A company with a small product line may maintain their price list in Excel, which is then periodically loaded into the RDMS.
Even if the Excel sheet is initially loaded from a RDMS, the users may perform edits, which are then subsequently loaded back into the RDMS. Without strongly typed columns (schema) and input constraints in Excel, there can be data entry errors which cause problems for the ETL process.
Typing columns (rather than just individual cells) and enforcing check and null-ability constraints isn't so hard to imagine in a spreadsheet. It's not about using Excel as database, it's about insuring data entry is valid before submitting it to the database ETL process.
I personally think Microsoft did about as good a job as is practical, VBA has numeric, null checks, oledb connectors and for that matter, an express edition of SQL that holds a non insignificant amount of data that we can connect spreadsheets to with import, export functions, adodb objects for vba, and even VSTO (managed addins using Visual Studio), they practically throw the kitchen sink in already, and with all of these options available, I just don't see the practicality of what you're suggesting. Obviously just an opinion.
January 22, 2014 at 11:23 am
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
OMG thats awesome! (or obvious to everybody, depending on your sarcasm detector's sensitivity 😉 )
So then given that we've been connecting Excel to databases for all this time, what would be the advantage of putting these constraints into Excel?
Excel is not always the front end for a RDMS. For example, we may acquire reference data from the web or some other 3rd party source, which is then imported into the RDMS. A company with a small product line may maintain their price list in Excel, which is then periodically loaded into the RDMS.
Even if the Excel sheet is initially loaded from a RDMS, the users may perform edits, which are then subsequently loaded back into the RDMS. Without strongly typed columns (schema) and input constraints in Excel, there can be data entry errors which cause problems for the ETL process.
Typing columns (rather than just individual cells) and enforcing check and null-ability constraints isn't so hard to imagine in a spreadsheet. It's not about using Excel as database, it's about insuring data entry is valid before submitting it to the database ETL process.
I personally think Microsoft did about as good a job as is practical, VBA has numeric, null checks, oledb connectors and for that matter, an express edition of SQL that holds a non insignificant amount of data that we can connect spreadsheets to with import, export functions, adodb objects for vba, and even VSTO (managed addins using Visual Studio), they practically throw the kitchen sink in already, and with all of these options available, I just don't see the practicality of what you're suggesting. Obviously just an opinion.
Most users who enter data into Excel are not programmers and don't work with VBA.
I think it would be useful to simply click on a column header and specify a hard constraint that every cell in that column be restricted (not just formatted) as numeric with 2 decimal places and contain no missing values in a range. Likewise, when entering something like classification codes or state abbreviations into a column, there should be a feature that disallows any value that isn't included in another columns containing a distinct list of valid codes. That basic functionality should be accessible without resorting to VBA script programming.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 22, 2014 at 11:34 am
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/22/2014)
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Of course we can pull data into Excel from a RDBMS using Data... From Connections...
OMG thats awesome! (or obvious to everybody, depending on your sarcasm detector's sensitivity 😉 )
So then given that we've been connecting Excel to databases for all this time, what would be the advantage of putting these constraints into Excel?
Excel is not always the front end for a RDMS. For example, we may acquire reference data from the web or some other 3rd party source, which is then imported into the RDMS. A company with a small product line may maintain their price list in Excel, which is then periodically loaded into the RDMS.
Even if the Excel sheet is initially loaded from a RDMS, the users may perform edits, which are then subsequently loaded back into the RDMS. Without strongly typed columns (schema) and input constraints in Excel, there can be data entry errors which cause problems for the ETL process.
Typing columns (rather than just individual cells) and enforcing check and null-ability constraints isn't so hard to imagine in a spreadsheet. It's not about using Excel as database, it's about insuring data entry is valid before submitting it to the database ETL process.
I personally think Microsoft did about as good a job as is practical, VBA has numeric, null checks, oledb connectors and for that matter, an express edition of SQL that holds a non insignificant amount of data that we can connect spreadsheets to with import, export functions, adodb objects for vba, and even VSTO (managed addins using Visual Studio), they practically throw the kitchen sink in already, and with all of these options available, I just don't see the practicality of what you're suggesting. Obviously just an opinion.
Most users who enter data into Excel are not programmers and don't work with VBA.
I think it would be useful to simply click on a column header and specify a hard constraint that every cell in that column be restricted (not just formatted) as numeric with 2 decimal places and contain no missing values in a range. Likewise, when entering something like classification codes or state abbreviations into a column, there should be a feature that disallows any value that isn't included in another columns containing a distinct list of valid codes. That basic functionality should be accessible without resorting to VBA script programming.
Well to be honest, I don't do much excel, but menu option -> Data -> Validation pretty much seems to cover this sort of thing.
edit: this looks to be the ribbon centric version, but I'm on an old version of excel right now:
January 23, 2014 at 5:34 am
patrickmcginnis59 10839 (1/22/2014)
Gary Varga (1/22/2014)
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
I disagree.
Excel can be considered a database but not a Database Management System (DBMS). Often, particularly in technology circles, we treat the term database as though it is synonymous with DBMS but that is not technically true.
But this just really dillutes the message Jim P is trying to get across, with absolutely no advantage. When you talk to end users or non IT management, you really NEED to use the word "database", because thats the common phrase, "I can't connect to the database", you NEVER hear "I can't connect to the DBMS".
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
And when you go out there with this "well, TECHNICALLY Excel can be considered..." their eyes glaze over and you've lost them.
These are fair points, however, in this technical discussion there is a claim by a technologist to peer technologists that "EXCEL IS NOT A DATABASE" which given the audience is totally untrue. This was based on a comment about adding relational integrity features to Excel (not a discussion involving end users). The technicality is essential in how we professionals treat these tools and the data. Excel spreadsheets are a data store which we need to assist with from time to time. Until we treat the end users with respect and their data stores too we will always be inhibitors.
In my opinion, Jim's incorrect position is one held by many that causes friction and issues between IT practitioners and end users. Excel spreadsheets may not be the recommended data storage mechanism of choice of IT practitioners but they need to work with end users in order to meet the end users' requirements.
Also I never said, nor ever would say, any such thing to end users. Nor did I suggest nor say anything that would even infer that I would.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 23, 2014 at 5:37 am
I agree with patrickmcginnis59 10839 that it is all there (and has been for some time).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 23, 2014 at 6:26 am
Gary Varga (1/23/2014)
patrickmcginnis59 10839 (1/22/2014)
Gary Varga (1/22/2014)
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
I disagree.
Excel can be considered a database but not a Database Management System (DBMS). Often, particularly in technology circles, we treat the term database as though it is synonymous with DBMS but that is not technically true.
But this just really dillutes the message Jim P is trying to get across, with absolutely no advantage. When you talk to end users or non IT management, you really NEED to use the word "database", because thats the common phrase, "I can't connect to the database", you NEVER hear "I can't connect to the DBMS".
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
And when you go out there with this "well, TECHNICALLY Excel can be considered..." their eyes glaze over and you've lost them.
These are fair points, however, in this technical discussion there is a claim by a technologist to peer technologists that "EXCEL IS NOT A DATABASE" which given the audience is totally untrue.
But then this means EXCEL is the dbms using this view. And them I'm going to have to start calling EXCEL a dbms and well I don't wanna do that, Its a program that edits spreadsheets!
But what I'm asking is whether going on with these technically correct (or incorrect depending on your viewpoint) definitions really helps, and when I answer my own question (talking to myself again LOL), I'm just saying that even in the technical discussions, Jim's point rings true and even rephrasing it seems to dillute the rallying cry, you know, if you want integrity of your data, put it where its managed best. Its a snappy 5 word reminder with a pretty unambiguous message in my opinion. 2 cents.
January 23, 2014 at 7:36 am
patrickmcginnis59 10839 (1/23/2014)
Gary Varga (1/23/2014)
patrickmcginnis59 10839 (1/22/2014)
Gary Varga (1/22/2014)
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
I disagree.
Excel can be considered a database but not a Database Management System (DBMS). Often, particularly in technology circles, we treat the term database as though it is synonymous with DBMS but that is not technically true.
But this just really dillutes the message Jim P is trying to get across, with absolutely no advantage. When you talk to end users or non IT management, you really NEED to use the word "database", because thats the common phrase, "I can't connect to the database", you NEVER hear "I can't connect to the DBMS".
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
And when you go out there with this "well, TECHNICALLY Excel can be considered..." their eyes glaze over and you've lost them.
These are fair points, however, in this technical discussion there is a claim by a technologist to peer technologists that "EXCEL IS NOT A DATABASE" which given the audience is totally untrue.
But then this means EXCEL is the dbms using this view. And them I'm going to have to start calling EXCEL a dbms and well I don't wanna do that, Its a program that edits spreadsheets!
But what I'm asking is whether going on with these technically correct (or incorrect depending on your viewpoint) definitions really helps, and when I answer my own question (talking to myself again LOL), I'm just saying that even in the technical discussions, Jim's point rings true and even rephrasing it seems to dillute the rallying cry, you know, if you want integrity of your data, put it where its managed best. Its a snappy 5 word reminder with a pretty unambiguous message in my opinion. 2 cents.
EXCEL IS NOT A DBMS!
If you are happy with that then so am I as I think that the statement above captures more succinctly what the issue is with using Excel in the ways that it often is and covers both Jim's point and remains a rallying cry.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 23, 2014 at 7:44 am
Typical Business view -
Users are familiar with Excel.
It only takes mere seconds to do almost anything in Excel.
With Control comes Cost.
Many Businesses likely do not add up the hidden costs of using Excel.
Time is Money.
IT takes Time.
IT is a waste of Money if it takes more Time than doing it in Excel.
Reality -
Some things may be OK in Excel.
Many users are very comfortable with Excel, and like to be creative.
But if you depend data in Excel to make critical Business Decisions, make sure you have done enough to minimize the Risk.
It's all about acceptable Risk vs. Cost.
I think from an IT side, it can be hard to get realistic costs for the whole picture.
It remains hidden from view.
We too can mess up. Many of those users know some subtle things in the data that unless you have matching logic, will always give them a reason to use your data as nothing more than a better starting point for them.
The challenge is how to get both sides working towards the same thing - accurate, timely data. Which can be quickly visualized to improve the bottom line.
January 23, 2014 at 7:59 am
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
Nowhere did I say strip the users of capabilities. But if you are going to make Excel a cornerstone of company decisions I hope your company has St. Simeon as their saint.
patrickmcginnis59 10839 (1/23/2014)
But then this means EXCEL is the dbms using this view. And them I'm going to have to start calling EXCEL a dbms and well I don't wanna do that, Its a program that edits spreadsheets!But what I'm asking is whether going on with these technically correct (or incorrect depending on your viewpoint) definitions really helps, and when I answer my own question (talking to myself again LOL), I'm just saying that even in the technical discussions, Jim's point rings true and even rephrasing it seems to dillute the rallying cry, you know, if you want integrity of your data, put it where its managed best. Its a snappy 5 word reminder with a pretty unambiguous message in my opinion. 2 cents.
I see nothing wrong with putting data out to an Excel SS for the graphing capability. Even to use for scheduling, contact list or similar things. In addition if you find a department or person using Excel on-the-fly it generally is fine. But again if it is a cornerstone of the business, I think it is the best interest to transition it to a backed-up, repeatable, consistent DB app.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
January 23, 2014 at 8:10 am
As it stands today, if a user imports data from SQL Server or another relational database into an Excel spreadsheet, they can mangle the data contained in the sheet (often times inadvertently). Not until an attempt is made to import the sheet back into the database will the data issues be discovered.
Simply asking that Excel carry over typed columns (meta data) and constraints (rules) along with the data when a sheet is imported from a relational data source, and then enforce the data types and constraints within the Excel speadsheet grid, is not the same as expecting Excel to be a Database Management System.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 23, 2014 at 8:23 am
Jim P. (1/23/2014)
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
Nowhere did I say strip the users of capabilities. But if you are going to make Excel a cornerstone of company decisions I hope your company has St. Simeon as their saint.
patrickmcginnis59 10839 (1/23/2014)
But then this means EXCEL is the dbms using this view. And them I'm going to have to start calling EXCEL a dbms and well I don't wanna do that, Its a program that edits spreadsheets!But what I'm asking is whether going on with these technically correct (or incorrect depending on your viewpoint) definitions really helps, and when I answer my own question (talking to myself again LOL), I'm just saying that even in the technical discussions, Jim's point rings true and even rephrasing it seems to dillute the rallying cry, you know, if you want integrity of your data, put it where its managed best. Its a snappy 5 word reminder with a pretty unambiguous message in my opinion. 2 cents.
I see nothing wrong with putting data out to an Excel SS for the graphing capability. Even to use for scheduling, contact list or similar things. In addition if you find a department or person using Excel on-the-fly it generally is fine. But again if it is a cornerstone of the business, I think it is the best interest to transition it to a backed-up, repeatable, consistent DB app.
I said "stripping the users of their productivity" not "strip the users of capabilities". These are not the same. Not by a long shot.
And I would never recommend that Excel should be "a cornerstone of the business" but I do feel that we have to assist in creating a solution as opposed to imposing one. Sometimes that means transition but it always means compromise.
If anyone is interested, I am far from being an Excel jockey!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 61 through 75 (of 89 total)
You must be logged in to reply to this topic. Login to reply