Dimension Creation Problem

I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.

Difficulty in writing a DateTime to a table

I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...

The following is a snippet...

Dim DDate As [SqlDateTime] = Now()

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"

Which won't work as I am attempting to concatinate a SqlDateTime into a string.

My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.

I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.

Hi Ian,

What is the data type of the field named Date_Time? What is the error you are getting?

I would suggest removing the quotes from your query and just letting SQL handle the data type change from SqlDateTime into what ever Date_Time is defined as. If it is a string, the conversion will likely happen automatically. (At least it does in T-SQL.)

If that doesn't work, consider using a type conversion function rather than just wraping the value in quotes. You should be able to find information about data type conversion functions in the VB help documentations.

One final consideration, if you only need to determine if a row has been updated and the actual time is not really important, you might consider the timestamp data type. This data type automatically updates when a row is added or changed, but it stores a relative time, no the time associated with a clock.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Mike,

In order: "datetime"

The error I get is a VB pre-complier error warning that I am attempting to mix "String" with "SqlDateTime".

Removing the quotes does not help as the connection to the database is made with the following command:

Dim TheCommand As SqlCommand = New SqlCommand(TheQuery, TheConnection)

Where both "TheQuery" and "TheConnection" are strings.

Unfortunately I need to know the time and date, this will be used for locating information later.

My most recent attempts have used :

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' GETDATE() ')"

Which throws "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." on execution (of the VB program).

I have attempted to set the default on the Date_Time column in "Groups" to be calculated as Getdate() and persistent, but then I get the following error: "Computed column 'DateTime' in table 'Tmp_Groups' cannot be persisted because the column is non-deterministic." I note that a calculated column in the table has no "format", which makes sense if it is not to be saved.

I am assuming that this is the correct forum, I think that the problem is related to SQL rather than .NET or VB.

I will be off the air now for 54 hours due to the weekend.

Thanks for your time Mike.

Ian

|||

It's interesting that you're getting an error specifying a timestamp field. Timestamp is different than datetime, you should make sure that you really have a datetime field.

That said, you're still trying to create string values by putting quotes around them. That isn't how you do it, you have to conver non-string values into strings using a conversion function, in this case CStr(). I managed to write an insert from VB by converting the output of Now() to a string and passing that in my query. I've povided the table definition and VB code below for you to examine.

Table Def-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Shippers](

[ShipperID] [int] IDENTITY(1,1) NOT NULL,

[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[tsTime] [datetime] NULL,

[sTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [aaaaaShippers_PK] PRIMARY KEY NONCLUSTERED

(

[ShipperID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

-- End Table -

- Code--

Sub Main()

Dim cnn As New SqlConnection

cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NorthwindSQL_Upsize;Integrated Security=True"

cnn.Open()

Dim cmd As New SqlCommand

cmd.CommandText = "INSERT INTO Shippers (CompanyName, Phone, tsTime) VALUES('VB Shippers','555-1212','" & CStr(Now()) & "')"

cmd.Connection = cnn

cmd.ExecuteNonQuery()

End Sub

- End Code --

|||

Mike,

A very astute observation, I do have an issue with the column format.

A search of my harddisk shows a total of 8 copies of this database, two I have as backups, one is a copy I was importing data into using Management Studio, the rest have uncertain pedigree, (some I no doubt created in error).

I long for the days of SQL Server 2000 when I only kept two copies of the database, (one live & one copy). Having a cut-back latest version makes it very tempting to develop & use the latest, but I wonder if SQL Server Express has been emasculated to the extent that it is no longer worthy of the title "Database". Yes, the mistake was mine, I think it was an easier mistake to make due to the "over enthusiastic feature removal".

Thank you for you help Mike.

Ian

sql