Help me design Table structure for data like this

Question

Post reply

Help me design Table structure for data like this

Netjunkie

SSC Rookie

Points: 41
More actions
January 20, 2014 at 10:51 pm

#303428

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 1

So you want to be able to load data straight out of Excel and into the tables?

SQL SERVER Central Forum Etiquette[/url]

Netjunkie SSC Rookie Points: 41 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Netjunkie SSC Rookie Points: 41 More actions · Answer 4

I agree.

The normalised table design is what am looking for.

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 5

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[/url]

Sean Lange SSC Guru Points: 286589 More actions · Answer 6

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 7

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[/url]

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 8

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[/url]

Netjunkie SSC Rookie Points: 41 More actions · Answer 9

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?

Sean Lange SSC Guru Points: 286589 More actions · Answer 10

kalyan.ch (1/21/2014)
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?

Given that you are wanting to break down your data by month I would recommend not having a column for each month. You will have to unpivot data before you can really use it anyway. I think you will not regret having a year and month column. It will make it a lot easier to get trending details month to month.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/