Dimensions from Fact tables

Most of the Fact tables I'm building seem to have a bunch of categorization type columns in addition to the numeric columns that naturally fit into the Fact tables. However it feels "wrong" to create dimensions from a Fact table, so instead I'm getting the ETL to split the tables: for every FactX table I get a DimXInfo table that has the non-numeric/non-key data.

E.g.
FactDeal includes dealID, price, quantity, product key, customer key (and other keys that relate to shared dimensions.
DimDealInfo table includes dealID, ContractNumber, DealType

Then in the SSAS cube DimDealInfo naturally forms a Dimension and FactDeal naturally forms a measure group. And you don't have to create a SSAS Dimension from the Fact table.

Question is does this way of doing it make sense. It makes life easier at cube level but at ETL level you're getting two tables that are one to one relationship which also feels kind of "wrong". Any suggestions ?

Yes, in my case it does make sense. I use it with weblogs, and if I want to parse these into a datacube, I need to make keys for every occurence with the global @.@.IDENTITY variable.
Wich I think is really wrong.|||

Degenerate dimensions are not evil!

Indeed there are some business scenario where they are useful and sometimes the only way to model your data.

Using it is a "simple" choice if you have some alternatives (but if you have alternatives maybe you should not use DD!) or a "must" if you haven't.

In my opinion if it seems your unique solution and you don't have performance or other kind of problem (during ETL and process)...why not?

|||

Hello. This is a link to a good article about a method of what to do with fact or degenerate dimensions. It will be appropriate for SSAS2005:

http://www.intelligententerprise.com/000320/webhouse.jhtml

I think that you are on the right track.

HTH

Thomas Ivarsson

|||

Thanks everyone. I think I'm getting closer to the answer now! Yes it is degenerate dimensions I'm dealing with. In some cases I have followed Ralph Kimball's suggestion of a "Junk Dimension" to group together a few apparently unrelated items and get them out of the fact table. This works for items where there are few distinct values. (I think I should move e.g. DealType to a "Junk Dimension")

I guess the remaining item really is "ContractNumber". There is going to be a separate distinct value for every row in the Fact Table. My reasons for taking it out to a separate Dimension table are 1) Remove all text/character based columns from Fact Table to make it run faster and 2) because I need users to be able to drill right down to individual deal level when they browse a cube and the only way I can see to do that is inlcude it as a dimension.

I'm guessing that my reason 1 is not valid because we're only talking one or maybe a few columns, so probably won't have significant performance hit. But what about reason 2, can you some how drill down to a descriptive field of individual row without having to make that field a dimension ? There was something in Analysis Services 2000 that let you view all the rows that made up a specific value but can't see it in SQL 2005 and besides didn't have a client to handle it, so in the end I added ContractNumber as a dimension so any client can drill down to single row level. (Clients I'm using are : SSRS and Excel 2003).

|||

Hello.

Perhaps you are thinking about drillthrough? You can find them under actions in the cube editor.

From a contract number/id you can probably create artifical levels above the single contract by using the first two/three/four characters as levels. It is better to build these levels in a dimension.

You can create these levels in named calculations by using the TSQL function LEFT().

Regards

Thomas Ivarsson

Dimension view

Hello,
I want to make a dimension, where dimension members are either of type 1 or type 2, (to put it in a simple way). I have two views of a table, each containing plus minus 200 records, wich contains strings, wich can be used to check whether a dimension member is of type 1 or 2.
Now I can make a view as dimension table with the occurences labelled these dimension as being of type 1 or 2, but, since there is a huge amount of data being generated, and I want the dimensional database to be updated regularly using a scheduler, I fear that the performance of this update will be extremely slow, since that process needs to search through 400 records for each record to be processed.
I'd prefer to make a view wich filters the dimension members when these are already generated, so I can make MDX queries like: SELECT [Type 1] ON COLUMNS

FYI I use a CLR function (to be able to use regex - I create natural keys with it) in these views, so I really need a similar functionality.
This is my data source

view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.|||

Well, this is the data source view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.

|||I made myself a dimension table with a view using a SELECT DISTINCT on the UserAgents (so, kinda like a dimension), and then I made a dimension wich uses an inner join with this view and All_Keys table. I hope this won't give me performance issues...

Difficult Query. Is it possible?

Hi,

I am looking for a type of aggregate function for a string. Instead of finding a Max value or the average of a column, I would like to build one string value holding the aggregate.

Example:

Source data

RecordID PersonID Name Course Score

1 1 Fred Maths 70

2 1 Fred Science 78

3 2 Mary Maths 65

4 2 Mary Science 60

5 2 Mary History 85

I would like my query to return the following resultset:

Name Scores

Fred 70; 78

Mary 65; 60; 85

Hi R2 DJ,

In your ASP.NET Application, using DataGrid web server control and implementing ItemDataBound event.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

You could use UDF's, but there are some limitations/dificulties.

For instance, the next sample is simple, but will only work for response sizes until varchar (max). You could do the same with text, but it would be a little bit more complicated:

create FUNCTION dbo.ConcatenateEmployeeCustomers

(

-- Add the parameters for the function here

@.iIDEmployee int

)

RETURNS varchar ( max )

AS

BEGIN

declare @.vcTotal as varchar (max )

set @.vcTotal = ''

select @.vcTotal = @.vcTotal + ISNULL ( CustomerID + ',' , '')

From orders

where EmployeeID = @.iIDEmployee

RETURN @.vcTotal

END

GO

select dbo.ConcatenateEmployeeCustomers ( EmployeeID ) , *

from employees

|||

In SQL Server 2005, you can do this using XML. For

your case, it would look something like this:

-- Adapted from an example posted by Erland Sommarskog

select

Name,

substring(IdList, 1, datalength(IdList)/2 - 1)

-- strip the last ',' from the list

from (

select distinct Name from YourTable

) as c -- or use a Names table if one exists

cross apply (

select

convert(nvarchar(30), PersonID) + ',' as [text()]

from YourTable as o

where o.PersonID = c.PersonID

order by o.RecordID

for xml path('')

) as Dummy(IdList)

Steve Kass

Drew University

www.stevekass.com

R2 DJ@.discussions.microsoft.com wrote:

> Hi,

>

> I am looking for a type of aggregate function for a string. Instead of

> finding a Max value or the average of a column, I would like to build

> one string value holding the aggregate.

>

> Example:

>

> Source data

>

> RecordID PersonID Name Course

> Score

>

> 1 1 Fred

> Maths 70

>

> 2 1 Fred

> Science 78

>

> 3 2 Mary

> Maths 65

>

> 4 2 Mary

> Science 60

>

> 5 2 Mary

> History 85

>

> I would like my query to return the following resultset:

>

> Name Scores

>

> Fred 70; 78

>

> Mary 65; 60; 85

>

>

Differential Backup in Maintenance Plan?

Hello. Can you schedule a differential backup in a
Database Maint Plan? I didn't see any way to do that, so
my guess is this is a sqlwish type of thing? Unless
someone knows a trick? Thanks, BruceHello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||Hello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||ok Andy... Yep, a scheduled job would be fine. Just not
ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>--Original Message--
>I thought I just saw Tibor answer this question a few
minutes ago. Oh well,
>no there is not a way to do it in the MP. Create your
own scheduled job and
>do it there.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:006401c34641$7daeeba0$a501280a@.phx.gbl...
>> Hello. Can you schedule a differential backup in a
>> Database Maint Plan? I didn't see any way to do that,
so
>> my guess is this is a sqlwish type of thing? Unless
>> someone knows a trick? Thanks, Bruce
>
>.
>|||Why not put ALLLLL the backups in your own scheduled jobs then to be
consistent. You have much better control over things if you don't use the
wizard and there really isn't anything the wizard can do that you can't with
a few lines of code.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0b4001c34647$70000e20$a001280a@.phx.gbl...
> ok Andy... Yep, a scheduled job would be fine. Just not
> ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>
> >--Original Message--
> >I thought I just saw Tibor answer this question a few
> minutes ago. Oh well,
> >no there is not a way to do it in the MP. Create your
> own scheduled job and
> >do it there.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:006401c34641$7daeeba0$a501280a@.phx.gbl...
> >> Hello. Can you schedule a differential backup in a
> >> Database Maint Plan? I didn't see any way to do that,
> so
> >> my guess is this is a sqlwish type of thing? Unless
> >> someone knows a trick? Thanks, Bruce
> >
> >
> >.
> >|||Bruce
You can use the backup wizard to create a differential
backup, if you do not want to code it yourself, and keep
it consistent if you need to do more than one.
Regards
John

Different versions of MS SQL Server

Hi all...
I'm a MySQL user and I want to switch to MS SQL Server, I heared that there is more than one type of MS SQL Server and one or two can be downloaded for free. Can someone tell me the different types of MS SQL Server and where can I download those free versions?freebies...

Google for MSDE download

or

SQL Server Express 2005 download

or

SQL Server 2005 CTP download

pay attention to system requirements.|||Thank you Thrasymachus...
I found the SQL Server Express 2005 but is there a Graphical Client for SQL Server that I can download?|||Don't think you can get one for free but I may be wrong on that. Enterprise Manager comes with the high end versions but I think you always have to pay for it.|||Yes there is...

http://www.microsoft.com/downloads/details.aspx?familyid=C7A5CC62-EC54-4299-85FC-BA05C181ED55&displaylang=en

I do not reccomend deploying BETA software.|||Cool. When I was installing SQL Server on my PC in April I didn't see that.

Different row delimiters in same connection manager

I have a situation where two CSV dmited types are read in. One file row type could be CRLF while another could be CR. Seeing how the only difference between these file types is the row delimiter, I would like to use one conn manager. Is it possible? Ideas?

Try this:
create a string variable Delimiter.
In Connection Manager window select your csv connection and look at its properties.
Open Expressions form and add an expression to the property RowDelimiter setting it to the variable User::Delimiter
Then you should be able to progamatically switch from one delimiter to another (toggling the variable value) before starting to load from the csv.
(i cannot try now this solution, take is as a hint)
|||

Unfortunately, this solution will not work. The RowDelimiter property is used only initially when no flat file connection columns are defined. After the columns get created the actual row delimiter is a column delimiter of the last column. You cannot do the same trick with the column delimiter because it is not expressionable.

Are you only concerned about the redundancy (with the two connection managers), or you had in mind more elegant data flow (with a multi flat file connection and one flat file source in a loop, perhaps)? I would like to find out more about your motivation to merge those two connection managers.

Thanks,

|||I am looking mainly to cut down the redundancy. There are 100+ inbound conns that need to support CRLF and CR returns, and it would end up being way too redundant to create a seperate manager for each return.|||

I can settle with just looking for CR for row delimiter. So, is there a way to maybe pad (or ignore) any LF's?

|||You can try that. The additional LF character would wrap up to the start of the first column of the next row. You should be able to clean it up using the Derived Column transform. However, I suspect it will make a headache with the incomplete last row, which would consist of a single LF character.

The solution I would go with is to have a Script Task before the Data Flow and preprocess files in it -- to unify the row delimiters.

Thanks,|||

Yah, I thought about doing this, however, won't this drain performance by having to essentially read/write the file twice (keeping in mind that some of these files can be rather large in size)? How would you handle the pre-processing in script?

Thanks.

|||

It will certainly have some performance impact. You should probably do some tests to verify how acceptable the cost is for the added manageability of your packages.

In the script, I would look for CR character and if the next one is not LF I would insert it. If you can be sure about the files with expected initial format ({CR}{LF}), you can skip those.

Thanks,

Different font size and style in the same table cell

I have a table cell that contains two things:

well code and well type (two different data fields concatenated)

for example:

4WA53102 Verticall Well

I want the code to be 12 pt arial regular

I want the well type to be 8 point italic arial

4WA53102 Vertical Well

How do I do this? Thanks

Why not put well code and well type in adjacent table cells and then you can change the formatting on the cell well code is in and the formatting on the cell well type is in to be different from each other. I don't see how it would be different from what you want to do.

It isn't possible to do what you want the way you want to do it.

Different dynamic grouping question

Hi,

I have a basic report that has some transaction info, eg

Name Type

AAA Buy

AAA Buy

AAA Sell

BBB Buy

BBB Buy

BBB Buy

BBB Sell

Etc

I want to group all the BBB's together, but I don't want to group ANY others together... Ie, I want to group the transactions together if the name is BBB, else I don't want to group at all.

Is this possible?

I hope I have given enough info.

Cheers

Rob

You can very well do the grouping on NAME field and hide the grouping depending on the value of that field.
|||

I need to be able to drill down into the complete list if needed. which means I need a group header to display the grouped data and allow the user to click the plus sign to expand the BBB records.

This won't work for two reasons, firstly I don't want anything other than BBB to be grouped at all, secondly this would show up a group header for each different name, when I'd only want a group header for the BBB group, and the others just display the details.

Hope that makes sense.

|||Anyone? This is doing my head in

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.