combine OLTP with Star design

Hi,

We are working with AS for some time. We are using OWC as front end. I would like to display in the pivottable attributes of the dimension that are coming from the OLTP. For instance. I have a pivottable sales, customer by time. Next to the customer name I would like to have a column with his phone number and another with his address. Is something like that possible? [I am not concerned about the roll up - the information should only be displayed at leaf level]

To complicate the scenario, I would like to have an additional column called projection. Where the user can edit/add yearly projections for that customer. Any idea how to implement something like that?

Raphael

There are two problems you should recognize in here.

One is how to define your Analysis Services cube to include addtional information about customer phone nuber and address. For this, take a look at AdventureWorks sample project installed as part of SQL Server setup. Take a look at the Customer dimension and see how Phone and other attributes defined there.

Second, is the problem on how to display customer information in your application. BI Dev studio is using OWC as well. You can drag Customer dimension on Rows in cube browser. Drill down to the lowest level (Customer). Now right click on any member on the lowest level and select "Show properties in the report->Select All properties" and you will see all of the Customer's properties.

To be able to modify AS data directly in your application, it should support "writeback" feature. Basically it needs to know how to submit changes back to Analysis Serivices. Take a look if OWC supports writing data back to Analysis Services.

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

ColumnName Property on an Attribute

I have a Matter dimension with an OrgUnit attribute, the ColumnKey is Matter.OrgUnitKey and the ColumnName is OrgUnit.Name:

This is the select generated when processing the cube is:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1],[Foundation].[Person] AS [Foundation_Person]

WHERE (([Foundation_Person].[PrimaryOrgUnit1Key] = [Foundation_OrgUnit1].[Key])

AND([Foundation_Matter].[BillingPersonKey]= [Foundation_Person].[Key]))

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

2

Firm 2

2

Firm 3

3

Firm 1

3

Firm 2

3

Firm 3

4

Firm 1

4

Firm 2

4

Firm 3

This is more or less the select I was expecting:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1]

WHERE ([Foundation_Matter].[OrgUnit1Key] = [Foundation_OrgUnit1].[Key])

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

3

Firm 2

4

Firm 3

The question is how is the SQL statement build?

I have defined the following relationships:

Matter.OrgUnitKey = OrgUnit.Key

Matter.BillingPersonKey = Person.Key

Peson.OrgUnitKey = OrgUnit.Key

I can define a select in the DSV to get the Name with the Key but like to avoid it if I can,

Please Help

It looks like you have a loop in your relationships and SSAS has chosen to go through the person table to get to the OrgUnit. Do you have any attributes in your Matter dimension that reference the Person table? If not, you might need to create a named query in the DSV for the OrgUnit table so that there are two instances of it. In this way you could break the realtionship loop into 2 pieces so SSAS would not be able to choose the wrong path.

|||

There is a relationship Matter to Person and Person to OrgUnit.I was trying to avoid going to the DVS, but it seems unavoidable.There is more that one Dimension that will have this problem.

Thank you for you quick response.

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.