combining Sat-Sun and also Fri-Sun

One of the dimensions in my cube is Weekday, with 1=Monday through 7=Sunday.

I have created named sets {1,2,3,4,5} for Mon-Fri and {6,7} for Sat and Sun. However many people also want to include Friday as the weekend, they prefer {1,2,3,4} and {5,6,7}, so I created named sets for those too.

However, I can't find any way to use these at the client level (my client is Excel Pivot tables, but I evaluated many other clients). I find named sets are only usable within SSAS's own cube browser (as a filter). Further, I can't find any way to get the cube to pre-calculate the groups. I know named sets don't get calculated, I am open to another way to get this done. I use VBA within Excel to select multiple weekdays in the PivotTable, but this causes lengthy OLAP queries each time.

If I had only one way of breaking weekday vs weekend, I would create a new table with 7 records:

1 Weekday

2 Weekday

...

6 Weekend

7 Weekend

and then create a hierarchy, the weekday/weekend level would then get pre-calculated. But since I have two possibilities, I can't do this.

I've tried all kinds of creative possibilities, does anyone have a suggestion on how to get all 11 "members" of this dimension (7 individual weekdays, plus the 4 named sets) to be pre-calculated? Thanks in advance!

Hello! The simple solution is that you use a client that will present SSAS2005 named sets, like ProClarity Professional 6.3 or Excel 2007.

Older versions of Excel do not support named sets.

If this is not possible I suggest that you add two new attributes for the two different weekday/weekend classifications.

HTH

Thomas Ivarsson

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

sqlsql

combining datasets

I have two queries, one that brings back data from a cube and another that
returns records from a db. Is there anyway inside reporting services to
combine the two data sets?Only through subreports.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:ucK4AE02FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two queries, one that brings back data from a cube and another that
> returns records from a db. Is there anyway inside reporting services to
> combine the two data sets?
>

COM DLL containing UDFs works fine with as2k but gives "function does not exist" error

Hi,

I have a legacy COM dll that contains some UDFs that we want to use with our 2005 cubes. Against an as2k cube I can get this working using the Use Library statement, but as this has been deprecated in 2005 I can't use this method. I have added the dll as an assembly in both the server and the database but I cannot access the functions within it. I just recieve a "function does not exist error".

Does anyone have any ideas?

regards

Colin

I remember COM UDF's are turned off by default.

Start SQL Server Surface Area Configuration by going through Start menu ->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.

Click on the Sufrace Area Configuration for Features. Under Analysis Services the last node is User-Defined Functions. Change that option and see if your UDF works. (You might need to restart the server.)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Unfortunately it doesn't appear to have made a difference., it still cannot see the function. I guess I will just need to rewrite in .NET.

regards

Colin

COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.

Collations in SSAS. How should it work?

Hi SSAS gurus,

Could you tell me, please, what collation gets a new dimension (cube), that collation isn't specified in create script, from Server collation or from data base collation? In the MSDN there isn't clear information related to it.

If I create a new database from script, where only the collation of the database specified, all dimension and cubes get collation from server, but not from database.
But in the SQL Server DB Engine all the objects inherit collation from database not from server.

How can I specify the collation of the data base object in the SSAS project? Is there in UI any place to set collation of database in the project?

Analysis Server implements general mechanism of static inheritance. Object's properties not specified in the create statement are getting their values based on pre-defined defaults or based on the value of parent object.

This way, the database is going to be created with server collation and any object within database keeps inheriting the database collation.

As for the collaiton exposed in the project UI, couldnt find anywhere I could change it myself :)

Simple workaround is to add following XML tag to your %ProjectName%.database file <Collation>Latin_general</Collation>

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I have following problem (probably a bug). I have spicified the collation Latin_General_Bin for the database in the database script . But the server has collation Latin_General_CI. In my script neither cubes no dimensions don't have collation specified. After script execution, all created cubes and dimesions have collation inherited from server, but not from database, es expected :-(

I have found how to set collation for the databese in UI :-(. In the BI Studio Menu Database -> Edit Database. Then in Properties window you can set collation for the database.

|||

You are probably right. This could be a bug. Though I would check one more thing.

Make sure when you are deploying, the database does not exist on the server. Deploying over existing database wouldnt change it's collation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, a am sure. The database desn't exists on the target server and database script has callation set only for database, neither for cubes nor for dimensions.