1,750
The Sales and Inventory cubes do not include the UOM as attributes that can be included in the analysis cubes/ pivot table report, however, this information is critical for us. How can the cubes be modified to include this?
*This post is locked for comments
Hello,
Your process will be a bit more complicated, as the data warehouse does not include the description field. You will need to develop some sort of integration (via SSIS or SQL stored procedures) to update the data warehouse with the values you want from the GP database. To make things more complicated, the SSIS packages for GP 10.0 installs are encrypted, so you will not be able to modify the existing packages.
My procedure to do what you need would be as follows:
1. Create a new table in the data warehouse to store the Item Category information (Company ID, Sub Category ID, Sub Category Description)
2. Create your own SSIS package to populate this table from one or more GP databases. Add this package to the nightly SQL Agent job for the cubes.
3. Add the new table to your DSV in SSAS (via BIDS).
4. Create a named calculation from the Item Master table to the new table in the DSV (trust me: do not use a join for this!!!) that "pulls" the Item Category Description from the new table into the ItemMaster table based on join conditions of Company ID and Item Category value.
5. Open the Item Master dimension and add the new named calculations to the dimension from the ItemMaster table.
6. Reprocess the dimension.
7. Reprocess all cubes that use this dimension (Sales, Purchasing, Inventory).
That's a fairly simplified way of how I would do it, but it should work for your needs.
David
Hello David,
I am having a similar issue. For the cubes that contain product information, the Item Sub-Categories 1 and 2 will only display the ID, which is not very descriptive. In GP these sub-categories have both an ID and description; I need to add the description for each sub-category to the cubes. They are both contained in the IV40600 table, but I have been unable to figure out how to add new fields to the cube using the Business Intelligence Development Studio. This is Dynamics GP10
Thanks in advance!
I re-checked the Item Current Quantity and Item Daily Quantity tables, and the U of M field does note exist in these tables. Sorry for the confusion. As in GP, the quantity values found in the Inventory cube are displayed in the base unit of measure for the particular item.
In the Purchasing tables, a field called "PurchasingUofM" exists. This will probably suffice in place of the UofM field that I mentioned above.
You did not say whether or not you could find the U of M field for the Sales Detail. Don't miss my instructions to reprocess the database after adding the field to the dimension. Even if you refresh Excel, failing to reprocess the database after making these changes will prevent the changes from appearing in your report.
David
1. Sales Detail
2. Pending Sales Orders - Dimension not found
3. Item Current Quantity - UOM not found
4. Item Daily Quantity - UOM of Field not found
5. Purchase Order Detail (if you want to add this to Purchases, too)
6. Pending Purchase Orders - UOM field not found
the attributes were not available in the refresh Excel Worksheet.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,407
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
