Can any one tell how to create pivot table

[Former Member]

Hi ,

Am trying to create a pivot tabel in the MDM Import Manager. Iam able to see the preview of the pivot table which i want to create. But when i try to click "OK" button, an error message getting displayed like...

"The new table cannot be created because the data source is not updatable.You may need the data source to an updatable format such as Microsoft Access before proceeding".

Due to the above error am not able to create pivot table, plz let me know if any one know how to solve this problem

Thanks & regards

Praveen k

[Former Member]

Hi Praveen,

Go to the below link and just give "Creating a Pivot Link" you have detail procedure along with screen shots.

” in the data source (where “table” is the name of the original source table).

11. In the drop-down list of source tables, select the newly created pivot table on which to perform subsequent import processing.

Pivoting creates additional fields out of field values. MDM automatically converts any characters in the field values that are invalid for field names to valid ones, as follows: period (.) ® underscore (_); exclamation ® vertical bar (|); square brackets ([]) ® angular brackets (<>); accent grave (`) ® single quote ('); and double quote (") ® single quote ('). In addition, MDM removes preceding and trailing spaces.

Various pivoting examples are illustrated in the following subsections.


Regards
Rehman

Reward Points if Useful

[Former Member]

Hi Praveen ,

The solution to your problem is -

The data source must be updateable in order for MDM to

create the new pivot or reverse pivot table. However, you can still

perform the steps leading up to table creation – including the preview –

even on a data source that is not updateable, so that you can explore

pivoting or reverse pivoting as a transformation option. If you then

decide you want to actually create the new table, convert the data

source into a format that is updateable (such as Microsoft Access) and

perform the pivot or reverse pivot on the new data source.

To create a pivot table, you must identify the source fields that

participate in the pivot, which ones contain metadata and which ones

contain data, which ones must be combined, the one-to-one

correspondence between metadata and data fields and/or field

combinations, and the key field or fields.

To create a pivot table:

1. In the drop-down list of source tables, make sure the table you want to

pivot is the current source table.

2. In the Source Hierarchy tree, select all the field nodes corresponding to

both the metadata fields and the data fields by which you want to pivot.

3. Right-click on one of the nodes and choose Create Pivot Table from the

context menu, or choose Source > Create Pivot Table from the main

menu

4. MDM opens the Create Pivot Table dialog

5. In the Key Fields dual-list drop-down control, move one or more fields

from the Available Fields list to the Selected Fields list to identify the

key fields on which to perform the pivot

6. In the dual-list control of fields, drag-and-drop the data fields from the

Field Values Become Field Names list to the Field Values Become

Field Values list

7. If necessary, select two or more fields in either list that must be

combined into a field combination, and click on the Combine button, or

right-click on one of the fields and choose Combine from the context

menu

8. If necessary, drag-and-drop fields or field combinations within each list

to create the one-to-one correspondence between metadata fields and

data fields.

9. Click on the Preview button to display a preview of the first ten records

of the pivot table

10. When you have verified that the pivot operation you have defined will

have the desired effect, click OK to close the Create Pivot Table dialog.

11. The MDM Import Manager creates a new table named “table <Pivot>”

in the data source (where “table” is the name of the original source

table).

12. In the drop-down list of source tables, select the newly created pivot

table on which to perform subsequent import processing.

Hope that helps ...

Regards

Deepak Singh

[Former Member]

Hi Kumar,

For MDM updatable source you need these two types of File source to create a Pivot Table (also creating Hierarchy)

1. MS Access

2. XML files

As far as i know these two are the only valid ones. Try to import the Excel file into Access and create a pivot table from the access. It should work seemlessly.

Regards

Sulaiman