SSAS Cubes - AX 2012

(0) Share

Report

Posted on by Arun B S

575

Dear All,
I am creating a new SSAS cube in AX 2012 for a finance project.
I have started creating the cube using the following link
"https://technet.microsoft.com/en-us/library/cc622026.aspx"
If we are adding multiple tables or views in the "Perspective", how those tables are related ?
i couldnt find any properties to relate those tables. On what basis these tables are related?

Even in standard cubes i saw multiple tables, but couldn't get how they are related.

Is there any other link/ reference to see, how a new cube is created and How to customise a standard cube.

Regards,
Arun B S

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Community Member on at

Like (0)

Report

Hello

Basecally the tables are linked in the datasource view.

This is the main place to influence the measures.

Based on theese relations, the behaviour of the dimensions in a cube are built and maintained under the "Dimensin usage" Tab of a cube, which is opened in Visual Studio.

Regards

Hans-Peter

Was this reply helpful? Yes No
Arun B S 575 on at

Like (0)

Report

Hi Hans,

Thanks for your reply.

I need data from the following tables,

1. GeneralJournalAccountEntry

2. GeneralJournalEntry----> Filter for a field

(In 2009: Period code should be "(Regular/Normal)"

(In 2012: "GeneralJournalEntry.FiscalCalendarYear" field stores recId, so

"FiscalCalendarPeriod" table also needed in data source, to filter

"FiscalPeriodType" should be "Operating",

3. ledgerJournalTable

4. ledgerJournalName

so do you mean that, I should not add any tables in Perspectives, instead of that i need to create a view joining these tables and add that view in Perspective ?

Thanks in Advance

Regards,

Arun

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, any table which will be used in the cubes has to be in the datasource view.

Hans-Peter

Was this reply helpful? Yes No
Suggested answer

Guy Terry 28,924 Moderator on at

Like (0)

Report

Hi Arun,

If a cube based on the G/L Transactions is your requirement, I would use the Wizard to deploy the standard AX 'General ledger' cube.

technet.microsoft.com/.../dd309683.aspx

Then you don't have to worry about creating your own perspectives in the AOT.

I can see the default G/L cube has a Dimension called 'Ledger transaction' that contains several Dimension Attributes; one of which is 'Fiscal period type'.

If you do need to modify the cube further, my preference has been to make modifications directly to the analysis services database. I only modify within the AOT if I need to add a field to the relevant View (e.g. the GeneralJournalCube view) and in this way I ignore Perspectives and never re-run the Wizard after the cubes are deployed.

Was this reply helpful? Yes No
Arun B S 575 on at

Like (0)

Report

Hi Guy Terry,

I can't use G/L cube. Still i have some additional requirement.

I need to create a new Perspective,

1. Can you tell me the Scenario, where we add tables in the perspective and where we add views in the Perspective?

2. I Created a test cube with a table, deployed and Processed it. I tried to open in Visual studio, the data Source is different. Is there anything like, I need to customise in Visual studio ?

Reference: "www.simple-talk.com/.../"

Thanks,

Arun

Was this reply helpful? Yes No
Guy Terry 28,924 Moderator on at

Like (0)

Report

Hi Arun,

I believed that the BI Wizard would take care of everything you need. i.e. you should be able to use it to create an Analysis Services database that works without going anywhere near BIDS/SSAS/Visual Studio. It should create the data source, DSV, Dimensions, Cubes, Measures and deploy it for you. The only thing you should need to create is some job that will routinely Process the Analysis Services Database.

However, it seems you may need to modify the data source if your Analysis Services Database is not called 'Dynamics AX', but it's not clear if this only needs to be done on versions of AX 2012 earlier than R2.

technet.microsoft.com/.../hh202068.aspx

This walkthrough describes how to add tables and views to a perspective:

technet.microsoft.com/.../cc622026.aspx

Earlier, you asked about how to define the relationships between the things you add to the perspective. Since the walkthrough does not mention this, I assume the Relationships defined on the Tables and Views themselves are used.

However, I did note this comment on the link below:

"If you create a measure group by using a view, the relationship between the measure group and dimensions must be specified manually in Microsoft SQL Server Business Intelligence Development Studio (BIDS) unless the view contains all fields of the backing table that are used to connect to a dimension."

technet.microsoft.com/.../cc558098.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi, i have a problem with cubes in AX 2012 R3.

I want to create one to use it in Microsoft Excel and check the information about some table or view.

To do that, i have to create a perspective and add in it tables and views.

I have been looking this video: www.youtube.com/watch

If I do the same in the video it goes perfectly. The problem is when I create a table or view that don´t exists in AOT when I try to implement the Analysis Service Project, an error appears. That error is, AX can´t found the perspective i created.

I don´t know if the problem is in some property or if i only have to use existings tables in AOT.

Could someone help me?

Thanks

Was this reply helpful? Yes No
Suggested answer

Lance [MSFT] on at

Like (1)

Report

An analysis services project gets its data from set of data source view tables, which are just sql queries that are stored in the project's data source view. The cube generation code creates a query for each table or view in the perspective. There should be relationships created between pairs of data source view tables if there is an AOT table relationship relating the tables. But if it there is no AOT a relationship, then you have a couple of options to set the relationship.

One option is to customize the analysis services project and add or modify the relationships between tables. The other option is to create an ax view that joins the related tables the way you want and then put the view into the perspective instead of the tables that make up the view. I prefer to make views myself as that means I can use the analysis services project wizard to update the project later. Sometimes, if you modify the data source view, the project wizard can't handle the project anymore. Just modifying the relationships is probably safe though, but changing the set of fields used in a query could cause problems. For this set of tables, maybe you could use a view that joins the two general journal tables and a view that joins the two ledger journal tables?

Either way after you generate the project, open it in sql data tools and review the relationships. You'll find relationships in several places. In the data source view there should be relationship joining any related views. You can add any that are missing.

Second in the .cube file, under the tab Dimension Usage, there are relationship between the measure groups (the tables from which you aggregate) and the dimensions (any other tables you group by). If there is a relationship needed between a measure group and dimension and that relationship is not present or isn't the relationship you want, then you can add or change the relationship. If the dimension and the measure group are both using the same table or view, then set the relationship type to Fact. Otherwise set the relationship type to regular, from the dimension pick the attribute you want to join. Then pick the field(s) from the measure group that should be joined to the attribute.

Hope this helps,

Lance

Was this reply helpful? Yes No