Deere has the ability to ingest Stratawise directly into the EDL for more efficient consumption. This process is recommended if data is being manually exported frequently for adhoc reporting, multiple data sources are being ingested frequently through Power BI, Tableau, etc, or the ingest performance is beginning to degrade due to the amount of data being accumulated in the Module.
Define URL for the Request
1. To retrieve the url, select the Access OData option through the export icon in the header of the Grid for the Module in scope and copy the url
2. Define the suffix of the url
- The Module name will go directly after the tenant value (in this example after 1450).
Below are examples of the suffix for both parent and subtask requests.
Parent items example (most modules):
- The Module name in this example is ISG Cost Center Form
- The Module name will go directly after the url. Be sure to include the “ / ”
“https://core.stratawise.com/odata/1450/ISG Cost Center Form”
Subtask items example:
- The Module name in this example is ISG Cost Center Form
- The Module name + “_Tasks” will go directly after the url. Be sure to include the “ / ”
“https://core.stratawise.com/odata/1450/ISG Cost Center Form_Tasks”
Define columns and column formats to be ingested
MAI will need to know the exact column names and formats for the ingest. It is not recommended to request all columns since there may be numerous technical columns unnecessary for consumption.
1. Retrieve the column names and formats from Power BI
This can be achieved by using the Power Query code at the bottom of these instructions to get the best visibility to the data MAI will be accessing. This will return all available columns and the detected format that will be used for the additional analysis below.
Note: This is also a good opportunity to confirm the most appropriate column names. Once this list is confirmed, any changes to the System Name will cause the EDL ingest to fail.
4. Save the Excel file as the suffix of the url defined above. This will be the name of the schema in the EDL once completed
Example: “ISG_Cost_Center_Form.xlsx or ISG_ Cost_ Center_Form_Tasks.xlsx”
Submit request to MAI
Although Stratawise can assist with the preparation process above, the request has to be submitted by Deere clients since the SharePoint site is managed by Deere. It is also recommended the business case and estimated cost savings are developed by the Deere client.
All requests are submitted through Deere’s SharePoint site below. Each request will be prioritized based on business case, estimated cost savings, and current backlog.
Industrial Intelligence Request Form:
https://deere.sharepoint.com/sites/MAI-Team/SitePages/MAI-Request-form.aspx?csf=1&web=1&e=dYHXX7
Provide business case, estimated cost savings, requested ingest frequency (daily, weekly, etc), any additional information required, and attach the excel file created from the previous step.
Once the request is submitted to MAI, a product owner will contact the requester to confirm the details and approve the request. The entire process from request submission to completion may take up to 6 weeks.
Caution: Once the EDL schema is created, deleting columns from the Module or renaming the System Name will cause the EDL ingest to fail.
- If columns are no longer needed, it is recommended to select the Hide in form option and deselect the Show in grid option. This will have the same effect as deleting since they will no longer be visible to end users. Once this step is completed, a follow up request can be submitted through the SharePoint site to officially delete the column. Once this is complete and confirmed, the column may be deleted from the Module.
- If a column needs to be renamed, it is recommended the Label or Column Header are changed instead of the System Name. This will have the same effect as renaming the System Name since the label and Column Header is what is displayed to end users (Label is what is displayed in the forms, and Columns Header is what is displayed in the grids).
Power Query to Retrieve All Columns and Displayed Formats for Analysis
Only a single Id is used for this query. Select an item in the Module with the most amount of column values included to get the best return. In the example below, the Id chosen is 9322.
let
ODataFilter = "Id eq 9322",
BaseUrl = "https://core.stratawise.com/odata/1450/ISG Cost Center Form_Tasks",
BaseQuery = ODataFilter,
Options = [
Query = [
#"$filter" = BaseQuery,
#"$top" = "1"
],
Headers = []
],
RawData = Web.Contents(BaseUrl, Options),
Json = Json.Document(RawData),
Value = Json[#"value"],
SourceTable =
Table.FromList(
Value,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" =
if Table.RowCount(SourceTable) = 0 then
SourceTable
else
Table.ExpandRecordColumn(
SourceTable,
"Column1",
Record.FieldNames(SourceTable{0}[Column1]),
Record.FieldNames(SourceTable{0}[Column1])
),
GetDetectedType = (v as any) as text =>
if v = null then
"null"
else if Value.Is(v, type text) then
"text"
else if Value.Is(v, type number) then
"number"
else if Value.Is(v, type logical) then
"logical"
else if Value.Is(v, type datetime) then
"datetime"
else if Value.Is(v, type date) then
"date"
else if Value.Is(v, type time) then
"time"
else if Value.Is(v, type list) then
"list"
else if Value.Is(v, type record) then
"record"
else
"other",
ColumnNames = Table.ColumnNames(#"Expanded Column1"),
ColumnProfile =
Table.FromRecords(
List.Transform(
ColumnNames,
each
let
ColumnName = _,
Values = Table.Column(#"Expanded Column1", ColumnName),
NonNullValues = List.RemoveNulls(Values),
SampleValue =
if List.Count(NonNullValues) > 0 then
NonNullValues{0}
else
null,
DetectedType = GetDetectedType(SampleValue)
in
[
ColumnName = ColumnName,
SampleValue = SampleValue,
DetectedType = DetectedType
]
)
)
in
ColumnProfile
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article