Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PanzerAttack
Yak Posting Veteran
71 Posts |
 Posted - 2011-04-19 : 08:34:11
|
| Hi, I've been doing some reading on this forum but I'm not really finding the articles I probably need and wondered if you could help by posting some useful links in here for me, which maybe of more specific use to me.Firstly, I'll explain who and whatI work in Finance and have been managing access databases with excel frontended Pivot and dashboards.The systems guys have made a 2005 version of sql available on a separate driver and I'm about to start the migration.The approach I'm taking is:01). Identify the required data, this will mostly be in the form of txt files.02). Identify who users would prefer to interact with the data03). Build the required quieries and frontendsWhere I need to do some reading is:01). What's the best way to import data into the database.02). Should I bring in descriptions with the imported txt files or use tables as I do in Access, one of the tech guys says using look up tables makes sql very slow, is this right?03). What's the best way for users to interact with the data in the database, I was told Pivots linked to sql are very slow is this right? THis is the real win for me, there are so many users, trying to identify their needs is a real nightmare, I'd prefer a web-based front end which changed as the user made selections, I might just be dreaming.If you have any good documents I could read up on, I'd be very greatful.cheers ears. |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-19 : 09:08:08
|
| Regarding #1, if you are dealing with text files of data then a simple windows console application could be used to read the text file and pipe the data into the system. Or perhaps combining all that data in the text files on to an Excel spreadsheet and then using the SQL data import wizard to just get it into SQL is an option. Sky is the limit on that one. There are so many variables that a discussion about importing the data alone could make this a very popular thread!#2, using look up tables makes SQL slow? Maybe their definition of a lookup is different than what I think of. My database philosophy is simple, the smarter I make the database the easier it is to write reports and such that are uniform. For instance, one employer might use a lookup table for Gender. I don't think anytime soon that the standard "Male" and "Female" options are going to go away. So I would create a table called xtblGenders and it would have a primary key, a name field and possibly a description. Another employer might say that you don't need a table to hold that information and that a simple column that stores a 1 for Male or a 0 for Female would work. I disagree with that approach. That puts the thinking into the hands of the programmer who then has to figure out whether those 40000 rows of 1s and 0s are men or women. To me a foreign key in the table pointing back to xtblGenders solves the problem. You join on the table and the database knows what to show. In many of the databases I worked in, about half the tables in it were lookup tables. To me they are the backbone of the "smart" end of the database. #3, web based is of course hugely popular. But I wouldn't count out a good ole desktop application or possibly even a smart phone app. I predict that the smart phones will eventually spell the death of the internet as we currently use it. I honestly still prefer desktop apps. To me they are more powerful and you don't have to jump through all the browser war BS. Just a thought.Hey, it compiles. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-19 : 09:10:39
|
| >> one of the tech guys says using look up tables makes sql very slow, is this right?No.>> I was told Pivots linked to sql are very slow is this right?No. I suspect the data will be extracted then pivoted - it depends on the amount of data and the client.If text files then probably import using ssis or bulk insert.If a not too much you might want to consider importing to excel then generating insert statements.Use lookup tables - if nothing else it saves the issue of different data.Excel is a good way of accessing the data if you are used to it - also consider reporting services for more static requirements and analysis services later on maybe.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
PanzerAttack
Yak Posting Veteran
71 Posts |
Posted - 2011-04-19 : 10:28:35
|
| Thanks for these reponses.nigelrivettI shall read up on SSIS and Bulk Imports. The files are quite large and I want to keep the database as clean as possible. For the Pivots, I would expect like Access for the data to be extracted via a query and the pivot linked into this.ajthepoolmanCan you give example of a good ole desktop application, as a reporting front end, so I can google and do some reading.Thanks for the note on apps, I'm very interested in that approach so will definitely do some reading around that. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-19 : 11:05:34
|
| I realize it's a contrived example, albeit real, but in the case of gender codes there are already established standards/codes (ISO 5218). So, if you are going to normalize the entites it would be a good idea to use existing standards. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-19 : 12:08:30
|
| Panzer, I am thinking of something inhouse. Not a packaged thing, sorry. I wasn't really clear on that. I assumed you were programming the interface. Lamprey, I don't want to hijack this thread on a ISO debate, but if my table xtblGenders has 3 rows, (Male, Female, Unknown) and their primary key IDs are 1,2,3...then how does the ISO standard really fit in? I guess I look at it from the angle that I am joining on a table and the database can figure out based on the foreign key what the value is. And since the tone of my voice can't be understood through text, I am not trying to be defensive. I am entirely open to being educated on why this should be done according to ISO standards.Hey, it compiles. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-19 : 12:31:43
|
| @ajthepoolmanLike I said, it's a bit of a contrived example as there are only 3-4 values and they seem to be pretty well known. But, let’s look at gender codes. If you use the ISO codes and you need to export your data to China or something, you don’t need to convert U, M, F and O to some Chinese equivalent because 0, 1, 2 and 9 are universal. Hence, the real gist of using standards is, well, they are standards. It gets more problematic if you try to “roll-your-own” for something more complicated, like Country Codes. Obviously people don't know about standards or choose to ignore them. A slight derivation from your original question.. But, many people may not like Joe Celko's tone on the forums, but for the most part he's right about using standards. Just look at all the code snippets posted here. Hardly any two use the same standards: Naming or Coding. Thus, one of the standards that people need to look at is the ISO 11179 standard that pertains to entity naming. Not to rip on you, but entities/Tables should never be prefixed with "xtbl" according to the ISO standard. But, maybe that is a standard you or your company as adopted. I think it's great if organizations standardize at all. However, it'd be better it they standardized on an industry standard rather than a home-grown standard. Industry standards make it that much easier for people to transition between companies and avoid issues. In short, the data-world would be a much happier place. :) |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-19 : 15:28:21
|
| That makes sense. And you are right, that was the "standard" as I was raised! As a noob 10 years ago I didn't know any better. Now I guess I would be called "set in my ways"!Hey, it compiles. |
|
|
|
|
|
|
|
|
Noob to this sql thing