Add Invoice number into AR Cube

(0) Share

Report

Posted on by Community Member Microsoft Employee

Im trying to add the invoice number into an AR cube, I tried creating a new cube, setting up the invoice number from custrans table as an attribute, but when I deploy the cube, it doesn't show up .

Any suggestions?

Thanks in advance

*This post is locked for comments

All responses (11)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Hi Lance.

Thank you very much for your help. All is working as expected.
Lance [MSFT] on at

Like (0)

Report

RE: Add Invoice number into AR Cube

I'd probably do this as follows.

Under the Query node in AOT add a new query and give it a name.

Under the Data Sources node add CustTransOpen

Expand the data source for CustTransOpen and in the Data sources list for that data source add the tables and views that you will join to CustTransOpen.

Set the JoinMode property on the data source to InnerJoin or OuterJoin depending on which join type you want to use when joining to CustTransOpen.

For each of the data sources that will join to CustTransOpen, add a relation.

On the relation, set the Field property to the name of the field from CustTransOpen that will be involved in the join.

Also set the RelatedField property to the name of the field from the other table or view that will be involved in the join.

Either set the Dynamic property of the Fields node to Yes for every data source (this results in select * from the table or view used by the datasource). Or set the Dynamic property to No and then under each fields list add a field for each of the fields you want to select from that datasource.

Save the query and run the compile command off the context menu of the query to see if it has any errors.

Under the view node create a view. Set the query property of the view to the query you created.

Expand Metadata -> (your query ) -> data sources. to see CustTransOpen.

Expand the fields list for the data source and drag any fields you want to return in the view to the Fields node of the view.

Expand CustTransOpen->data sources to see the other data sources.

Drag any fields from those data sources that you want to select to the fields list for the view.

Save the view. Compile the view to see if it has any errors

Run the Synchronize command off the view context menu to create the view in sql.

Check the sql view definition to see if it came out how you want. If not adjust the query as needed.

Hope this helps,

Lance.

P.S. CustTableCube already includes a join to CustTable, so you may be able to pick up whatever fields you wanted from CustTable out of CustTableCube without needing to join CustTable into the query a second time.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Hi Lance

Thank you for your answer. Im trying to create a view in AOT, but with no success.

Im trying to do the following View

Select *

FROM dbo.CUSTTRANSOPEN INNER JOIN

dbo.CUSTTABLECUBE ON dbo.CUSTTRANSOPEN.ACCOUNTNUM = dbo.CUSTTABLECUBE.ACCOUNTNUM LEFT OUTER JOIN

dbo.CUSTTRANS ON dbo.CUSTTRANSOPEN.REFRECID = dbo.CUSTTRANS.RECID LEFT OUTER JOIN

dbo.CUSTTABLE ON dbo.CUSTTABLE.ACCOUNTNUM = dbo.CUSTTRANSOPEN.ACCOUNTNUM

But Im not having results I want. I'm having problems with the relationships between tables
Community Member Microsoft Employee on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Thank you John. I will try your options
jac_rod 1,299 on at

Like (0)

Report

RE: Add Invoice number into AR Cube

You can download SSDT, as well.

msdn.microsoft.com/.../mt204009.aspx
Lance [MSFT] on at

Like (0)

Report

RE: Add Invoice number into AR Cube

1. You can use both views and tables in a perspective

2. Yes you can create views in AOT. they are under Data Dictionary.

3. To specify an attribute is the key attribute, set the property Usage to Key. There has to be exactly one attribute with this setting. This is done in sql data tools.

4. Sql data tools is the editor for analysis services projects. It's a component of sql server and can be installed from the sql server installation media.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Hi Lance

Thank you very much for your detail answer. I Have some questions?

1- Why is necessary to add a table in the perspective? What is the difference between adding only views or Tables + views?

2- Is any way to create views on AOT, or I have to do it in SQL server?

3-How can I set up an attribute as a Key attribute?

4- When you mention SQL data Tool, is that SQL Server?

Thank you again

Sorry for the kind of question I'm doing, but is my first time with the cubes
Suggested answer

jac_rod 1,299 on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Ariel,

This is the piece I was talking about. In some circumstances, no, you cannot do the association between the dimension and the measure group in the AOT - you need to do this in Visual Studio.

"If the cube generation wizard was able to determine the relationship between the header table and the line table then there will already be a relationship set between them. If its not set, then add a relationship. To do so click on the button "..." in the grid cell where the row for the header dimension intersects the column for the line measure group."

Let me know if this helps.
Verified answer

Lance [MSFT] on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Adding invoice id as a group by column is going to make your analysis services database rather slow to process because ssas will have to group data (presumably invoice lines) per invoice and there are likely a high number of invoices in the invoice table. Typically an analysis services database would group only by fields that have a reasonably small set of distinct values. Thousands of records in a dimension is fine, but if your dimension key will have 10 million distinct values or more, the processing time likely to be a problem. Once you get it working, you might want to time how long it takes to process in case the process time becomes intolerable. If you keep the number of other attributes and calculations in your cube to be a small set, the cube may still process fast enough to be useful, but you'd want to check it to make sure. If its' too slow, maybe use ssrs to make a report to show calculations on invoices lines for some limited range of invoices?

As for why there's no invoice id attribute generated, without further information, I can't say where things are off in your project.

My guess is that your dimension on custtrans might have the AnalysisDimensionType property set to Transaction ... which tells the project generator that its a dimension containing only enumeration columns, in which case all string fields like invoice id would be ignored.

In general you want to make sure your project generates and that the project contains a dimension for the table containing the invoice number and the dimension has an attribute for invoice number, and that dimension is related to the cube. You probably want that invoice number attribute as your dimension's key attribute as well.

Here is an overview of defining an analysis services database using an AOT perspective. You can compare this with what you've done already in case there is a missed step.

To create a new ssas cube, you create a new perspective, set its Usage property to OLAP and add to it the tables you want to use. Expand the fields list for each table and set the properties you need. If you are something along the lines of a header and lines table, then in the line table, any fields you want counts, or sums for should have the AnalysisUsage property set to Measure and the AnalysisDefaultTotal property set to either Sum or Count. Instead of using two distinct table you also have the options of creating a view that joins the two and then using just that view.

Any fields you want to be able to group by should have the AnalysisUsage property set to Attribute. Also Set AnalysisUsage to attribute on any date fields that you want linked up to the date dimension and on the field(s) in the header table that you want to use to join the header and lines tables. If you want to change the names for the measures or attributes generated from the field, set the AnalysisLabel property. By default the field labels are used for the names.

Generate the project using the Sql Server Analysis Services project wizard and save it to disk. If you don't already have Sql Data Tool installed you'll need to install it to be able to modify the project. Open the project in Sql Data Tools. Find the file with the .cube file extension and open it in the designer. Go to the tab named Dimension Usage. It shows a grid with one side showing the fact tables (the lines tables in this case) and the axis showing the related dimension tables (the dimensions for the header table and probably the date dimension and the company dimension)

If the cube generation wizard was able to determine the relationship between the header table and the line table then there will already be a relationship set between them. If its not set, then add a relationship. To do so click on the button "..." in the grid cell where the row for the header dimension intersects the column for the line measure group.

If the dimension is using the same table/view that contains the fields that are being computed, set the relation type to Fact. No other settings are needed for this relation type.

Otherwise, if the dimension is for some other related view or table, set the relation type to regular. In the granularity attribute field pick the dimension attribute to link on. You want to pick an attribute that contains all the fields you need to link on (typically dataareaid plus some identifier column like invoice id, account number, recId etc) If needed, Analysis services allows the attribute to use multiple columns. (If you need to add another column to an attribute, you do in the dimension file by adding the column to the attribute's key fields collection). Under measure group columns at the lower right, for each column of the header table dimension attribute, pick the field from the lines table that should be joined to the header table field.

Also check which attribute is set as the key attribute for your dimension. The key attribute is the maximum level of detail you can get for the dimension, so if you had it set to say customer, then you can only group down to data per customer and per invoice data would not be available.

Build the project to make sure there's no errors. You can then import it back into AOT if desired. And you can deploy it to analysis services using the SQL analysis services project wizard. Process it through sql management studio the first time you process and make sure it has no errors (other than perhaps null key warnings indicating a join field is null). If you get processing errors there's something to fix in the project.

Hope this helps,

Lance
Community Member Microsoft Employee on at

Like (0)

Report

RE: Add Invoice number into AR Cube

Hi Jacob

I created the new cube in AOT, and I haven't looked in Visual studio. There is not new dimension created.

Can be done from the AOT?, or has to be done from Visual Studio.

Thanks