Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help me design Table structure for data like this Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 10:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:33 AM
Points: 5, Visits: 49
Hello Folks,

Please help me design the table structure to store a report which comes to me in Excel in the below format.



Notice that the Goal and Monthly values for each title have a different data types.
There are several other rows (with same/different types) in the report but I have posted just a few.

SSRS reports need to be created with similar structure.

Do let me know if you need any further clarification on the issue.
Post #1532854
Posted Tuesday, January 21, 2014 3:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:32 AM
Points: 306, Visits: 533
So you want to be able to load data straight out of Excel and into the tables?

SQL SERVER Central Forum Etiquette
Post #1532936
Posted Tuesday, January 21, 2014 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:33 AM
Points: 5, Visits: 49
The excel comes to me in a format that is shown here.
This data is to be stored in the database in a way that it can be leveraged for various other calculations and SSRS.

Dump data straight into a table with varchar datatypes vs normalized tables (excel will be parsed and tables will be loaded via SSIS) is a suggestion i am looking for
Post #1532969
Posted Tuesday, January 21, 2014 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
I would import this into a staging table and then parse it out into properly normalized tables.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1533100
Posted Tuesday, January 21, 2014 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:33 AM
Points: 5, Visits: 49
I agree.

The normalised table design is what am looking for.
Post #1533132
Posted Tuesday, January 21, 2014 8:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:32 AM
Points: 306, Visits: 533
Sean Lange (1/21/2014)
I would import this into a staging table and then parse it out into properly normalized tables.


Agreed. Looks like for each distinct row type in the spreadsheet you'd want to create a new table, with a Year column so you can tell which year each row corresponds to. Would be useful to included a loaded date so you can tell when you loaded the data in, as well as any other relevant audit info.


SQL SERVER Central Forum Etiquette
Post #1533136
Posted Tuesday, January 21, 2014 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
kalyan.ch (1/21/2014)
I agree.

The normalised table design is what am looking for.


Need quite a bit more information to be able to normalize this. I would think you have 4 tables maybe? We don't even know what this data represents. Project planning? Budgeting?

Generally having a column for each month is a bad idea, but there are times when it makes sense. Is this one of those times? No clue. Maybe all of this really belongs in a single table? Without some knowledge of what this data represents and what you are trying to do we can't offer much more than "it depends".


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1533150
Posted Tuesday, January 21, 2014 8:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:32 AM
Points: 306, Visits: 533
kalyan.ch (1/21/2014)
I agree.

The normalised table design is what am looking for.


Here's a start for one of the tables. You'd need to do the same for the rest of the tables and then load them from the staging table.

--Create a new schema to hold the new tables (optional)
CREATE SCHEMA [Finance]
AUTHORIZATION [dbo];
GO

--Check if the table exists and create it if it doesn't
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Finance].[Sales]') AND type in (N'U'))
BEGIN
CREATE TABLE [Finance].[Sales](
[SalesId] [int] IDENTITY(1,1) NOT NULL,
[SalesDescription] [varchar](50) NULL,
[Goal] [money] NULL,
[January] [money] NULL,
[February] [money] NULL,
[March] [money] NULL,
[April] [money] NULL,
[May] [money] NULL,
[June] [money] NULL,
[July] [money] NULL,
[August] [money] NULL,
[September] [money] NULL,
[October] [money] NULL,
[November] [money] NULL,
[December] [money] NULL,
[YTD] [money] NULL,
[Year] [int] NULL,
[CreatedOn] [datetime] NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[SalesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO



SQL SERVER Central Forum Etiquette
Post #1533155
Posted Tuesday, January 21, 2014 8:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 534, Visits: 790
Kind of difficult to answer without more information, but from the looks of it you would want a Sales table that has a SalesDate column. Now depending on what it is that you sell and how it gets fulfilled there will be more tables. I know this doesn't help, but the Excel report doesn't give enough information.

Here is what I can tell that you care about:
Sales - (When the sale occurred (datetime), to which customer, which staff worked on it?)
Staff(Employees) - What are the different types of positions are there and do they all count towards the utilization #
Customers - Typical stuff
Projects - Is it always a 1:1 with customers to projects? Or can there be more than one Customer to a project? What are the different steps to complete for a project, when is it considered done?
Goals - Is this company wide? Does each project have it's own goal(s)?

Start with the entities first and then work on the relationships between them. Once you have something put together post it back here and then we can see if you need additional help.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533156
Posted Tuesday, January 21, 2014 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:33 AM
Points: 5, Visits: 49
Thanks everyone for providing your inputs.

How it comes and what it is:

This data (Excel Sheet) comes from a manually prepared report for the leadership that provides them with a snapshot of how various departments (Finance, Customer Engagement) are doing in specific areas (Sales, StaffUtilization, FulfilmentTimes etc..) and so on..

What to do with it:

I need to capture this info in a database to use it and calculate other dependent metrics.
Example - Monthly/Yearly Trending reports for each of those departments in their specific areas, Gap between YTD and goals etc.

Challenge:

Each of those 'rows' (technically speaking) is of a different data type and very hard to normalize.

I have shown just 4 rows (4 datatypes) but there are more.
So I am not sure if creating n tables for n types is a good idea

What I did so far:

Created the monthly columns as decimal and converting them accordingly where needed.
Is this the right way?






Post #1533262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse