July 9, 2011 at 3:06 am
Hey Guys,
I've studied Database development over a year ago but haven't used it practically.
I am trying to develop a database to gather financial data (KPIs) from multiple companies
Since most of the KPIs are fixed (but some companies has more or less KPIs), I decided to use a bridge entity between the company table and KPI data. The bridge entity includes the values and the date.
But I can't figure out how to insert the data. keep in mind that I don't want to use the ID of the Companies and KPIs because of the large number of data. I want to use the "Names" of KPI and Company. and When a KPI doesn't exist, it should be inserted into the KPI table
I will include a part of my ERD in the attachment.
P.S. I have no problem using ASP.NET (If there is no way to solve it through SQL server only)
July 9, 2011 at 6:27 am
Where exactly do you get stuck?
The concept I would use is to call a stored procedure with the parameter Comp_Name, KPI_name, date and value. You'd need to make sure Comp_Name and KPI_name do have a unique constraint and are unique. The tricky part would be to make sure to make sure there are no two companies using the same name (e.g. MyComp Inc. from NYC and MyComp Inc. from L.A.). In such a case the table constraint won't help.
You might need to consider using a unique company Id that's officially available (I'm not sure if there's such a number in the U.S., but here in Germany we could use a tax number).
A similar issue are the KPI names: do you need to diffferentiate case sensitive KPI names? (is MyKPI identical to myKPI?)
Once those issues are clarified, you could use the following steps within the stored procedure:
1) data validation
2) insert the new KPI if it doesn't exist
3) insert data into tb_Values using Comp_Name and KPI_name and the related tables as lookup for the ID values.
As a side note: If there is more than one value available that needs to be inserted you might want to use a concept to provide all data at once instead of row-by-row...
July 10, 2011 at 5:16 am
I am stuck on inserting multiple rows into multiple tables using stored procedure. Also, the data is stored in an excel sheets
I have no problem inserting Them into one table. by creating a CSV file and by using Bulk function I can insert them.
I've been looking in many sites of how to do it but I couldn't find any useful way that's similar to my case.
I am thinking of creating one table and insert all the data there using bulk and then try to find a way to do my objective. but again I am stuck in that
P.S. Thank you I've got the general idea and there are some points that I didn't realize until you've mentioned them
July 10, 2011 at 5:26 am
Can you provide a short test scenario (table def for the import table and the target tables including a few rows in each table) so we have something to work with?
You should have a look at the first link in my signature on how to post sample data in a ready to use format.
Based on that we can show you a concept how it can be done (probably there will be more than one option together with a discussion of the pro and con).
July 11, 2011 at 1:40 am
Sorry for late response,
I've created a simpler database to be a testing database. The original one has more tables but once I don't think I will have a problem if I solve the problem with this one.
This is the SQL code for my tables
CREATE TABLE test_company(
com_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
com_name char(25) not null)
CREATE TABLE test_kpi_type(
kt_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
kt_name char(25) not null)
CREATE TABLE test_currency(
cur_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
cur_name char(25) ,
cur_symbol char(5) not null)
CREATE TABLE test_kpi(
kpi_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
kpi_name char(100) not null,
cur_ID INT FOREIGN KEY REFERENCES test_currency(cur_ID)
)
CREATE TABLE test_value(
kpi_ID INTFOREIGN KEY REFERENCES test_kpi(kpi_ID),
com_ID INTFOREIGN KEY REFERENCES test_company (com_ID),
v_date date not null,
v_value1 float,
v_value2 float,
kt_ID INT FOREIGN KEY REFERENCES test_kpi_type(kt_ID),
CONSTRAINT v_pk PRIMARY KEY (kpi_ID,com_ID,v_date))
I am stuck with the insertion the data into these tables.
the data is in spreadsheets (for each month or quarter) but I will convert them into CSV file if needed.
and it includes the following data:
company name
kpi name
date
type (monthly, quarterly1, quarterly2 ...etc)
value 1
value 2
kpi currency symbol ($, % ....ect)
As I mentioned earlier, most kpi are the same like revenue, number of customers...etc (about 150 kpis) but some companies have its unique kpi. AND the companies will be already inserted in the database.
I hope I didn't left anything unmentioned.
Regards,
Hussin
P.S.I need your opinion in this:
you notice that I used (date) as part of a primary key the reason is that my data will be imported at the first of each month so I thought of how I wanna make each data unique and the only way I came up with is to make the date a part of the primary key. BUT should I keep it as it is or should I make it in a seperate table like the kpi and company tables?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply