Data Dictionary for Dimensions

Is there any easy way to create a dictionary or lookup function based on
Dimensions in a cube? We only have 4 cubes so far but the big one has some
50 dimensions and sometimes finding the attribute you want to report on is
consuming.
Would like to for example to search somewhere for where the attribute
"model" is, from example below and have it return the cube and dimension
it's in:
Something like:
Cube XYZ
Dimension: Autos
Attributes:
Make
Model
Year Released
..Hello Joe,
I am not sure what version of Analysis services you are using so I am
going to assume that you are using AS 2005.
With AS 2005 you have the ability to create perspectives. These can be
used to group dimensions and measures into common areas for reporting.
Perspectives can be defined to the attribute and measure level. You can
also create a linked cube that can maintain the perspectives across all
your cubes.
You could also consider using report builder as a front end. It
provides a good ad hoc query interface, but is not a replacement for
pivot tables in Excel. Report Builder creates a semantic layer on top
of the cube that allows users to search for attributes and entities
when creating a query.
To use this you will need Reporting services 2005 installed. Check out
my blog post on creating a report model against AS 2005 cubes.
http://bi-on-sql-server.blogspot.co...er-and-udm.html
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||underprocessable|||Hello Joe,
I have seen this error before. It was caused by the SQL server being
renamed Check the following:
1. Use the full server name instead of local alias
2. Check that the server has not been renamed
3. Try to connect to the RS server through Management Studio
Are you only getting this error in BIDS. Have you tried accessing
Reports in Report Manager?
Myles|||I got it working well on my local box - so it must be related to the server
I'm deploying too...maybe security issues...
<Myles.Matheson@.gmail.com> wrote in message
news:1156928164.274497.324030@.i42g2000cwa.googlegroups.com...
> Hello Joe,
> I have seen this error before. It was caused by the SQL server being
> renamed Check the following:
> 1. Use the full server name instead of local alias
> 2. Check that the server has not been renamed
> 3. Try to connect to the RS server through Management Studio
> Are you only getting this error in BIDS. Have you tried accessing
> Reports in Report Manager?
> Myles
>

Data Conversion Numeric to date

I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)

I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.

However I cannot seem to be able to convert and store (in SQL) this column can anyone advise

Thanks

The source column is called AHDATE and a value is 60703 which I am told is YY/MM/DD and using a data conversion componant transforming to data type date[DT_DATE] I get 12/03/2066 00:00:00

Which is way out

Thanks for any help

Robbie

|||

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

|||

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

|||

Blackuke wrote:

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

Store it however you like. That's up to you. As is the data type of the output column from the Derived Column component.

If you want to store it in a datetime field, cast it as a datetime value. You can do this within the Derived Column component.

-Jamie

|||

Blackuke wrote:

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

|||

Blackuke wrote:

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

yeah my bad, sorry. Its just a matter of counting brackets though. Try this instead:

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],4,2)

rather than this which is what it was before (I've highlighted the offending characters in blue):

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

-Jamie

|||

Thanks guys, I have done my goal for today and that is import the data into SQL 2005 as a date which i have done thanks to your help.

I used a derived Column using the followin expression

"200" + SUBSTRING((DT_STR,5,1252)AHDATE,1,1) + "-" + SUBSTRING((DT_STR,5,1252)AHDATE,2,2) + "-" + SUBSTRING((DT_STR,5,1252)AHDATE,4,2)

I selected database timestamp as the data type and on the SQL 2005 table itself, on the date column I changed the data type to datetime

This was the only way I could get it to work.

Thanks guys for pointing me in the right direction, where shall i send the beer

(If you have a Exchange 2007 Question I am your man)!!!

|||

Jamie Thomson wrote:

Blackuke wrote:

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

yeah my bad, sorry. Its just a matter of counting brackets though. Try this instead:

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],4,2)

rather than this which is what it was before (I've highlighted the offending characters in blue):

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

-Jamie

Thanks, i managed to google around to find it out, thanks for the help, no doubt you will see more of me!!!!

|||

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

|||

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

|||

Blackuke wrote:

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

Yes.

I'll point you in the right direction but you're gonna have to piece this together yourself I'm afraid. I'm busy :)

The conditional operator will help you out:

http://msdn2.microsoft.com/en-us/sql/ms141680.aspx

-Jamie

|||

Thanks I appreciate it

|||

I could not get it working, so I decided to do 3 conditional splits and then the derived columns.

Seems to be working, now just got to work out how to do a time dimension, I have the invoice date in the fact table I think I have already made a mistake.

Thanks again

data being repeated.

Hello, I have merged a master and detail tables into a fact table in order to create a cube, the master table let's say it's a sales table, and the detail the products of the sale, I merged both tables into a single fact table, so the data for one sale is being repeated once for each detail, this is the way I want. So if my sale has a total, let's say $100, and the detail has 3 products, when I visualize the cube the total for the sale is $300, and obviously this is incorrect, and let's say too, if there are many sales with different totals and differents details, the calculation can not be a simple MAX function or AVG, so, somebody know how I can show the data as I want?

Thanks in advance!

odin_dark@.yahoo.com.mx

If you're using AS 2005, you could consider the new many-many dimension feature:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

>>

Many-to-Many Dimensions in Analysis Services 2005

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data, and get ideas for other uses such as treating medical conditions, software testing, and more.

>>

There is an example, in Adventure Works, where Sales Reason relates to Internet Sales as a many-many dimension, via the intermediate measure group: Sales Reasons.

So, in your case, the main measure group could be based on the master table, and the intermediate measure group could be based on the detail table. And dimensions like Product, which relate directly to the detail table, could be configured as many-many dimensions. In your example of $100 total sale (on the main measure group), $100 would be attributed to each of the 3 individual products, but the contribution at the All Product level should still be $100, not $300.

|||

Hi,

U can u many to many concept.But this can be solved if u proper joins while joining the table.

Some time the relation between the table is not set properly which result in repeated data.

from

Sufian

|||

Hello all, thanks for your reply, unfortunately I'm using analysis services 2000, so is there another way?

and.. wich kind of joins are u talking about, because I want the data to be repeated in the fact table to improve performance, but in the cube I want to fiter this data correctly.

thanks in advance!

|||

Hi,

By repeting the data in the fact table will not increase the performance but it will effect the performance in negative way.

When the cube is processed the it will take more time and more space to store ur aggregates and data.

I am talkin about the joins between the facttable and dimension table.

Some time when u create the cube through desing view analysis services creates the join where it fines refrence of relation keys.

HTH

from

Sufian

|||Unfortunately, I don't know of any way to achieve exactly what you want with AS 2000. In master-detail scenarios, I typically set up separate fact tables and cubes for the master and detail, but combined them in a virtual cube. Still, I had separate measures from the 2 cubes.|||

I've already tried using separate fact tables and combining them into a virtual cube, but I do need the measures "united", not separated, because, how I can know to which "master" registry corresponds the "details" registries?

|||

thanks all!, but I'm still having the problem, mohd sufian could you please send me an example about the joins between tables you're talking about? because i think i have the proper joins but the data is being repeated..

thanks in advance!

|||

Dear ,

In SSAS2005 can have more then one fact table.

So when u going the fact table with the dimension table .The join shoud be Facttable primary key is used as a refrence key in the dimension table.

The same methord is applied in all.

In this way AS will not repeat the data .

HTH

Regards

Sufian

data being repeated.

Hello, I have merged a master and detail tables into a fact table in order to create a cube, the master table let's say it's a sales table, and the detail the products of the sale, I merged both tables into a single fact table, so the data for one sale is being repeated once for each detail, this is the way I want. So if my sale has a total, let's say $100, and the detail has 3 products, when I visualize the cube the total for the sale is $300, and obviously this is incorrect, and let's say too, if there are many sales with different totals and differents details, the calculation can not be a simple MAX function or AVG, so, somebody know how I can show the data as I want?

Thanks in advance!

odin_dark@.yahoo.com.mx

If you're using AS 2005, you could consider the new many-many dimension feature:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

>>

Many-to-Many Dimensions in Analysis Services 2005

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data, and get ideas for other uses such as treating medical conditions, software testing, and more.

>>

There is an example, in Adventure Works, where Sales Reason relates to Internet Sales as a many-many dimension, via the intermediate measure group: Sales Reasons.

So, in your case, the main measure group could be based on the master table, and the intermediate measure group could be based on the detail table. And dimensions like Product, which relate directly to the detail table, could be configured as many-many dimensions. In your example of $100 total sale (on the main measure group), $100 would be attributed to each of the 3 individual products, but the contribution at the All Product level should still be $100, not $300.

|||

Hi,

U can u many to many concept.But this can be solved if u proper joins while joining the table.

Some time the relation between the table is not set properly which result in repeated data.

from

Sufian

|||

Hello all, thanks for your reply, unfortunately I'm using analysis services 2000, so is there another way?

and.. wich kind of joins are u talking about, because I want the data to be repeated in the fact table to improve performance, but in the cube I want to fiter this data correctly.

thanks in advance!

|||

Hi,

By repeting the data in the fact table will not increase the performance but it will effect the performance in negative way.

When the cube is processed the it will take more time and more space to store ur aggregates and data.

I am talkin about the joins between the facttable and dimension table.

Some time when u create the cube through desing view analysis services creates the join where it fines refrence of relation keys.

HTH

from

Sufian

|||Unfortunately, I don't know of any way to achieve exactly what you want with AS 2000. In master-detail scenarios, I typically set up separate fact tables and cubes for the master and detail, but combined them in a virtual cube. Still, I had separate measures from the 2 cubes.|||

I've already tried using separate fact tables and combining them into a virtual cube, but I do need the measures "united", not separated, because, how I can know to which "master" registry corresponds the "details" registries?

|||

thanks all!, but I'm still having the problem, mohd sufian could you please send me an example about the joins between tables you're talking about? because i think i have the proper joins but the data is being repeated..

thanks in advance!

|||

Dear ,

In SSAS2005 can have more then one fact table.

So when u going the fact table with the dimension table .The join shoud be Facttable primary key is used as a refrence key in the dimension table.

The same methord is applied in all.

In this way AS will not repeat the data .

HTH

Regards

Sufian