Hierarchy view for rolling up data

Most Hierarchy code I’ve found relies on having write ability to the source system to write back a hierarchy ancestor table. This is a view to roll up data in a method that is better performing and more flexible than I’ve seen. This should support up to 10 levels of parents. This is designed in an Oracle database, but it should work in any SQL environment. Please make sure there is indexes on the ID columns.

WITH RELATIONSHIP_VALUES AS (
 SELECT [ROW_ID] AS CHILD_ID,
 [ROW_PARENT_ID] AS ANCESTOR_ID
 FROM [DATA_SOURCE]
) SELECT ANCESTORS.CHILD_ID,
 CASE DISTANCE
 WHEN 0 THEN ANCESTOR_ID_0
 WHEN 1 THEN ANCESTOR_ID_1
 WHEN 2 THEN ANCESTOR_ID_2
 WHEN 3 THEN ANCESTOR_ID_3
 WHEN 4 THEN ANCESTOR_ID_4
 WHEN 5 THEN ANCESTOR_ID_5
 WHEN 6 THEN ANCESTOR_ID_6
 WHEN 7 THEN ANCESTOR_ID_7
 WHEN 8 THEN ANCESTOR_ID_8
 WHEN 9 THEN ANCESTOR_ID_9
 WHEN 10 THEN ANCESTOR_ID_10
 END
 AS ANCESTOR_ID,
 DISTANCE,
 CASE WHEN LEAFS.ANCESTOR_ID IS NULL
 THEN 1
 ELSE 0
 END
 AS IS_LEAF
FROM (
 SELECT DISTANCE_0.CHILD_ID,
 CASE WHEN DISTANCE_9.ANCESTOR_ID IS NOT NULL THEN 10
 WHEN DISTANCE_8.ANCESTOR_ID IS NOT NULL THEN 9
 WHEN DISTANCE_7.ANCESTOR_ID IS NOT NULL THEN 8
 WHEN DISTANCE_6.ANCESTOR_ID IS NOT NULL THEN 7
 WHEN DISTANCE_5.ANCESTOR_ID IS NOT NULL THEN 6
 WHEN DISTANCE_4.ANCESTOR_ID IS NOT NULL THEN 5
 WHEN DISTANCE_3.ANCESTOR_ID IS NOT NULL THEN 4
 WHEN DISTANCE_2.ANCESTOR_ID IS NOT NULL THEN 3
 WHEN DISTANCE_1.ANCESTOR_ID IS NOT NULL THEN 2
 WHEN DISTANCE_0.ANCESTOR_ID IS NOT NULL THEN 1
 END
 AS MAX_DISTANCE,
 DISTANCE_0.CHILD_ID AS ANCESTOR_ID_0,
 DISTANCE_0.ANCESTOR_ID AS ANCESTOR_ID_1,
 DISTANCE_1.ANCESTOR_ID AS ANCESTOR_ID_2,
 DISTANCE_2.ANCESTOR_ID AS ANCESTOR_ID_3,
 DISTANCE_3.ANCESTOR_ID AS ANCESTOR_ID_4,
 DISTANCE_4.ANCESTOR_ID AS ANCESTOR_ID_5,
 DISTANCE_5.ANCESTOR_ID AS ANCESTOR_ID_6,
 DISTANCE_6.ANCESTOR_ID AS ANCESTOR_ID_7,
 DISTANCE_7.ANCESTOR_ID AS ANCESTOR_ID_8,
 DISTANCE_8.ANCESTOR_ID AS ANCESTOR_ID_9,
 DISTANCE_9.ANCESTOR_ID AS ANCESTOR_ID_10
 FROM RELATIONSHIP_VALUES DISTANCE_0
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_1
 ON DISTANCE_0.ANCESTOR_ID = DISTANCE_1.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_2
 ON DISTANCE_1.ANCESTOR_ID = DISTANCE_2.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_3
 ON DISTANCE_2.ANCESTOR_ID = DISTANCE_3.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_4
 ON DISTANCE_3.ANCESTOR_ID = DISTANCE_4.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_5
 ON DISTANCE_4.ANCESTOR_ID = DISTANCE_5.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_6
 ON DISTANCE_5.ANCESTOR_ID = DISTANCE_6.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_7
 ON DISTANCE_6.ANCESTOR_ID = DISTANCE_7.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_8
 ON DISTANCE_7.ANCESTOR_ID = DISTANCE_8.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_9
 ON DISTANCE_8.ANCESTOR_ID = DISTANCE_9.CHILD_ID
 ) ANCESTORS
LEFT JOIN (
 SELECT ANCESTOR_ID
 FROM RELATIONSHIP_VALUES
 ) LEAFS
ON
 ANCESTORS.CHILD_ID = LEAFS.ANCESTOR_ID
LEFT JOIN (
 SELECT 0 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 1 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 2 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 3 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 4 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 5 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 6 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 7 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 8 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 9 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 10 AS DISTANCE
 FROM DUAL
 ) LEVEL_DISTANCES
ON
 LEVEL_DISTANCES.DISTANCE <= ANCESTORS.MAX_DISTANCE
ORDER BY 1,3;

Advertisements

Real – Time Planet Tracking System & Trajectory Prediction (Self adjusting pan mechanism [MPU-9250]){RTPT} with Arduino and GPS

I might have to wire up my telescope with this, using my LinkIt one.

Projects

Introduction

This project aims to make a system that effectively track celestial bodys (such as planets ) with a fair amount of accuracy.We will be using some algorithms along with a processing unit for the calculations and a servo mechanism to show the location of the planet physically!.The hardware used in the project is pretty much basic and simple because the primary focus of this project is on the software that is to make people understand about the algorithms and their implementations.So please bear with my “un-formatted” hardware.

Not just planet tracking you will learn some additional important things that you can implement in your other projects :

1. Planet tracking using keplers algorithms
2. Many co-ordinate systems and their interconversion
3. pan-tilt programming and servo mapping (3.5 turns Servo and 180 degree Servo )
4. MPU9250 auto-calibration programming
5. Using Madwicks/Mahony Filter to Stablise Mpu readings.
6. Yaw correction using P- controller with MPU9250

The…

View original post 201 more words

PL/SQL Query to view tablespace use

I love making tools that help others. What better use of my time, than to help others save theirs? When I went looking online, there were snippets of info about where to find total space or used space, but nothing that was a finished work of art. I compiled this from all the different snippets I found. It returns a table of all tablespaces, how large they are right now, and how much space they have left. It was also requested to have the AUTOEXTENSIBLE flag included, so that’s there too.

To run this, you need select access to DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS.

I’m going to work on making this a view for users to keep track of the space usage, and proactively address running out of space prior to ETL failing.

SELECT TOTAL.TABLESPACE_NAME,
 TOTAL.SPACE_TOTAL_IN_MB,
 NVL(USED.SPACE_USED_IN_MB,0) AS SPACE_USED_IN_MB,
 AVAILABLE.SPACE_AVAILABLE_IN_MB,
 ROUND(NVL(USED.SPACE_USED_IN_MB,0)/TOTAL.SPACE_TOTAL_IN_MB,6)*100 AS PCT_USED,
 TOTAL.AUTOEXTENSIBLE
FROM
 (SELECT TABLESPACE_NAME,
 AUTOEXTENSIBLE,
 SUM(BYTES)/1024/1024.0 AS SPACE_TOTAL_IN_MB
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME,
 AUTOEXTENSIBLE
 ) TOTAL
LEFT JOIN
 (SELECT TABLESPACE_NAME,
 SUM(BYTES)/1024/1024.0 AS SPACE_AVAILABLE_IN_MB
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME
 ) AVAILABLE
ON AVAILABLE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
LEFT JOIN
 (SELECT TABLESPACE_NAME,
 SUM(BYTES)/1024/1024.0 AS SPACE_USED_IN_MB
 FROM DBA_SEGMENTS
 GROUP BY TABLESPACE_NAME
 )USED
ON AVAILABLE.TABLESPACE_NAME = USED.TABLESPACE_NAME
ORDER BY 1;

Calculating odds of M&M pulls using PLSQL

There was a question/challenge posted to a bunch of us nerds, and I decided to go overkill by brute force calculations. The challenge came down to what would the odds be in a 6 M&M pull, of getting one of each color? I wrote a SQL script (PLSQL) that uses the observed color distribution found here and came up with my answer: 1.43267503022458011648%

98.55% of the time you’re not going to get one of each color, or of all the same color.

Also interesting to note is the odds of getting all of each of the colors:

BLUE:       0.0038303288464389083136%
BROWN:  0.0008060825508063215616%
GREEN:    0.0039315653631577133056%
ORANGE: 0.0080050508509564440576%
RED:          0.0008198418170944%
YELLOW:  0.0007791407675257913344%

Code:

WITH COLOR_DIST AS (
SELECT .1836 AS ODDS, 'BLUE' AS COLOR FROM DUAL
UNION SELECT .1416 AS ODDS, 'BROWN' AS COLOR FROM DUAL
UNION SELECT .1844 AS ODDS, 'GREEN' AS COLOR FROM DUAL
UNION SELECT .2076 AS ODDS, 'ORANGE' AS COLOR FROM DUAL
UNION SELECT .1420 AS ODDS, 'RED' AS COLOR FROM DUAL
UNION SELECT .1408 AS ODDS, 'YELLOW' AS COLOR FROM DUAL)
SELECT COLOR_MATCH_TYPES
 ,SUM(ODDS) * 100 AS ODDS
 ,'1:' || ROUND(1 / SUM(ODDS), 0) AS ONE_IN_WHAT
FROM (
 SELECT CASE 
 WHEN ODDS_1.COLOR = ODDS_2.COLOR
 AND ODDS_1.COLOR = ODDS_3.COLOR
 AND ODDS_1.COLOR = ODDS_4.COLOR
 AND ODDS_1.COLOR = ODDS_5.COLOR
 AND ODDS_1.COLOR = ODDS_6.COLOR
 THEN 'SAME COLOR ' || ODDS_1.COLOR
 WHEN ODDS_1.COLOR NOT IN (
 ODDS_2.COLOR
 ,ODDS_3.COLOR
 ,ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_2.COLOR NOT IN (
 ODDS_3.COLOR
 ,ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_3.COLOR NOT IN (
 ODDS_4.COLOR
 ,ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_4.COLOR NOT IN (
 ODDS_5.COLOR
 ,ODDS_6.COLOR
 )
 AND ODDS_5.COLOR NOT IN (ODDS_6.COLOR)
 THEN 'DIFFERENT COLORS'
 ELSE 'OTHER DISTRIBUTION'
 END AS COLOR_MATCH_TYPES
 ,ODDS_1.ODDS * ODDS_2.ODDS * ODDS_3.ODDS * ODDS_4.ODDS * ODDS_5.ODDS * ODDS_6.ODDS AS ODDS
 FROM COLOR_DIST ODDS_1
 CROSS JOIN COLOR_DIST ODDS_2
 CROSS JOIN COLOR_DIST ODDS_3
 CROSS JOIN COLOR_DIST ODDS_4
 CROSS JOIN COLOR_DIST ODDS_5
 CROSS JOIN COLOR_DIST ODDS_6
 )
GROUP BY COLOR_MATCH_TYPES
ORDER BY 1

MKR1000 AP notes

Intro

I participated in a contest to receive a MKR1000 (read it out loud, MaKeR 1000), and they liked my idea! Woot, more things to play with. I did some investigating and now I’m doing a quick write up regarding mores on the Arduino 101/MKR1000’s soft AP so you don’t have to learn what I have the long way.

WiFi Access Point?!?!

Yes, it shows up as an access point, not a peer-to-peer point. When my phone connected to it, I ran a scan from Fing (https://play.google.com/store/apps/details?id=com.overlook.android.fing&hl=en) to see what I was up against.

As you can see, everything looks good from here. The MKR1000 lives at .1, and any connected device is .100 by default. That is correct, only ONE device can successfully be connected at any given point. I think this is due to some code where the wifi is not fully capable of handling the IP addresses (like a proper DHCP server). I’ve been trying to tap into the DHCP or DNS server on the board, and I haven’t found anything yet, and this makes me think that the board has a hard-coded string to spit out to any device. “This is my SSID and info” and when connecting “this is your connection info” and these do not change, except the SSID. No password support as of this post.

Conclusion

It makes a proper access point! Sweet! Only one device can connect to it at a time though, and that device lives at .100. Understand these restrictions and you’re good to go! I’m still going to be working for a while on trying to redirect any website queries to the MKR1000’s web page for easier admin.

Notes

• The source for the code I used as framework is: https://www.arduino.cc/en/Tutorial/Wifi101SimpleWebServerWiFi
• The wifi chip in the MKR1000 and 101 are the same, so code is cross compatible.
• I still have yet to find a battery library to query charging state or battery level. I love the LinkIt ONE for having that.
• The power draw of the board itself has been said to be around 20ma. I can confirm it is about that, although with the wifi on, the board warms up. I haven’t done load tests on power usage under wifi.
• I will have to do range tests with my Pringles Cantenna.

PL-SQL Tips and Tricks

I, like every other coder, have had a series of times where we need to get creative to get something done. Here’s my documented list of Tips and Tricks. This is by no means complete, but I’ll try and update this as I come across new things.

Get the source code for Oracle materialized views:

I needed this when I lost the file that created the MV that I was working on in DEV. Using this, I was able to get the code that was committed to the DB back.

SELECT
QUERY
FROM
USER_MVIEWS
WHERE
MVIEW_NAME = '[Materialized View Name]'

Source

Oracle Row Generator using “CONNECT BY LEVEL”

This is useful when you need to generate a table with a single column of values that are incrementing. I use this when I am working through data in the ETL process.

SELECT
LEVEL just_a_column
FROM
dual
CONNECT BY LEVEL <= [Number of rows needed]

Source

 

Bitmap Join Indexes

“In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A ‘1’ means that row contains that value, a ‘0’ means it doesn’t.”

“Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.”

CREATE BITMAP JOIN INDEX <index_name>
ON <table_name> (<table_name.column_name>)
FROM <table_name, table_name>
WHERE <join_condition> 
 
-- for example:
CREATE BITMAP INDEX cust_sales_us
ON    sales(customers.state)
FROM  sales, customers
WHERE sales.cust_id = customers.cust_id;

Source

Speed testing the MPU9150’s functions using a LinkIt ONE

Background

I am working on a project where I am gathering a lot of data. One of the sources of my data is my 9Dof sensor. Since I am logging at sub-second intervals, I wanted to know how long (the cost of time) each sensor takes to return data, so I make sure to get as many data points, from as many data sources as possible.

I set up this project with the sole intention of determining the cost in milliseconds of the standard example, then try to figure out how to make it faster.

Setup

I grabbed my trusty LinkIt ONE and my 9Dof sensor (MPU9150, connecting over I2C), and looked at the example library. I wanted to get data from each of the sensors as fast as possible, and I will do post-capture processing for heading and orientation. I set my sights on these three programs:

• getAccel_Data
• getGyro_Data
• getCompass_Data

I then made blocks of C to run in a loop:

Serial.print(millis() % 1000);
Serial.print(",Acc1,");
getAccel_Data();
Serial.print(Axyz[0]);
Serial.print(",");
Serial.print(Axyz[1]);
Serial.print(",");
Serial.print(Axyz[2]);
Serial.print(",");
Serial.println(millis() % 1000);

This is my code for checking a single Accelerometer data gathering. I figured all I had to do is try running the same block, with additional runs of “getAccel_Data;” and notations of “”Acc_” so I can to comparisons afterwards. I then take the time difference between the start and end and map it to the sensor read type, and the sensor read count. After that, I subtract the time of the single run to the double and triple run to find how much longer it took for the one or two additional runs.

Accelerometer Tests

Let’s have a look at one set of my results (do note, time is in milliseconds, 1/1000^th of a second):

Row Labels	Average of Runtime	StdDev of Runtime
Acc1	23.307	2.230
Acc2	45.701	3.985
Acc3	68.123	6.254

First thing I see here is that 23.3 + 45.7 does not equal 68.1. This is good, this is what I expect, and am glad to see. My formula for calculating the actual average runtime of getAccel_Data is as follows:

( (Acc2 – Acc1) + (Acc3 – Acc1) ) / 3

This is set up to give a double weight to the difference between Acc3 and Acc1, since there was an additional run compared to Acc2 to Acc1.

Testing All Sensors

I created a few sets of functions besides the stock ones to test out.

• getAccelGyro_Data: Combined getAccel_Data and getGyro_data, eliminating duplicate runs of accelgyro.getMotion9
• getDof_Data: Combines getAccelGyro_Data and getCompass_Data
• getRawDof_Data: getDof_Data without the calculations, just raw values

Ultimately, I let my computer run, and I got 4600 records for each set of sensors. Here’s my results:

		Runtime
Function	Runs/cycle	Average	StdDev
getAccel	1	23.307	2.230
getAccel	2	45.701	3.985
getAccel	3	68.123	6.254
getAccelGyro	1	23.364	1.998
getAccelGyro	2	45.844	3.899
getAccelGyro	3	68.235	6.166
getGyro	1	23.303	2.005
getGyro	2	45.729	3.924
getGyro	3	68.192	6.200
getCompass	1	11.231	2.411
getCompass	2	21.774	4.139
getCompass	3	32.368	6.095
getDof	1	33.926	3.493
getDof	2	66.875	7.062
getDof	3	99.803	10.856
getRawDof	1	33.945	3.418
getRawDof	2	66.967	7.083
getRawDof	3	99.951	10.899

From this, I calculated the following:

Function	Actual Cycle Cost	Overhead Cost
getAccel	22.4038	0.8983
getGyro	22.4380	0.8591
getCompass	10.5597	0.6631
getAccelGyro	22.4504	0.9287
getDof	32.9420	0.9872
getRawDof	33.0094	0.9418

Conclusion

1. The library should not have “getAccel_data” and “getGyro_data”, rather just combine the two together for optimal performance.
2. Also, when we look at the ‘getDof” vs. “getRawDof”, by saving the larger values instead of reducing their size before saving, it actually costs time. About 0.0674 milliseconds per cycle.
3. You save 22.4596 milliseconds per cycle by combining gathering the sensor data into one procedure.
4. You save 0.0681 milliseconds by running “getDof_data” instead of “getAccelGyro_data” and then “getCompass”.