Basic Key Field question

I'm not sure if this is an SSAS question or an SSIS question.....I'll start here with basic theory/SSAS question.....

Simple design:

dimPriceBasis

PriceBasisKey (PK,int,not null)

PriceBasisCode (int,null)

PriceBasis (varchar(50),null)

dimTime

TimeKey (PK,int,not null)

AltDateKey (datetime,null)

factOrders

OrderDateKey (FK,int,not null)

ShipDateKey (FK,int, not null)

PriceBasisKey (FK,int, not null)

OrderID(bigint,not null)?

Amount (float, null)

Price (money,null)

I am having trouble conceptualizing key field set up. I had this cube up and running yesterday but then I started thinking about it and now I'm lost. Foreign keys in the fact table are supposed to be integer (counters) that point to integer primary keys in the dim tables. If you then combine the FK fields to serve as the primary key of the fact table I am confused when I go to load data as to how I'll generate integer counters based on the data in my OLTP Order table that will provide unique links to my dim tables and provide a unique key for the fact table. (OrderID is the OLTP table primary key.) All the data is basically coming out of one table for now.

My next question would be how to generate surrogate keys but that would be an SSIS question.

"If you then combine the FK fields to serve as the primary key of the fact table " - not sure why - it's not always applicable. For example, in your case, couldn't there be multiple orders with the same PriceBasis/OrderDate/ShipDate combination?

Basic Join Question

Say I have two tables, Table1 and Table2. Table1 has a primary key field "ID", and Table2 has a foreign key of "ID". Table1 has a one-to-many relationship with Table2, ie. there is only one entry for an ID in Table1, but there are many entries in Table2 that have that ID.

If I want to join Table1 with Table2 where Table1.ID = Table2.ID, what does the result look like?

I'm using SQL Server2000 if that makes a difference.the result of the join looks like this

stooge curly
stooge larry
stooge moe
reindeer dasher
reindeer dancer
reindeer prancer
reindeer vixen
mercury NULL
venus NULL
earth luna
mars deimos
mars phobos
jupiter ... (i'm too tired to list them all, i think there's 61 of them)

note that the rows with NULL for table2.id are only seen in a left outer join

i just thought i'd mention that, because there are several different kinds of join

rudy
http://r937.com/|||So if I have tables like the following:

Table_Planet
Planet_ID (pk)
Planet_Name

Table_Moon
Planet_ID (fk)
Moon_Name

and I join on Planet_ID, then the output will look like:

001, earth, luna
002, mars, deimos
002, mars, phobos
003, jupiter, moon1
003, jupiter, moon2
003, jupiter, moon3 (etc.)

is that correct?|||yes, that's right

what you show is the result of an inner join -- mercury and venus have no moon, so they "drop out" of the inner join

rudy|||Thanks for your help Rudy.

One more question. If I want to join (inner) three tables instead of two, and the third table is related to the first in the same way as the second table, what kind of output does that generate, and how do you construct that join statement?

Table_Planet
Planet_ID (pk)
Planet_Name

Table_Moon
Planet_ID (fk)
Moon_Name

Table_Inhabitants
Planet_ID (fk)
Inhabitant (for our example, lets pretend there are aliens)|||ah, then you're in trouble :(

you will get a "cross join" effect

let's say all three stooges live on mars

mars curly deimos
mars curly phobos
mars larry deimos
mars larry phobos
mars moe deimos
mars moe phobos

basically, if two unrelated tables (planet inhabitants and planet moons) are joined to the same table, you will get this cross-join effect

not much you can do about it except "don't do that then"
( http://www.jargon.net/jargonfile/d/Dontdothatthen.html )|||I guess I "won't do that then", although it would be nice. If we were somehow able to relate Table_Moon and Table_Inhabitants, would it then be feasible? Join moon and inhabitants and then join the result to planet?|||no, "join moon and inhabitants" is the part that is going to give you the cross-join effect|||Even if they were related? So that means that joining more than two tables is not ever advisable?|||no, no, no

if they were related, you wouldn't have the cross-join problem

you can join as many tables together as you like, provided they are related

you really ought to try it yourself with a few test tables

you say you are using sql server 2000, so get busy and create some tables and some joins...|||I'll do that. I appreciate all of your help!

Basic data type question

What type of field do I create for OLE objects? (text,
ntext...) What field size?
thksJanice,
text,ntext and image sare the BLOB data types in SQL Server.ntext can
support unicode data and thats the only difference between ntext and text
while image datatype is used to store binary data .text and image support
upto 2GB and there isnt any need to specify the field size while ntext, in
terms of storage size, is two times the number of characters entered.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Janice Gilbert" <jgilburt@.yahoo.com> wrote in message
news:080501c33f26$d85d0db0$a001280a@.phx.gbl...
> What type of field do I create for OLE objects? (text,
> ntext...) What field size?
> thks

Barcode Help

I am using Centura Report Builder to pull data from a SQL Database.
I need to print barcodes from a field and cantinate an * at the begining and
end of this field.
I cant figure this out.
Thanks,
MIke
Sorry if this is not the right group
Miike schrieb:
> I am using Centura Report Builder to pull data from a SQL Database.
> I need to print barcodes from a field and cantinate an * at the begining and
> end of this field.
> I cant figure this out.
> Thanks,
> MIke
> Sorry if this is not the right group
For Centura (aka gupta) Report Builder Questions i suggest you look at
the newsgroup.guptaworldwide.com Server. There you'll find many skilled
gupta developers.
hth
Gregor Stefka

Barcode Help

I am using Centura Report Builder to pull data from a SQL Database.
I need to print barcodes from a field and cantinate an * at the begining and
end of this field.
I cant figure this out.
Thanks,
MIke
Sorry if this is not the right groupMiike schrieb:
> I am using Centura Report Builder to pull data from a SQL Database.
> I need to print barcodes from a field and cantinate an * at the begining a
nd
> end of this field.
> I cant figure this out.
> Thanks,
> MIke
> Sorry if this is not the right group
For Centura (aka gupta) Report Builder Questions i suggest you look at
the newsgroup.guptaworldwide.com Server. There you'll find many skilled
gupta developers.
hth
Gregor Stefka

Barcode Help

I am using Centura Report Builder to pull data from a SQL Database.
I need to print barcodes from a field and cantinate an * at the begining and
end of this field.
I cant figure this out.
Thanks,
MIke
Sorry if this is not the right groupMiike schrieb:
> I am using Centura Report Builder to pull data from a SQL Database.
> I need to print barcodes from a field and cantinate an * at the begining and
> end of this field.
> I cant figure this out.
> Thanks,
> MIke
> Sorry if this is not the right group
For Centura (aka gupta) Report Builder Questions i suggest you look at
the newsgroup.guptaworldwide.com Server. There you'll find many skilled
gupta developers.
hth
Gregor Stefka

Bar Chart Help

I would like to display the series field at the start of a bar instead of
using a legend.
thanks in advanceI'm not sure I understand the question correctly. A chart of type "bar" is
actually drawn with horizontal bars and the labels of the categories are
shown as text along the y-axis. Isn't that exactly what you are looking for?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:7D560240-EF6A-4568-91E4-A3A8EB4CE0A3@.microsoft.com...
> I would like to display the series field at the start of a bar instead of
> using a legend.
> thanks in advance|||id like to have no y axis shown 9due to space limitations) but the name from
the y axis at the left hand side in the bar itself. Hope this makes sense.
"Robert Bruckner [MSFT]" wrote:
> I'm not sure I understand the question correctly. A chart of type "bar" is
> actually drawn with horizontal bars and the labels of the categories are
> shown as text along the y-axis. Isn't that exactly what you are looking for?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:7D560240-EF6A-4568-91E4-A3A8EB4CE0A3@.microsoft.com...
> > I would like to display the series field at the start of a bar instead of
> > using a legend.
> >
> > thanks in advance
>
>|||You could set the category label expressions to a blank: =" "
Then, use datapoint labels to show descriptions on the datapoint bars inside
the chart.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:06EE7101-A06C-457C-B615-41FD45E7563F@.microsoft.com...
> id like to have no y axis shown 9due to space limitations) but the name
from
> the y axis at the left hand side in the bar itself. Hope this makes sense.
> "Robert Bruckner [MSFT]" wrote:
> > I'm not sure I understand the question correctly. A chart of type "bar"
is
> > actually drawn with horizontal bars and the labels of the categories are
> > shown as text along the y-axis. Isn't that exactly what you are looking
for?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:7D560240-EF6A-4568-91E4-A3A8EB4CE0A3@.microsoft.com...
> > > I would like to display the series field at the start of a bar instead
of
> > > using a legend.
> > >
> > > thanks in advance
> >
> >
> >|||Thanks Robert,
i cant work out how to use a datapoint label.
"Robert Bruckner [MSFT]" wrote:
> You could set the category label expressions to a blank: =" "
> Then, use datapoint labels to show descriptions on the datapoint bars inside
> the chart.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:06EE7101-A06C-457C-B615-41FD45E7563F@.microsoft.com...
> > id like to have no y axis shown 9due to space limitations) but the name
> from
> > the y axis at the left hand side in the bar itself. Hope this makes sense.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > I'm not sure I understand the question correctly. A chart of type "bar"
> is
> > > actually drawn with horizontal bars and the labels of the categories are
> > > shown as text along the y-axis. Isn't that exactly what you are looking
> for?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > news:7D560240-EF6A-4568-91E4-A3A8EB4CE0A3@.microsoft.com...
> > > > I would like to display the series field at the start of a bar instead
> of
> > > > using a legend.
> > > >
> > > > thanks in advance
> > >
> > >
> > >
>
>|||You will find a sample report at the bottom of this posting.
In particular investigate
* the category grouping label expression
* the datapoint label expression of the "UnitsInStock" series
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:04DAD287-5566-487C-903B-4F5DD719DF21@.microsoft.com...
> Thanks Robert,
> i cant work out how to use a datapoint label.
>
> "Robert Bruckner [MSFT]" wrote:
> > You could set the category label expressions to a blank: =" "
> > Then, use datapoint labels to show descriptions on the datapoint bars
inside
> > the chart.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:06EE7101-A06C-457C-B615-41FD45E7563F@.microsoft.com...
> > > id like to have no y axis shown 9due to space limitations) but the
name
> > from
> > > the y axis at the left hand side in the bar itself. Hope this makes
sense.
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > I'm not sure I understand the question correctly. A chart of type
"bar"
> > is
> > > > actually drawn with horizontal bars and the labels of the categories
are
> > > > shown as text along the y-axis. Isn't that exactly what you are
looking
> > for?
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:7D560240-EF6A-4568-91E4-A3A8EB4CE0A3@.microsoft.com...
> > > > > I would like to display the series field at the start of a bar
instead
> > of
> > > > > using a legend.
> > > > >
> > > > > thanks in advance
> > > >
=========================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>BottomCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Value>=First(Fields!ProductName.Value)</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsOnOrder.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Top>1in</Top>
<Title />
<Width>5.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=" "</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>3in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Units In Stock</Label>
</StaticMember>
<StaticMember>
<Label>Units On Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.625in</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>4.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f6248fbe-a9a8-40b9-8084-1f56b066026d</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select top 6 * from products</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>205aa2a3-8ec1-473c-a700-2270ed171aac</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Bad data

Some knuckleheads have used a cutom indexing app to create several thousand records with this problem: The indexing app permitted field entries longer than the db receiving the index. IE, a 100-char entry when the DB only accepted 50 characters. The app had no safeguard against this error.

Does Oracle or PL/SQL have a function that will allow me to select all records where the content exceeds a certain number of characters in length? Actually in this case it would be better to find strings exactly 50 chars long. The data has been imported to our Oracle db, and I need to find these bad records!Try:

select * from myTable
where length(col_x) = 50;

This will list all rows where col_x is 50 characters long.

:cool:|||That's it! Thanks.

bp