====Creating a Query for Wind Turbine Power==== In this example, we're going to look at some of the ways we can ask for the data we need for the [[excel_and_powerquery]] task. We'll go through the initial step to //find// the assets and data, then a few different options for building our query. ===Finding Assets & Properties=== The first step is to identify the assets and properties we need. We've been asked to make a report showing the average //power being generated// by our //Wind Turbines//. We can view the assets at [[https://demo.optrix.com.au/s/ex/diagram/40/2]] to find out what options are available. There are a large number of wind turbines, each of which has a property named **power**. ===Option 1 - All Power Properties=== A simple way to build this query is to simply ask for //every// power value. 'Power' PROPERTY ALLPOINTS [[https://demo.optrix.com.au/s/ex/aql/api/query?query=%27Power%27+PROPERTY+ALLPOINTS|Example Response]] The only problem is that this database includes more than just the wind turbines - we're seeing power values from assets we're not interested in. ===Option 2 - Including Each Turbine in the Query=== ('Wind Turbine #1', 'Wind Turbine #2'....'Wind Turbine #22') ASSET 'Power' PROPERTY VALUES We can manually create a list of assets, but this is not only horribly time-consuming, but also too rigid - if the site makes changes, our logic would need to be re-written. Instead, we should use something more dynamic. ===Option 3 - All Turbines Of A Type=== All of these assets share a //type// named 'Wind Turbine'. So we can narrow down our set of assets by choosing only those with that type and getting the Power property. 'Wind Turbine' OFTYPE 'Power' PROPERTY VALUES [[https://demo.optrix.com.au/s/ex/aql/api/query?query=%27Wind+Turbine%27+OFTYPE+%27Power%27+PROPERTY+VALUES|Example Response]] This works nicely - we have only the assets we're interested in. The only awkward thing is their **order**. Right now, turbine #19 and #20 are mixed around. ===Option 4 - Ensuring The Right Order=== Users often prefer to see assets in a particular order - which is **not** always alphabetical. In some cases, the order is based on the //progression across a process line// - from the start of the line where raw materials are added, to the end of the line where the final product is complete. Luckily, ARDI can be given this order as a **logical [[relationships|relationship]]**, and we can use that as a reference. In this case, the Wind Turbines have a relationship named //Sequence// which connects each of the wind turbines in order. To use a relationship in an ARDI query, we need to find it with the RELATIONSHIP function. This takes a single constant parameter - the name of the relationship to search for. To actually find the related assets, we need to use the RELATED function. This has the following parameters... ^Parameter^Use^ |ASSETLIST|The asset to begin the search from| |RELATIONSHIP|The releationship to search| |CONSTLIST|A string describing how to search| The values for the constant can be... ^Value^Meaning^ |down|All assets downstream, excluding the start of the search| |up|All assets upstream, excluding the start of the search| You can add the letter 'i' at the end of the constant to make the search //inclusive// - meaning that the asset you're starting from will be included in the results. You can also add the letter 'd' at the start of any of these constants - this makes them only explore the //direct// children rather than continuing on to grand-children. The query is then built up this way... 'Wind Turbine #1' ASSET //Choose the first part of the Sequence// 'Wind Turbine #1' ASSET 'Sequence' RELATIONSHIP //Add a [[RELATIONSHIPLIST]] containing the 'Sequence' relationship// 'Wind Turbine #1' ASSET 'Sequence' RELATIONSHIP 'downi' RELATED //Get all of the assets downstream in the Sequence relationship from Wind Turbine #1// This now gets us **all of the wind turbines** and ensures they are **in the correct display order**. ===Getting History=== Finally, we need to ask for //history// for these points. To get history from ARDI, you need to use the GETHISTORY AQL function. This is one of the few functions that take a **map** as a parameter. Maps are objects that can have many different properties and are written as JSON objects. GETHISTORY parameters include... ^Parameter^Meaning^ |range|A string containing a //relative time range//| |start|A start datetime in YYYY-MM-DD HH:MM:SS format| |end|An end datetime in YYYY-MM-DD HH:MM:SS format| |grain|The [[grain|grain]] of your data request| |method|A [[gethistory method|method]], such as 'raw', 'min' or 'max| |context|A context ID if using multiple contexts| If we want to have one-minute samples across the last hour, our request would include... { "range": "1 hour", "grain": -600 } GETHISTORY ===Continue Reading=== This query should be perfect for [[analyst:excel_and_powerquery|sending data to Excel]].