Would AwareIM work as a front end to a data warehouse?

Jaymer

If you had a legacy btrieve system that you wanted to extract all the data from and make available as a MSSQL READ ONLY Web system, for looking up historical data, do you think Aware would be a good fit for this?

What if it was a poorly designed system that had 300 fields in the order entry lines file?
What if you were going to have 25 million rows in order lines history?
Would you want to "flatten" it out and denormalize the data for better/traditional data warehouse type queries (making an even "wider" flat file and a lot more storage required per row), OR
would you keep it poorly normalized as is?
In fact, it could be normalized one more level (which is why it was poorly designed to begin with) at conversion time.

The backend database might appreciate it being denormalized for faster queries but I wonder how Aware would handle a long tuple.
And can you imagine writing just one Procedure/Process where you needed to copy 300 fields into another record in Aware's SQL builder window... wouldn't that be a pain?

any suggestions?
thx, jaymer...

4xjbh

I noticed one of the queries in the examples was pulling data from a plugin linked to a northwind database. Maybe your situation may require this seeing the DB already exists.

If you could get a copy of the schema I'm sure Vladimir could assist in some way so you don't spin your wheels too much...

BenHayat

Well, my first question is, are you going to use an ODBC driver to read Btrieve 5.2 version or will you use Pervasive's new driver to read the Btrieve files?
I think to use Aware, you may have to go with Pervasive driver to start with and then use SQL to access data.

Jaymer

None of the data will exist in btrieve. Its being extracted/converted/massaged into something else (MSSQL, most likely).
At that time I can normalize/denormalize.
Sorry, I'll edit the OP.
Please delete replies # 2 & 3 and then I'll delete this ... to keep this thread clean.

CalD

If you are talking denormalising, or re normalising the data is a conversion to sql off the table?

Jaymer

CalD wrote
If you are talking denormalising, or re normalising the data, is a conversion to sql off the table?

no, i'm definitely going to be using a large SQL backend db to store 25 million rows of data.

my larger issue is whether AwareIM can handle it. not the data size, but the UI.
Can it work nicely as a front end to a large dataset?

Did you see this thread

Jaymer

CalD wrote
If you are talking denormalising, or re normalising the data, is a conversion to sql off the table?

Also, a normalized data structure is preferred for insert/update transactional systems.
This is an offline historical database to be queried for previous order research/warranty info/etc.
It can fully be denormalized and still be in a SQL backend... but will Aware like it (ie. tuples with lots of fields and data length).

johntalbott

This isn't AwareIM's sweet spot.

Have you looked at Power BI? https://powerbi.microsoft.com/en-us/

tford

Jaymer,

I don't see any reason why AwareIM can't meet your needs based on what you've described especially with a normalized historical database. It sounds like there will be no updates to the historical data, so you can even build in certain flags and attributes to make searching and sorting even more efficient.

The forum post that you referenced earlier showed a very complicated query that was built to meet a specific need. AwareIM certainly has easier ways to query data .. that was just one example of the power under the hood.

Jaymer

johntalbott wrote
This isn't AwareIM's sweet spot.

Have you looked at Power BI? https://powerbi.microsoft.com/en-us/

John
We have PowerBI and are using that for other things... this app is to provide web lookup of historical data. For this I don't need the analysis, interactive reports, etc.
From what I've seen, PowerBI is slick indeed!

CalD

from what i am reading here and perhaps I am off base but if you are going to flatten the data then why not use the inbuilt filtering and sorting? you can use aware to turn on/off columns (user can do themselves), have multiple filter columns. etc is the aim for the users to search and filter the data then export it?

I'd be interested to try that with 25mil records on a reasonable but not over spec'd server. 25mil is a bit but its not a ridiculous amount in this day and age. Good index's (probably lots of them) would be the key, but you are in the fortunate position where by you are not updating the data so the overhead of lots of index's are largely negated, biggest cost will be disk... and that is cheap anyway.

Jaymer

Results of my 1st test. ALSO Big concerns BELOW

I decided to try accessing the btrieve data via AWARE, using PSQL 12 server and JDBC drivers.
This provides a chance to do a proof of concept to the powers that be without investment all the way into MSSQL, etc.

The builtin filtering/searching seems to work... but at first, I thought there was a problem reading my btrieve data.

I made a query to view "All Order Lines", and when I ran it, I only saw the headings. No data. The query for "All Orders" worked and displayed data fine. I figured something was amiss in the field mapping or something and AIM didn't read the data correctly and just crapped out.
Problem was that it [PSQL engine, I guess] was reading through all 100k recs and if you gave it a minute or two, the data displayed. There was no error after all. In fact, when I view Order Headers, you can see a tiny delay between the heading line display... and then the data appears. But its only like 2 seconds compared to 60 seconds. (This makes sense [a little] if you think about it as AIM displays the Page Navigation arrows, so it somehow knows how many recs are out there and gives you a way to page through them.)
File size is 40k headers with 100k lines.

In reality, FOR WHAT I'M DOING HERE, you wouldn't go hit the full 100k Order Lines file anyway, as you'd only be using the primary key to find the lines on a given order. Thats works fine and fast as you'd expect. I haven't tried any processes to see how long it would take to step through records, update fields, tally or do reporting.

CONCERNS
But if you wanted to just give users access to that Order Lines file, then I'd be concerned with why it takes 60 seconds for the data to come up. Where is this bottleneck coming from? JDBC? PSQL? Sorting required behind the scenes? Dunno. But if I use the built in editing button, where SAVE-CLOSE buttons are on the bottom, when I click CLOSE, it takes 60 seconds AGAIN to go back a screen. (Sure, then make it a popup, thats not the point).
I can only imagine this time would grow with more than 100k recs in the file.
So, part of continuing the Proof of Concept would be figuring out whats causing this delay and putting front and search boxes on to filter that initial hit on the db.
FORTUNATELY, this need isn't to give users search access to millions of rows from a BI perspective - its to lookup historical orders from known values - not just crazy searches through the data.

Work in progress,
jaymer...

Jaymer

PS _ Observation on dealing with 300 fields from outside data source

1) AIM doesn't give me the ability to rename data fields. So you're at the mercy of what programmer originally named fields, or at least whats in the DDFs.
2) AIM lets me delete fields from the BO, so I didn't need a bunch and I got rid of them. This helps A LITTLE when building a form as you don't have to see 300 fields.
3) When building a form, it sure is a hassle to deal with 200 fields - on grid output, you've got to find the 10 fields you're really interested in and get them to the top. Might be easier with that new GUI thats coming out, but scrolling through these tiny windows slows you down a bit.
4) I think its worth spending the time to go back to Magic and name the fields better - so there's less work to do in aware.

Jaymer

I def. think I'd like to do this, but it depends.

1) If I keep the data in btrieve and access via PSQL, then I'm not going to be able to change the source data. What I mean is that if I use the btr fields as they are, without writing routines to massage the data, then everything stays as it was in the production environment. This has the advantage of saving time & money and leaving files as is.
2) If I manually/physically migrate data into MSSQL, then I can dump unneeded fields AS WELL AS bring lookup descriptions into the main file.
3) BUT, I'd have to get the option of BruceL, our resident MSSQL pro, I'm wondering if I'd just build a MSSQL VIEW and use that as the data source into AIM --- not really flattening, but just giving the appearance of doing so.

When dealing with my type of data source, AIM says I can only add Shortcut fields. Apparently, I cannot even add a calculated field to make a new field for display purposes. So thats another reason to get the data the way I want it before AIM if I will use this as external data.
Of course another option would be to make a bunch of Shortcut references and let AIM do its thing, but since I have the ability to do it [at least] one step closer to the DB, then it makes sense to i.e. before AIM.

I think while doing the Proof of Concept makes it look like AIM will tackle the job, getting the data out of Btrieve and into something AIM can natively read is the best option.

ACDC

1) AIM doesn't give me the ability to rename data fields. So you're at the mercy of what programmer originally named fields, or at least whats in the DDFs.

I use stored procedures and then execute them using AIM to manipulate the external DB into a layout that AIM is happy with. Then I recreate the table in AIM with all the data from the external database. This lets me stay within AIM and use its power. I don't know if that makes sense in your situation though

tford

Jaymer,

Why wouldn't you simply:

1) One time: Access the historical database and write records into an AwareIM native normalized database of your choice. This process could include new calculated fields and also eliminate any unwanted info from the original database.

2) Access the newly created database with an AwareIM lookup BSV.