The Warehouse Users Group will meet FINALLY!
Wednesday,
December 6, 2000
1:30 PM to 3:00 PM
Life Science E Room 104
(BIG ROOM...y'all come!)
This
is Reading Day at ASU.. and for the last several
semesters we have taken advantage of classes
being
out so WUG can use the big Life Science Room!
This meeting will feature the Directory Services
Database which all STUDENT Data Warehouse
users now have access to. The rest of the
meeting will feature STUDENT data although
the BrioQuery techniques shown
may transfer to other databases.
The
agenda is still forming... so far we have...
1.
Overview of the Directory Services Database
2. Finding Students Applying for Graduation
3. Looking at Probation Status from Semester
to
Semester (using an included query object.)
4. Saying good-bye and thank you to a long
friend
of the ASU Data Warehouse.
Yell
out with any additional agenda topics you
are
interested in!
Nancy Dickson
Monday
20, Nov. 2000 2.12 PM
The
missing records have been found! The Employee
and Employee Personal Data tables in the Human
Resource database have been loaded. All is
well now!
Nancy Dickson
Monday
20, Nov. 2000 10.30 AM
The
Employee and Employee Personal Data tables
in the Human Resource database have zero records
in them due to a load problem last night.
Information Technology is investigating the
problem. We hope to find a fix and be able
to restore the missing data with tonight's
load. We will send you a note when all is
well again.
Sorry
for the inconvenience :(
Nancy Dickson
Thursday
09, Nov. 2000
"Hi
All! Forgive my red exclamation mark on this
note... but I know many of you focus specifically
on notes that you need to respond to. I wanted
to make sure we all are on the same page regarding
the DIRECTORY_SERVICES database on enterprise1.
DIRECTORY_SERVICES has both Student, Employee
and Other data in it. It is the data that
feeds the Outlook e-mail directory. Data Administration
is the Data Trustee for this database. We
recently gave everyone who had STUDENT database
access, access to the DIRECTORY_SERVICES database
also. We felt we could do that because the
folks who already have access to STUDENT know
the Warehouse Data Usage Policies and have
learned the FERPA regulations with respect
to the student data.
There
are still people however who do not want access
to STUDENT who do want access to DIRECTORY_SERVICES.
These folks MUST receive the Warehouse Policies
and FERPA guidelines BEFORE they get their
userid. This is especially important because
student data is involved! We also must have
a record (e-mail or written) of the person's
approval for access and training on the policies.
So....
here is what I propose for the process to
get access to Directory Services for someone
who does not have access to Student.
1. Person requesting access sends a note to
ware-q@asu.edu explaining why they need access.
2. Ware-q reader forwards note to Susan Moore
for approval. (in Susan's absence Nancy or
John may sub).
3. Susan sends note to DBA's to set up userid
and password (copy to user so they know we
are working on it).
4. DBA's send note to ONLY Susan Moore, me
(Nancy Dickson) and John Rome that it is set
up.
5. Data Admin staff (Susan, John or moi) contact
user to arrange policy training and give them
the userid.
6. Trainer records date and name of person
trained (you could just print out the e-mail
and sign and date) and then gives it to Tina
to file in Data Admin.
Again...
we need to insure especially for student data..
that our users know the policies BEFORE they
get their userids.
THANKS ALL... yell out if you have questions
or a problem with this! We probably won't
have many people doing this... but there are
some.
Nancy Dickson
Tuesday
03, Oct. 2000
As
Dawn shared with you in a previous note, the
tables in the Course database did not freeze
on the 21st day as they normally do. Tomorrow
morning we will update the tables with the
correct 21st day data and then the tables
will no longer update. The tables effected
are:
Class
Class Meeting Time and Place
Class Meeting Time Assignment
Class Running Totals
Class Enrollment by Hours
Class Foot Note
Note
this does not effect the Student Profile or
Warehouse Census database headcount views.
It only effects the Class tables. These changes
should be done by 8am after which
time queries on the Class tables will give
correct official enrollment. Thanks to Noreen
Murray in Extended Ed for bringing this to
our attention!!
Also
tomorrow, one change will be made to the Student
Profile table. One student's New Undergraduate
Status Code will be changed from New Transfer
to New Non Degree. This will not
effect the first time freshman numbers.
If
you need more info on this change please contact
ware- q@asu.edu.
Nancy Dickson
Monday
02, Oct. 2000 9.14 AM
The
21st Day Data Is Official! The Warehouse has
been corrected to show the official headcount
and SCH for Fall 2000. The Warehouse (Census)
database has been populated with Fall 2000
data.
There
are a couple of things you might like to know
about with respect to the Fall 2000 official
data...
1)
The Warehouse and the RG35 screen on the SIS
will not agree for the following categories:
Main
Campus (T) Headcount (RG35 is 3 too high)
Main OFF Campus (O) Headcount (RG35 is 30
too low)
Main Campus (T) SCH (RG35 is 120 too high)
Main OFF Campus (O) SCH (RG35 is 120 too low)
All
other categories match between the Warehouse
and RG35 including the overall Main numbers
which lump Main (T) and Main Off (O) together.
This
mismatch is due to a difference in Funding
Campus code which Institutional Analysis and
the Warehouse picked up, but RG 35 did not.
The change correctly
allocates additional heads and SCH to Extended
Education for REL 321, SLN 37740.
2)
Clep and Advanced Placement hours have been
removed from ASU cumulative earned hours totals.
This was a change that occurred on the SIS
last summer.
A
BIG THANKS goes to the Institutional Analysis
gang - Lap-pun Lam, Melinda Gebel, and Dawn
Parchert, who helped figure out why things
didn't jive this semester!
If
you have any questions, please contact ware-q@asu.edu.
THANKS!
Dawn Parchet
Monday
02, Oct 2000 8.08AM
"The
load for the weekly student tables was not
successful. I am going to have production
support rerun the job once they are here.
Also
the CLASS_MEETING_TIME_ASSIGNMENT table was
not successfully loaded and I will have production
support rerun this also.
" Jere Coon /O=ARIZONA STATE UNIVERSITY/OU=MAIN/CN=RECIPIENTS/CN=1000408472M
EX John Rome;Nancy Dickson;Dawn Parchert /O=ARIZONA
STATE UNIVERSITY/OU=MAIN/CN=RECIPIENTS/CN=1000421232M;/O=ARIZONA
STATE UNIVERSITY/OU=MAIN/CN=RECIPIENTS/CN=1000015188M;/O=ARIZONA
STATE UNIVERSITY/OU=MAIN/CN=RECIPIENTS/CN=1000373581M
EX;EX;EX Normal Normal
21st Day Data "I am sending this note to you
via the listserv because you have
access to the STUDENT and COURSE databases
on the ASU Data Warehouse.
The
Enrollment Count field in the Class table
does not reflect actual 21st
day enrollments. This problem is due to the
nightly loading of the tables
in the Course database. We are currently attempting
to solve this problem.
In the meantime, you can get official enrollments
in a particular course by
using the Student Class table and counting
individual student records. If
you have questions or need more information,
please send a note to Ware-Q.
Dawn Parchet
Monday
02, Oct 2000 7.43AM
The
weekly load on the Student Data Warehouse
did not run last night. In addition, the Class_Meeting_Time_Assignment
table in the Course database did not load
either. We are planning to run the load again
this morning. Please avoid running any queries
on the weekly loaded tables, as this will
cause the load to slow or stop. Tables you
CAN use are Student and any of the mailing
labels or e-mail tables.
We
will send out another note when the loads
are complete. Sorry for any
inconvenience this caused.
Jere Coon
Friday 29, Sept. 2000
The Student and Course databases have completed their weekly updates.
You
may now feel free to use any of the tables.
Happy Querying!
Dawn Parchert
Friday
28, Sept. 2000
Happy
21st Day!
To update you.... the Student 21st day official
data was frozen last night. It will be loaded
to the Student Data Warehouse tonight (Wednesday).
Census Flags for the 20007 semester will appear
in Student Profile and Student Class Thursday
morning.
Institutional Analysis (IA) will then proceed to check the Warehouse
census numbers against the SIS. This process
can take two weeks worst case. During this
time you may see Census Flags and data change
in the Warehouse, so consider this data preliminary.
When
IA completes their matching and cleaning process
on the data, I will send another note to let
you know the Census Flags for 20007 are OFFICIAL.
Nancy Dickson
Wednesday 13, Sept. 2000
After
the beginning of each fiscal year the previous
fiscal year's data is removed from TRANSACTION_DETAIL
and placed in its own table called TRANSACTION_DETAIL_FY
where FY equals the fiscal year of the data.
This
change will occur on Saturday, September 16th.
FY 2000 data will be moved into a table called
TRANSACTION_DETAIL_2000. Once the FY 2000
data has been moved, the TRANSACTION_DETAIL
table will contain data for FY 2001 only.
If
you have any questions or concerns about this
process, please contact Duane Cluff, duane.cluff@asu.edu.
Duane Cluff
Wednesday 30, Aug. 2000
Reminder...
WUG Meeting Thursday!
Thursday,
Aug., 31st
2:30pm to 4:00pm
Memorial Union Ventana B Room
Join
us and decide what you think about the new
version of BrioQuery. Your opinions are important
in shaping ASU's query future!
Nancy Dickson
Monday 21, Aug2000
The next Warehouse Users Group (WUG) meeting will feature BrioQuery 6.
Yes.. there is a new version. Come take a
look and decide for yourself if it has features
that you need.
ASU does not currently have a plan for mass
upgrades to 6. We would like to hear from
our Warehouse users as to how useful they
think Brio 6 is... so y'all come!
The
meeting is
Thursday, Aug., 31st
2:30pm to 4:00pm
Memorial Union Ventana B Room
(its a big one... so we hope everyone will
fit!)
First
we will hear from Mike Nash, Senior Systems
Consultant for Brio. He will describe the
new features of Brio 6.. like having two (or
more) queries with two (or more) sets of
results that you can combine in a Detail Report....
like what you can do with Java script in an
EIS page... and like how you can make labels
in Brio 6. Dawn Parchert and I will do a couple
queries in both Brio 5 and 6 to see the difference
and Chris Cejka, our local Brio Sales Rep,
will announce prices to upgrade individual
copies of Brio 5 to Brio 6.
Hope
you can join us... and be sure and send me
e-mail after to let me know what you think
of the new Brio 6! For those who can't attend,
I will try to accumulate the thoughts of those
that do and put them up on the web page. THANKS!
Nancy Dickson
Wednesday 16, Aug. 2000
Here
is a note from one of our Sybase dba's...
Please
let the Data Warehouse (Enterprise1) customers
know that we will be recycling (shutting down
and
starting up) the Sybase server between 7:00
PM and 7:15 PM every Sunday and Wednesday,
starting August 16. Processes running during
this time will be terminated.
This
is necessary to prevent the Sybase Server
from experiencing connection problems every
6 (six) to
seven (7) days. We are working with Sybase
to solve the problem and will let you know
when this is no
longer necessary. We are sorry for any inconvenience
that this may cause...... Walt Ellis
Many thanks to Walt and our other dba's -
Jack Davis, Kato Haws, and (recently joining
us!) Kevin Miller for staying on top of these
problems that appeared shortly after the Sybase
software on the server was upgraded!
Nancy Dickson
Friday
04, Aug. 2000 1.45 PM
The
Data Warehouse is back up again, and everything
appears to
have loaded properly. Again, we apologize
for any inconvenience.
If you have any questions or comments, please
send them to Ware-Q.
Dawn Parchert
Friday
04, Aug. 2000 12.24 PM
The
Warehouse is down. We are investigating the
reason. We will have it up as soon as we can.
I will send a note when it is up again. Sorry
for any inconvenience.
Dawn Parchert
Thursday
27, July 2000
There is a problem on the Data Warehouse server (enterprise1) which is
causing response time to be slow. The Database
Administrators (dbas) are planning to take
the Warehouse
down at noon to fix this problem. It is expected
to be back up by 12:30pm today (Thursday).
If
you have a query running when the Warehouse
goes down, it may still look like it is running,
but no results will come back. Please try
again after 12:30pm
THANKS
and sorry for the problem.
Nancy Dickson
Thursday
22, May 2000
Starting
on Thursday, June 1, 2000, the following tables
in the COURSE database will be updated nightly
instead of weekly.
Class
Class Footnote
Class Meeting Time Assignment
Class Meeting Time Place
Class Running Totals
Course
We
are trying this initially to facilitate the
early registration programs. This will enable
folks to see
open and closed classes on a nightly basis
in the Class table. If it works well and people
want to continue
on a nightly basis, we will consider continuing
it. AGAIN... please realize this is an experiment.
Beware,
that when joining these tables to the Student
Class table you may experience TIMING MISMATCHES...
For instance, the number of student's in a
particular
class in the Student Class table may not match
the enrollment in the Class table because
one number
is as of Sunday and the other is as of Wednesday
or Thursday. If you encounter timing mismatch
problems, please contact ware-q@asu.edu <mailto:ware-q@asu.edu>
for help.
Dawn Parchert
Tuesday
09, May 2000
There
was a problem loading data into the monthly
summarized tables on the FINANCIAL database
last
Wednesday. All the month end records were
not included in the April summaries.
This
affects only the summarized tables like Transaction
Summary and Expenditure. The Transaction Detail
table is correct and includes all records.
The
load will run again tonight to include the
missing data in the summary tables.
Sorry for any inconvenience this caused.
Nancy Dickson
Tuesday
02, May 2000
WUG
meeting tomorrow!..........
Wednesday,
May 3, 2000
Life Science E Wing
Room 106
2:00Pm - 3:30Pm
This
meeting will feature the STUDENT database.
Here is the line up....
1.
Manipulating Transcript Data with Excel and
Access by Sheldon Zola, Student Affairs Research
Did
you know that some students have 30 transcript
records in the Postsecondary Transcript table?
Sheldon is going to show us how to find the
most recent institution by finding the maximum
rows in
Excel. He will also analyze the data with
an Excel pivot table and then compare that
to an Access
pivot.
2.
Making Sense of the Immunization Table by
Nancy Dickson, Data Administration
Now
that this table has a Record On SIS Flag,
it can be useful to find students who are
prohibited
from registering because measles immunization
info is missing.
3.
Using MAC's Data with the Immunization Table
by Rosanna Sidhu, Public Programs
Rosanna
identifies her advisees using the Mandatory
Advising data in the Advisement table
and then joins it to the Immunization table
to make sure students can register.
4.
Calculating with Undergrad Exam Scores by
Dawn Parchert, Institutional Analysis
The
exam scores on the Warehouse are in text or
character format. That makes it impossible
to find average SAT scores. Dawn will show
us how to use Brioquery to change the scores
to
numbers and then average them.
This
is in a big room... so yall come!
Nancy Dickson
Tuesday
25, Apr. 2000
Yes,
its almost the end of another semester. That
means we can take over LSE106 for a Warehouse
Users Group (WUG) meeting again! This meeting
will feature the STUDENT database.
We
have two topics on the agenda so far:
Making
Sense of the Immunization Table Now that this
table has a Record On SIS Flag, it
can be useful to find students who are prohibited
from registering because measles immunization
info
is missing.
Manipulating Your Data With Excel Excel can be used to evaluate data
after you
bring it back from the Warehouse. Questions
like, what was the most recent or earliest
semester a
student was here, or if you want to find students
that took this class or that class, but not
this
other class... Excel can help.
WHAT
ELSE ARE YOU INTERESTED IN?????? REPLY TO
THIS NOTE
WITH YOUR IDEAS!!
And
meanwhile mark your calendar....
Wednesday,
May 3, 2000
Life Science E Wing
Room 106
2:00Pm - 3:30Pm
Happy
querying!
Nancy Dickson
Monday
17, Apr. 2000 5.31 PM
There
has been a change to the Purchase Orders Open
Line ASU table in the FINANCIAL Data Warehouse.
This change is causing a number of folks to
get an error message on queries that ran
just fine in the past. The error reads...
Server
Name: ENTERPR1_SS.Message:Invalid column name
'PO_Line_Oustanding_Amount_$' Message Number
(207), severity (16)
The
name of the field PO_Line_Oustanding_Amount_$
has been changed to PO_Line_Outstanding_Amount_$.
(Difference is the spelling of outstanding
has been corrected.) This was
necessary for some new development we are
doing to provide web Advantage reports (watch
for news soon!)
To
fix a query showing this message you must
replace the Purchase Orders Open Line ASU
table with a fresh copy from your Table List.
Be sure to jot down all the limits you
have in that table before you remove it and
also note the data elements you have from
it up on the Request Line. After you bring
out the table fresh, you will have to
relimit things and readd the data to the Request
Line.
If
you have trouble with this, please attach
your query to an e-mail and send it to ware-q@asu.edu.
We will help you.
THANKS
and sorry for the problem.
Nancy Dickson
Monday
17, Apr. 2000 12.58 PM
The
Immunization table on the STUDENT database
will have a new field added this Wednesday.
A Record On SIS Flag will be added just before
the Extract Datetime field. We believe the
addition of this flag will help us more accurately
identify students who are prohibited from
registering due to
immunization requirements.
If
you have queries built that use this table,
they will still run on Thursday, however if
you want to limit the new Record On SIS Flag,
you will have to remove the Immunization table
from your current query and then replace it
in order for the field to show up. If you
need help with this, send your query
to ware-q@asu.edu.
Happy
querying!
Nancy Dickson
Wednesday 22, Mar2000
I
am relaying a note from one of our Database
Administrators(Jack Davis).
The
Enterprise1 server will be having an emergency
outage at Noon today, Wednesday, March 22,
2000. This outage will be for about 15 minutes.
This affects the Student, Human Resource and
Financial Data warehouse users. Sorry for
the inconvenience...
Nancy Dickson
Upon
further inquiry, I understand there are some
auxiliary tables that are locking up. They will
take the server down and adjust the database
space so this doesn't happen.
If
you have a query running at noon when we bring
the server down, it may give you an error
message... but if it doesn't, just try back
in the afternoon. THANKS!
Nancy Dickson
Tis
the season of the quick and dirty survey!
Please answer the three questions below to
help Data Administration decide what query
tools we need to support for the Data Warehouse.
1.
What query tool(s) do you use to access the
Data Warehouse?
Indicate all you use or expect to use!
BrioQuery
MS Access 97
MS Access 2000
Excel 97
Excel 2000
Others....???? Please specify.
None..
you don't use a query tool - How do you access
then?
2.
Rate the databases as to which you query most.
(which is #1?)
Student?
Financial?
Human Resource?
Other - specify please.
3.
Do you have a copy of BrioQuery that you don't
use and
would be interested in selling to another
user?
PLEASE
RESPOND...Your answers matter!
OK.. its not quite as important as the 2K
CENSUS... but its
always nice to influence decisions that affect
you!
If
its easier.. you can fax answers to 5-1548.
THANKS
ALL!
Nancy Dickson
Monday
03, Mar2000
Did
anyone notice? East Campus has College Codes
now! As of last Monday, 3/6/2000, College
Codes for the East Campus have been loading
to the Warehouse from the Student Information
System.
The
codes are as follows:
EC---East
College
AG--Morrison School of Agribusiness
TS--College of Technology & Applied Science
Students
with East Campus majors had their College
Codes changed in the SIS. When the Warehouse
loaded after that, new records went out with
the new colleges and the old
records had their Record On SIS Flags changed
to N. (More reason to limit that puppy!)
We
can now identify Main and East Campus students
by using the College Code instead of having
to look at the major code! Three cheers to
the IT staff that added this to
the SIS!
I
have a note from the Registrar that outlines
all the Degree/ Major codes that fit under
each East college. If you would like a copy,
please let me know.
Happy
Querying!
Nancy Dickson
Friday
25, Feb 2000
he
Data Warehouse server (enterprise1) will not
be available Friday, Feb. 25, 2000 from approximately
9pm to midnight. Information Technology will
be installing an additional
1GB of memory on enterprise1. This memory
should help queries run faster and will help
us later, when we upgrade the Sybase operating
system to take advantage of parallel processing.
Nancy Dickson
Thursday
17, Feb. 2000
It's
OFFICIAL! The 21st Day numbers have been checked
and adjusted to match Institutional Analysis's
numbers (thanks Lap and Jere!).
The
Student Profile table in the STUDENT database
as well as the census tables in the WAREHOUSE
database have the correct data for Spring
2000 (20001).
For
those of you who are new to the Warehouse...
the census data is used to compare data from
Semester to Semester. Use it to answer questions
like ""How many chemistry majors did we
have over the last five years."" By using
the census data, you are always comparing
the numbers at the same point in the semester
- the 21st day.
Happy
Querying!
Nancy Dickson
Wednesday 09, Feb. 2000
Just
reminding you that the next meeting of the
Warehouse Users Group will feature STUDENT
data and will be oriented for BEGINNER WAREHOUSE
USERS.
Thursday,
February 10, 2000
2:30pm to 4:00pm
in the Computing Commons Auditorium
(CC 120)
Dawn
Parchert and Nancy Dickson will present.....
Student Data Concepts - WHERE is it???....
and WHEN is it???? and by popular request
we have added... how do you JOIN to it????
One
of the hardest things to determine as a beginning
Warehouse user is WHERE data is among all
the Warehouse tables. The other difficult
concept involves TIME.... why is a student's
major listed as Biology in one table... but
English in another? Until you understand the
TIMING of when the tables
are loaded, you won't know which table to
believe.
Tables
that we will explore include:
Student
Student Profile
Student Degree
Student Mailing Labels Local and Permanent
Student E-mail
Student Class
We
will use BrioQuery to present, however we
will be focusing on data
concepts, tables, and timing...not how to
use Brio.
If
you are one of those folks who came to the
intro class recently or even a
long long time ago.... and you just didn't
feel you could take off on your
own
from there.... THIS SESSION IS FOR YOU!
Nancy Dickson
The STUDENT Warehouse load has completed successfully. The Warehouse
contains data from the SIS as of Monday night
when processing on the SIS completed. Thanks
to all those who stayed
off when they were dying to be here at 6 am
doing reports!
Nancy Dickson
WAREHOUSE
STUDENT DATA NOT AVAILABLE TUESDAY 1/18 UNTIL
8 or 9AM!!!!
The
Warehouse STUDENT data will be updated MONDAY
NIGHT rather than the usual Sunday night this
weekend. This will provide the most up-to-date
data for the first week of classes. The data
will not
be extracted until after processing on the
SIS completes Monday night. We anticipate
that the loading process may run later than
usual due to late SIS processing and lots
of changes that take place
the weekend before school starts. I will send
a note to all users Tuesday morning as soon
as Production Control verifies that the loads
are completed. We are hoping they will be
complete by 8 or 9am.
Please
do not query the Warehouse early Tuesday morning.
If you do, you will get old data and it will
slow the loads. The dba's will be watching
for users and may disconnect queries that
get
in the way of the loads.
We
appreciate your cooperation! I will be in
touch Tuesday
morning.
Nancy Dickson
This
weekend, a new field will be added to the
Undergraduate Application table in the Warehouse
STUDENT database. The new field is called
Admission_Process_Code and it will be added
at the
bottom of the table just above the Record_On_SIS_Flag.
This
change will NOT affect saved queries. (That
only happens if a field goes away or changes
names.) If you have queries saved that use
the Undergraduate Application table tho...
in order to see the
new field you would have to readd the table
to your query and re-set any limits you had
on it.
MORE
INFO ON THIS CODE....
This code was requested by the Undergraduate
Admissions Office.
The valid values for this code are:
These
codes indicate to Admissions staff what to
do with certain
applications.
Nancy Dickson
There
is a problem on the enterprise1 server which
is the computer that holds all the Warehouse
databases. To fix the problem, our dba's will
bring the Warehouse down and
then up again at Noon today (Tuesday). If
you are running a query when it goes down,
it will be disconnected and won't return with
results. Please try it again around 12:30pm.
Thanks and sorry for any inconvenience.
Nancy Dickson
Friday
12, Dec 1999
For
those of you who are interested in querying
the Warehouse with Excel.... Hans DeBano from
Liberal Arts and Sciences reports that Excel
97 has the Get External Data option under
the Data menu. I
just tried it and it worked! So... you may
not need to upgrade your Excel to do queries
like I showed at WUG today! THANKS Hans!
And
if you do upgrade... be cautious you don't
clobber old Office parts that you need like...
OUTLOOK! You could easily wipe out your e-mail
if you are not careful.
PS.
Let me know any other impressions of Excel
you have if you try it.
Nancy Dickson
Wednesday 08, Dec1999
Just a reminder that ""Alternate Query Tools Day"" is happening Thursday
(tomorrow) at the Warehouse User Group meeting!
The agenda is below with a few things added
and the order of presentations is
changed just a bit. Hope to see you!
Thursday,
Dec. 9th, 1999
at 2:00 to 3:30pm
in the Life Science E 104
(This is a BIG ROOM - room for everyone!)
Agenda
I.
Basic SQL - by Jack Davis, Database Administrator,
IT
Jack
will explain how SQL (Structured Query Language)
works.
SQL is the language the query tools use to
get data from the
Warehouse. It is helpful to understand the
SELECT, FROM
and WHERE statements of SQL when looking at
query
tools.
II.
Excel 2000 As A Query Tool - Nancy Dickson,
Data Admin
Nancy
will explore the capabilities of Excel 2000
to query the
Data Warehouse. You can use either the Excel
query wizard
or MS Query. I will demo a query on the HR
database that
lists terminated employees and their department.
III. Using Crystal Reports to Query the Warehouse
- by Cheryl
Conover, Sponsored Projects
Cheryl
uses Crystal (made by Seagate) in her office.
She will
show us how to build a query and get data
using this tool and
will explain the strenghts of Crystal in comparison
to Brio (Crystal
list price $350 vs Brio $1000.. for instance!).
The query she will
build creates a report of monthly expenditure
and encumbrance
activity from the FINANCIAL database.
IV. MS Access 97 Query Primer - by Dawn Parchert,
Institutional
Analysis
Dawn
will explain briefly how to connect to the
Data Warehouse
with ODBC and show us step by step how to
build queries
in Access. Her example will provide a list
of students who
meet certain criteria for a scholarship.
Nancy Dickson
Monday 06, Dec1999
The daily loads for the STUDENT, HUMAN_RESOURCE and FINANCIAL databases
are in the process of loading now. The weekly
loads will not occur until tonight. The Warehouse
should
be restored to its usual wonderful self by
tomorrow morning.Sorry for the inconvenience...
it is still a puzzle why this happened.
Nancy Dickson
Monday
06, Dec1999
We
received this note from IT this morning....It
appears like none of the loads for the warehouse
ran
this weekend (Saturday forward). Production
Support is looking into what caused this to
happen.
I
will update you when we get information regarding
this problem.
Nancy Dickson
Friday
03, Dec1999
"Next
WUG Meeting Is Thursday, DEC 9, 2:00PM" "Many
apologies for the two notes just to get the
date for the WUG meeting correct.... it is
really DECEMBER 9th, 2PM, LSE104.
Nancy Dickson
Sunday
07, Nov. 1999
The
next meeting of the Warehouse Users Group
will feature ""Alternate"" Query Tools! You
have probably heard that you don't HAVE to
use Brio to query the Warehouse. At this meeting
we will look at software OTHER than BrioQuery
that can be used to query the Data Warehouse.
The meeting is:
Thursday,
Dec. 9th, 1999
at 2:00 to 3:30pm
in the Life Science E 104
(This is a BIG ROOM - room for everyone!)
The
agenda is as follows:
I.
Basic SQL - by Jack Davis, Database Administrator,
IT
Jack
will explain how SQL (Structured Query Language)
works. SQL is the language the query tools
use to get data from the Warehouse. It is
helpful to understand the SELECT, FROM
and WHERE statements of SQL when looking at
query tools.
II.
Using Crystal Reports to Query the Warehouse
- by Cheryl Conover, Sponsored Projects
Cheryl
uses Crystal (made by Seagate) in her office.
She will show us how to build a query and
get data using this tool and will explain
the strenghts of Crystal in comparison to
Brio.
III. MS Access 97 Query Primer - by Dawn Parchert,
Institutional Analysis
Dawn
will explain briefly how to connect to the
Data Warehouse with ODBC and show us step
by step how to build queries in Access.
IV.
Excel 2000 As A Query Tool - Nancy Dickson,
Data Admin
Nancy
will explore the capabilities of Excel 2000
to query the Data Warehouse. You can query
the Warehouse right from your Excel spreadsheet.
Hope
you can join us! This meeting may be of interest
to any Warehouse user. We will not be focusing
on any particular database but rather we will
focus on the functionality of the
query tools.
Nancy Dickson
Wednesday 27, Oct. 1999
This
is the agenda for the next meeting of the
Warehouse Users Group. We will feature the
FINANCIAL database and we will focus on reports
that can be generated on the Data Warehouse
that are not currently available with Advantage.
The meeting is:
Tuesday,
Nov. 9th, 1999
at 10:30am to Noon
in the Memorial Union Ventana B Room
(This is a BIG ROOM - room for everyone!)
I.
Demo of Access Canned Queries that Provide
USR6 Type Information - by Nancy Dickson
Nancy
will demo a MS Access application that she
and the Access Users group are building which
allows users to input their Account, Account
Type Code and Fiscal Month. Then with the
click of a button... records are brought back
from the Warehouse and dumped into Excel.
We are looking for more
queries that could be incorporated into this
Access database and then we will make it available
to anyone who has Access. It is a SIMPLER,
CHEAPER way to get Warehouse data than
to use Brio.
II.
New Tables on the FINANCIAL Warehouse - John
Rome.
John
will talk about the new SubOrg Code table
which contains descriptions of suborgs. He
will also talk about the new Open Purchase
Order tables and about the coming ASU Vendor
table!... the one with the English words like..COMPUTING,
BOOKSTORE... remember those???
III.
Open Purchase Order Queries Using Brio - Nancy
Dickson and Duane Cluff
Nancy
and Duane will show reports showing open purchase
orders from the point of view of both the
servicing department and the purchasing department.
IV.
USR4 Reporting Off the Warehouse Using Access
- Dennis Ederer
Dennis
has two queries that he uses to provide the
data in a USR4 report. He will explain how
to build the two queries at this meeting.
For those who are more interested in learning
Access, he will continue development of this
application at the next Access Users Group
Meeting.. hooking it to a macro
and creating a beautiful printed report.
Nancy Dickson
Wednesday 06, Oct. 1999
The
next meeting of the Warehouse Users Group
will feature the FINANCIAL database. It will
be:
Tuesday,
Nov. 9th, 1999
at 10:30am to Noon
in the Memorial Union Ventana B Room
(This is a BIG ROOM - room for everyone!)
The
agenda is still forming, but so far we know
we want John Rome to show the new purchase
order tables. Dennis Ederer has volunteered
to do something and I have an Access database
that performs some financial queries using
a form. It makes it really easy to get financial
data from the Warehouse into your Excel spreadsheet.
Since
the agenda is still up in the air, please
reply to this note with any thoughts you have
about what you would like to see.
Meanwhile... make sure to mark your calendar!
Nancy Dickson
Five
new tables with purchase order information
for PD, PC, SC, and PO documents have been
added to the Financial Data Warehouse. The
tables are scheduled to be updated daily based
upon the previous day's Advantage transactions.
(Updating of the Financial Data Warehouse
is usually completed by 8 a.m., although this
time schedule may be delayed on the day following
Advantage monthly close processing.)
The titles of the new Financial Data Warehouse
tables and the corresponding Advantage tables
are listed below.
PURCHASE
ORDERS OPEN (OPPH) - contains PD, PC, and
SC header information
PURCHASE
ORDERS OPEN LINE (OPPL) - contains PD, PC,
and SC accounting line information
PURCHASE
ORDERS OPEN COMM (OPPC) - contains PD, PC
and SC commodity line information
PURCHASE
ORDERS OPEN ASU (OPOH) - contains internal
purchase order (PO) header information
PURCHASE
ORDERS OPEN LINE ASU (OPOL) - contains internal
PO accounting line information
The information contained in these tables
has numerous uses including: to help departments
track a specific purchase order, to allow
departments to obtain a daily listing of outstanding
purchase order encumbrances, to assist departments
in making sure purchase orders are closed
after final payment has been made, and to
give internal servicing departments current
information regarding purchase orders issued
to them by other departments.
If
you have any questions regarding these new
tables, or any other information in the Financial
Data Warehouse, please contact Duane Cluff
in the Comptroller's Office at duane.cluff@asu.edu
<mailto:duane.cluff@asu.edu>
Reminding
you the next meeting of the Warehouse Users
Group is TODAY! It will feature the Student
database. It will be:
Thursday,
Oct. 7th, 1999
at 2:00pm to 3:30pm
in the MU Pima Room (BIG ROOM in the SW corner
of MU)
The
agenda includes:
I.
Using the Brio Detail Tab to Create A Report
Showing Total Upper Division Hours Completed
by Students. by Nancy Dickson, Data Admin
and Crissy Dye, Economics
Crissy
was looking for students to award scholarships
to. She found it useful to look at the total
number of ECN upper division courses a student
had taken to make sure they were in the last
year of their program. This same technique
will work to alert advisors of students who
do not have enough upper division hours to
graduate.
II.
Creating Freshman Profiles - Comparing Brio
and Excel's Pivot Functionality.
By Sheldon Zola, Student Affairs and Nancy
Dickson, DA
Sheldon
will show a BrioQuery that shows Freshman
ethnicity, enrolled hours, if they live in
a dorm etc. He and Nancy will then create
Pivot reports using Brio and Excel so we can
see the similarities and differences of each.
III.
Little Known Student Data Secrets like....Will
the REAL registered students stand up? or
what's the
difference between registered students in
the Student, Registered Student, Student Profile
and Student Currently Enrolled. Also... why
do all my students go away for old Semesters
on the Undergrad Application Table?
Happy
Querying!
Nancy Dickson
The
previously reported problem in the WAREHOUSE
(Census) database in the West Enrollment Census
table whereby the student's West Campus major
was being changed to 04/0000 has been corrected.
The West Enrollment Census table as of last
Friday night (9/24/99) was corrected to match
the official numbers reported to the Arizona
Board of Regents.
In
checking over the data this week before pronouncing
it sound... a couple of little known facts
came to light about Census data that might
be worth mentioning...
1)
When we count students for the Census by campus
(East, Main or West), we only count a student
if they are enrolled in a class that is funded
by that campus. If a West campus major is
only enrolled in
Main classes this semester, West would not
count them, only Main would. Even though their
major code is 7002, West does not count them.
Counts are based on campus enrollment not
major codes.
2)
In the Main and West tables in the WAREHOUSE
database, we CHANGE DEGREES AND MAJORS! The
above mentioned West 7002 major in the Main
Enrollment Census table will have their
degree/major changed to 02/0000 (for an undergraduate)
or 04/0000 (if a graduate student.) The concept
is ... this student is Non Degree Seeking
at the campus they are visiting so we change
the degree/major to the non degree seeking
designation. The same would be true of a Main
Campus major who is taking classes at West
Campus.... in the West Enrollment Census table
his degree/ major would be changed to 02/0000.
Keep
in mind, these little known facts are only
important if you need to match to what is
reported to ABOR by campus.
Happy
Querying!
Nancy Dickson
The
next meeting of the Warehouse Users Group
will feature the Student database. It will
be:
Thursday,
Oct. 7th, 1999
at 2:00pm to 3:30pm
in the MU Pima Room (BIG ROOM in the SW corner
of MU)
So
far, the agenda includes:
I.
Using the Brio Detail Tab to Create A Report
Showing Total Upper Division Hours Completed
by Students. By Nancy Dickson, Data Admin
and Crissy Dye, Economics
Crissy
was looking for students to award scholarships
to. She found it useful to look at the total
number of ECN upper division courses a student
had taken to make sure they were in the last
year of their program. This same technique
will work to alert advisors of students who
do not have enough upper division
hours to graduate.
II.
Creating Freshman Profiles - Comparing Brio
and Excel's Pivot Functionality.
By Sheldon Zola, Student Affairs and Nancy
Dickson, DA
Sheldon
will show a BrioQuery that shows Freshman
ethnicity, enrolled hours, if they live in
a dorm etc. He and Nancy will then create
Pivot reports using Brio and Excel so we can
see the
similarities and differences of each. III.
?????? Send me a note back... what would you
like to see?
Happy
Querying!
Nancy Dickson
Here
is an update regarding the 21st day data in
the Student Profile table and in the WAREHOUSE
census database.
The
Student Profile table can now be used to match
the official numbers that have been reported
to the Arizona Board of Regents. Institutional
Analysis has verified that the records in
Student Profile that carry a
Census Enrollment Flag of Y are correct.
The
Fall 97 (19997) semester is now available
in the WAREHOUSE census database. Keep in
mind that the College or Divison Codes for
East Campus have not yet been implemented.
(IT is working on them currently.) Most East
Campus majors have ES (engineering) for a
College. For the first time this semester,
there are also majors from the Education college
who belong to East Campus. A breakdown by
College does not give correct head counts
for Main and East Colleges. If you need to
match official counts of students by COLLEGE
as reported to ABOR, send a note to ware-q@asu.edu
for help with the logic to do that.
Also!!!!
There still appears to be a problem with some
records in the WAREHOUSE database, the West
Enrollment Census table whereby the student's
West Campus major is being changed to 04/0000.
We will
work to correct that next week and send out
a note when its fixed.
Happy
Querying!
Nancy Dickson
Monday
20, Sept. 1999
The
weekly STUDENT load did not complete today
(Monday, 9/20/99). The update is scheduled
to run tonight and will be available tomorrow.
FYI....
Production Support in Information Technology
is working to make the load process run more
reliably. In the past, the entire extract
and load procedure was one BIG job and it
often ran late because we had to wait for
the SIS extracting to be done before the Warehouse
load started. IT is trying to break the process
into pieces so the load can happen in a shorter
amount of time. This week, there was a problem
with one of the extract modules, but we are
hopeful that after we get the bugs worked
out
the extract and load will be more reliable.
Thanks for your patience!
Also....
although the Census Flags were loaded into
Student Profile last week, the data will not
be official until Institutional Analysis completes
their check and matches it to official numbers
reported to the Board of
Regents. When the match is completed, I will
send out a note to let everyone know its official.
Nancy Dickson
I
am forwarding a note from Walt Ellis who is
one of our Sybase Database Administrators
regarding the Warehouse being unavailable
this weekend.
Information
Technology will be working on the Data Warehouse
machine, Enterprise1, from 9:00 PM Friday
evening, September 17, to approximately 2:00
AM Saturday morning, September 18. They will
upgrade the RAID chassis. The Data Warehouse
will be unavailable during this time.
Additionally, we will be making some adjustments to the Sybase SQL Server
from noon Saturday, September 18, to approximately
4:00 PM Saturday afternoon. We will increase
the temporary database space by a factor of
two (2) and make a few adjustments to our
Sybase hard disk device allocation. The Data
Warehouse will be unavailable during this
time as well.
Walter F. Ellis
**PLEASE
NOTE**
The
Transaction Detail Table is the only table
in the Financial Data Warehouse that is currently
being updated daily. Daily updates to tables
other than the Transaction Detail Table have
been temporarily suspended since August 22.
Programming
changes are being made in Advantage to correct
a posting problem. Once those changes have
been finalized in Advantage the Financial
Data Warehouse will be reloaded with the correct
information. Because of the magnitude of this
reload it will need to be completed over a
weekend.
Once
the reload is completed, Financial Data Warehouse
tables will be current and daily updates will
resume. All inception-to-date revenue and
expenditure activity will be in period 0 (zero)
in the 'reloaded' Financial Data Warehouse.
Optimistically, the Financial Data Warehouse may be reloaded and available
for regular use on Monday, September 13. Realistically
this may not occur until Monday, September
20. A notice will be sent out once the reload
is completed.
Please
note that these changes do not impact tables
that are pending redesign due to changes in
Advantage. Changes to the account roll-up
should be completed by the end of September.
Changes to the purchase order tables should
be completed no later than the end of October.
After
the Financial Data Warehouse is reloaded,
you may wish to re-run any queries you have
using fiscal 2000 information in order to
confirm your original results.
If
you have any questions please contact Duane
Cluff in the Comptroller's Office at duane.cluff@asu.edu.
" John Rome John.Rome@asu.edu SMTP nancy.dickson@asu.edu
nancy.dickson@asu.edu SMTP Normal Normal First
Meeting of the Access Users Group "I am sending
this note to you via the listserv because
you have access to the ASU Data Warehouse.
I
am forwarding a note from Dawn Parchert (below)
regarding the first meeting of the Access
Users Group (Aug.!). Since a significant number
of Warehouse Users have dabbled with Access..
I thought it might be of interest.
Attention
Access Users! Last year, some groups expressed
interest in having an Access Users Group.
Based on that discussion, I have scheduled
the first ever Access Users Group (AUG.) meeting
for
September 23 from 2:00 - 3:30 in the Computing
Commons Auditorium. If you use Access and
want to find out more about it or learn how
others on campus use it, please join us.
The
agenda is as follows:
Justin
Preston from Microsoft will talk about Access
2000--new features, Web functionality, and
performance enhancements.
Nancy
Dickson will present ""Let the Warehouse Automatically
Enter Data For You.""
She
will demo a piece of her Software Sales database
that allows you to type in a person's ID,
click a button, and the person's data is automatically
entered into the database with no typing!
This can be done for either staff or students
or you can pull data from your own local database
and fill forms automatically.
Dawn
Parchert will present ""Combo Boxes: What
Are They and Why Should I Use Them.""
She
will demonstrate how to create combo boxes
using lookups, wizards, and manual adjustments
in the Properties window. If time allows,
she will show how combo boxes can be used
to filter and display data in forms.
Since
this is the first ever AUG. meeting, I'd like
to get a feel for what topics you might want
to see at future meetings. Please come prepared
with ideas, suggestions, and/or questions
about problems you may have had in the past.
We
can also discuss the possibility of setting
up a list serve.
I
hope to see you there!
Dawn Parchert
Thursday
02, Sept. 1999
Reminder
- Warehouse User's Group will be:
TODAY....Thursday, Sept. 2, 1999
2:00 to 3:30 PM
Memorial Union, Ventana B (MU 226B)
If
you can't make the meeting, the handouts will
be available on the web at http://www.asu.edu/Data_Admin/
Look under the WUG button! Handouts are in
Acrobat
format (.pdf).
Here
is the agenda for today!
1.
NEW HR Tables and Fields Available!
2.
New Hires - query to see if employees are
on HRMS yet.
3.
Find People With No Open Accounts - should
they be terminated.... or are they coming
back?
4.
Vacation/Sick/Comp time balances - leave with
or without pay.
All
queries will be done with BrioQuery 5 at this
meeting. Hope to see you there!
PS.
Next meeting is Oct. 7, 1999, 2:00pm in the
MU Pima Room. It will feature the STUDENT
database!
Nancy Dickson
Monday
08, Sept. 1999
The
Student reload of the Data Warehouse has completed,
however, the Student Class table update WAS
NOT SUCCESSFUL. All the other tables loaded
successfully.
The
Student Class table will be reloaded tonight
and the data should be available tomorrow
morning.
Again...
sorry for the inconvenience.
Nancy Dickson
The
Student Data Warehouse load did not run last
night. Because there were such a large number
of people who noticed it and are asking about
it, I have asked the IT folks to rerun the
load now... during the day.
If
you are using the Student database on the
Warehouse, please log off so the load can
progress quickly. I will send a note when
it is finished. If you are using HR and/or
Financial database, if you could wait until
later, we would appreciate it altho your queries
should be able to run while the load runs.
Sorry
for the inconvenience... especially when SO
many people are wanting to look at second
week numbers :(
Nancy Dickson
Monday
23 Aug. 1999
The
Student data on the Data Warehouse was not
updated completely last night. We are not
sure which tables got updated and which didn't.
The
load is rescheduled for tonight (Monday, 8/23/99)
and the data should be available tomorrow
morning.
IMPORTANT.......
To
make sure the load works tonight, the Warehouse
server will be recycled at 6pm tonight. This
means it
will be turned off and then back on. Please
don't plan to have queries running at this
time as they will be
interrupted. After it comes back up, the Student
loads will be run.
Sorry
for the inconvenience!
Nancy Dickson
Wednesday 18, Aug 1999
We
have nearly completed our changes to the FINANCIAL
warehouse to support our conversion from CUFS
to Advantage.
Summary
of changes (implementing tomorrow, Thursday,
August 19th!):
TRANSACTION_DETAIL table will be renamed to
TRANSACTION_DETAIL_1999.
TRANSACTION_DETAIL_2000 table will be renamed
TRANSACTION_DETAIL.
(please change your queries accordingly)
ACCOUNT_ROLLUP is now ready for use
(will be updated daily by the end of the month)
The
following tables will be removed:
ACCOUNT_OLD
BEGINNING_BALANCE_BY_MONTH
BEGINNING_BALANCE_OLD
BEGINNING_DEFICIT_BY_MONTH
BEGINNING_DEFICIT_OLD
EXPENDITURE_BY_MONTH
EXPENDITURE_OLD
GRANT_DESCRIPTOR
REVENUE_BY_MONTH
REVENUE_OLD
TRANSFERS_IN_BY_MONTH
TRANSFERS_IN_OLD
TRIAL_BALANCE_BY_MONTH (available for a few
more weeks)
Outstanding
Items:
We are now working on replacing the CUFS Purchase
orders tables. We're hoping to make those
available in September.
The
""new design"" of the financial warehouse
database is now available in Adobe acrobat
format (pdf) on the Data Administration web
site.
The
Entity/Relationship (E/R) Diagram you will
want to select is ""Financial"" .
The
web page is: http://www.asu.edu/Data_Admin/WH-1.html
If you have any questions, problems or concerns
during this transition,
please send a note to ware-q@asu.edu. Thanks.
John Rome
Monday
16, Aug 1999
WELCOME
BACK ALL!
The
first meeting of the Warehouse User's Group
will be:
Thursday, Sept 2, 1999
2:00 to 3:30 PM
Memorial Union, Ventana B (MU 226B)
This
meeting will focus on topics of interest to
HUMAN_RESOURCE database users. Topics will
include:
1.
NEW Tables and Fields Available!
2.
New Hires - query to see if employees are
on HRMS yet.
3.
Find People With No Open Accounts - should
they be terminated.... or are they coming
back?
4.
Vacation/Sick/Comp time balances - leave with
or without pay.
All
queries will be done with BrioQuery 5 at this
meeting. Hope to see you there!
PS.
Next meeting is Oct 7, 1999, 2:00pm in the
MU Pima Room. It will feature the STUDENT
database!
Nancy Dickson
Thursday
05, Aug 1999
Greetings
all!
The
Postsecondary Transcript table in the STUDENT
database will get two new fields this weekend....
Degree Code and Degree Year. These fields
will show degrees obtained by transfer students.
The Degree Code field is also being used by
Admissions to show students who participate
in special community college transfer programs.
All the codes are defined in the Code Degree
table.
This
change will not require that you rebuild your
queries. All the fields that were there previously
will remain. If you want to use the two new
fields however... you would have to drag the
table
out after the change has occured.
Nancy Dickson
Tuesday
03, Aug 1999
We
have almost completed our changes to the FINANCIAL
warehouse to support our conversion from CUFS
to Advantage. The following changes were made
last night.
Summary
of changes:
ACCOUNT
(some missing columns were added back, ACTIVITY
(formerly FUNCTION) info is still not available
in this table)
CODE_*
tables are now being updated on a daily basis
BEGINNING_BALANCE (table modified due to Advantage, now updated daily,
data back to 1993)
BEGINNING_DEFICIT (table modified due to Advantage, now updated daily,
data back to 1993)
EXPENDITURE
(table modified due to Advantage, now updated
daily, data back to 1993)
REVENUE
(table modified due to Advantage, now updated
daily, data back to 1993)
TRANSFERS_IN (table modified due to Advantage, now updated daily, data
back to 1993)
TRANSFERS_OUT (table modified due to Advantage, now updated daily, data
back to 1993)
FUNCTION_REPORTING_CATEGORY (new table, you can now get a description
for FUNCTION_REPORTING_CATEGORY)
SUB_ORGANIZATION (new table, you can now get description of your SUB_ORGANIZATION)
You
will want to start using the corresponding
tables above instead of the *_BY_MONTH tables
(e.g. EXPENDITURE_BY_MONTH) You can now get
both yearly and monthly data in these tables.
We'll
make the *_BY_MONTH tables available for the
next few weeks and then remove. The *_OLD
table will also be available for a few weeks
as well. If you use these tables in Microsoft
Access,
you may need to re-attach these tables. If
you're using Brio, you may need to rebuild
your query.
Outstanding
Items:
We are still working on converting the ACCOUT_ROLLUP
table and the ACTIVITY data is still not yet
available in the ACCOUNT table and the lookup
tables. We hope to have that data available
in
the next day or two.
Please
note the following changes:
Old table ---> New table
CODE_FUNCTION ---> CODE_ACTIVITY
CODE_FUNCTION_NACUBO ---> CODE_ACTIVITY_NACUBO
CODE_FUNCTION_GROUP ---> CODE_ACTIVITY_GROUP
CODE_STATE_OF_AZ_PROGRAM ---> CODE_STATE_OF_AZ_PROGRAM
(stays the
same, ACTIVITY_CLASS)
CODE_AREA ---> CODE_AGENCY
CODE_AREA_FUNDING_SOURCE ---> CODE_AGENCY_FUNDING_SOURCE
REPORTING_CATEGORY --->FUNCTION_REPORTING_CATEGORY
GRANT_DESCRIPTOR ---> ACCOUNT_SPONSORED
(GDES +)
TRIAL_BALANCE_BY_MONTH ---> TRANSACTION_SUMMARY
EXPENDITURE_BY_MONTH and EXPENDITURE --->
EXPENDITURE
REVENUE_BY_MONTH and REVENUE ---> REVENUE
BEGINING_BALANCE_BY_MONTH and BEGINNING_BALANCE
--->
BEGINNING_BALANCE
BEGINING_DEFICIT_BY_MONTH and BEGINNING_DEFICIT
--->
BEGINNING_DEFICIT
TRANSFERS_IN_BY_MONTH and TRANSFERS_IN --->
TRANSFERS_IN
TRANSFERS_OUT_BY_MONTH and TRANSFERS_OUT --->
TRANSFERS_OUT
Tip
of the month:
If the TRANSACTION_DETAIL is too slow, look
and see if TRANSACTION_SUMMARY will fit your
needs.
The
""new design"" of the financial warehouse
database is now available in Adobe acrobat
format (pdf) on the Data Administration web
site.
The
Entity/Relationship (E/R) Diagram you will
want to select is ""Financial"" .
The
web page is:
http://www.asu.edu/Data_Admin/WH-1.html
If
you have any questions, problems or concerns
during this transition,
please send a note to ware-q@asu.edu. Hope
to see you at
Kevin Walker's reception tomorrow. Thanks
and happy querying.
John Rome
Friday
30, July 1999
Many
of you may have already heard... the Founding
Father of our FINANCIAL Data Warehouse is
leaving ASU. After 17 years here, Kevin Walker
will be leaving to become the Associate Comptroller
at Vanderbuilt in Nashville. Kevin IS the
reason there is a FINANCIAL Data Warehouse
here at ASU so if you would like to join us......
Data
Administration will be making a presentation
to him at his farewell reception hosted by
the Comptrollers Office. Feel free to join
us there in thanking him for ALL he has done
for our Warehouse Users!
August
4, 1999
1:30 - 3:30 pm
Memorial Union Alumni Lounge
Last
I heard... presentations were going to be
around 2:15pm.
Nancy Dickson
Tuesday
27, July 1999 12.22 PM
Here
is a note from Val Patten who is the person
in IT who helps us build and load the STUDENT_AID
database.
The
weekly Student_Aid Data Warehouse load did
not get run on Sunday. Production support
is trying to fix the problem and load the
data today. Apparently, a JCL change that
should have been
implemented was not. I'll let you know when
the loads are complete.
Val
Patten
Applications Systems Analyst Principal
Arizona State University
Nancy Dickson
Tuesday
27, July 1999 10.59 AM
Hi
Guys... I have three fixes to tell you about
today.
1.
The 'Last Semester Admitted ASU' field in
the Student table in the STUDENT database
had its name changed last night to 'Last Semester
Applied ASU.'
If
you have a query that has a limit on that
field or that brings that field back in the
results of the query.... you will have to
rebuild the query. This change was made to
correctly reflect
the data contained in the field.
2.
The 'Fees Paid Flag' in the Student table
in the STUDENT database has been fixed to
show more correctly which students have paid
their registration and tuition fees.
Please
note... this flag is not 100% correct in predicting
which students will be removed from their
classes due to non fee payment. Students are
removed from their classes based on a very
complex process that involves both the Student
Fee system and the Financial Aid system. This
flag probably overestimates non fee payment
primarily because it does not take financial
aid payments into account. The Fees Paid Flag
also does not look at fees other than tuition
and registration... special fees, SRC fee,
etc.
3.
FOR WEST CAMPUS WAREHOUSE FOLKS!
The problem with the ASU West Enrollment Census
that was reported previously has been corrected.
Majors that were appearing as 0000 have been
changed to their correct West Campus major
code.
Nancy Dickson
Wednesday 21, July 1999
ATTENTION!
ATTENTION!
The
Student Fees Flag in the Student table in
the STUDENT database is wrong! Since this
is a time that many people are looking for
students with unpaid fees, I wanted to alert
you to this problem that was discovered this
week by Sheldon Zola, Student Affairs Research
and Jon Moorhead, Student Affairs
West Campus. (THANKS BOTH!)
Student
Fees processing is VERY complex... people
pay... people unpay... people pay with financial
aid.... people have aid taken away....people
partially pay.....etc. We have discovered
the Student Fees Flag has a Y for many students
who have NOT paid. We are looking into the
problem and will
update you as a fix is attempted.
Nancy Dickson
Wednesday 16, July 1999
Hi
All! I have two changes to the STUDENT database
to tell you about.
1.
FIELD NAME IN STUDENT TABLE CHANGING.
The
Semester Last Admitted ASU is named incorrectly!
It is actually the Semester a student last
APPLIED to ASU. It is used to join to the
Undergraduate (or Graduate) Application table
to find out if they are admitted, registered
or what. On Monday, July 26th, 1999, the field
name will change to Semester Last Applied
ASU. Thanks to Craig Leedham in the Graduate
College for discovering this problem!
You
will only have to rebuild a query if....
a. You have this field on the Request Line.
b. You have a limit on this field.
c. You have a join on this field.
To rebuild, just remove the Student table
from your query, then replace it and put back
the limits/joins.
2.
NEW AREA CODES WILL APPEAR AFTER JULY 31ST.
ACIT
is planning to make area code changes to the
SIS beginning on July 28 and continuing through
July 31. The August 1st load of the Warehouse
should contain the new area codes if all goes
well. You don't have to rebuild any queries
for this one! :)
Happy
Querying!
Nancy Dickson
Thursday
15, July 1999
The
Data Warehouse is located on the enterprise1
server. Enterprise1 will be unavailable from
6:00 PM Friday, July 16, to 6:00 AM Saturday,
July 17. The server will receive hardware
upgrades. Warehouse database access on this
server will not be possible during this time
frame. Any queries that are still running
at 6:00PM Friday will be discontinued.
We
are sorry for any inconvenience that this
may cause.
Nancy Dickson
Thursday
24, June 1999
Phase
2 of the HUMAN_RESOURCE data warehouse is
coming soon!!
What
will this mean to the Users? What new stuff
can we do? What changes have been made? Read
ahead to find out the answers...
During
our Phase 2 development of the warehouse,
we looked at what the users had suggested
as possible items to be included for query
purposes. Based on your ideas and suggestions,
we have added three new tables and several
fields to existing tables.
The
NEW tables are:
1. PAY_PERIOD_SCHEDULE
This table will aid in the determination of
which payroll schedule number is assigned
to the payroll end date you want to query.
2. EMPLOYEE_BALANCE_LEAVE_ACCRUAL
This table can be used to verify an employee's
vacation and sick accrual for each pay period.
3. EMPLOYEE_BALANCE_HOURS
This table can be used to verify an employee's
vacation, sick, or compensatory time balances,
as of each pay period.
The
EXISTING tables that will have fields added
include:
1. EMPLOYEE
* AFFILIATE_ID (will be populated)
* NAME_PREFIX
* NAME_SUFFIX
* EMPLOYEE_TYPE_CODE
* EMPLOYEE_PAY_STATUS_CODE
2. EMPLOYEE_DIRECTORY
* AFFILIATE_ID (will be populated)
* NAME_PREFIX
* NAME_SUFFIX
* BOARD_OF_REGENTS_CODE
* EMPLOYEE_TYPE_CODE
* CAMPUS_CODE
3. EMPLOYEE_PAYROLL_EXPENDITURE
* PAYROLL_TRANSACTION_CODE
4. EMPLOYEE_PROFILE
* EMPLOYEE_PAY_STATUS_CODE
* LEAVE_ACCRUAL_CODE
* LEAVE_ACCRUAL_DATE
* FLSA_STATUS
5. EMPLOYEE_PROFILE_MAY_15TH
* EMPLOYEE_PAY_STATUS_CODE
* LEAVE_ACCRUAL_CODE
* LEAVE_ACCRUAL_DATE
* FLSA_STATUS
6. ERE_BY_ACCOUNT
* PAY_PERIOD_DATE
7. ERE_BY_EMPLOYEE
* PAY_PERIOD_DATE
The
tables that had fields added will affect:
BRIO users - Your existing queries will run
the same, unless you wish to add one of the
new fields to your query. In this case, the
query will need to be rebuilt with the table
to include the new fields.
MSACCESS
users - Because the table structure has changed,
you will need to refresh your links to the
warehouse. Once you ""relink"", you will also
have access to the new fields. To 'relink'
you will go to Tools
>Add-Ins>Relink Table Manager>check
all boxes for the tables that are showing
and click on refresh.
The
dates for the tables to be placed into Enterprise1
are:
Effective
July 1st, 1999
PAY_PERIOD_SCHEDULE_NUMBER
Effective
June 28th, 1999 (with new data appearing on
July13th, 1999 for the first payroll of the
FY)
EMPLOYEE_BALANCE_HOURS
EMPLOYEE_BALANCE_LEAVE_ACCRUAL
ERE_BY_ACCOUNT
ERE_BY_EMPLOYEE
EMPLOYEE_PAYROLL_EXPENDITURE
Effective
July 1st, 1999
EMPLOYEE
EMPLOYEE_DIRECTORY
EMPLOYEE_PROFILE
EMPLOYEE_PROFILE_MAY_15TH
Effective
around September 1999
More to come...
AND,
last but not least, REMARKS will be updated
to accommodate the new fields descriptions
by the second week in July, 1999.
Hope
you have a wonderful day!!
Clara Adams
Monday
26, Apr 1999 3.07 PM
the
tables in the STUDENT database have now been
loaded with
Sunday's SIS data. The FA Tracking tables
in the STUDENT_AID
database are still being loaded but will be
complete by 4pm.
Sorry
for the inconvenience. Happy Querying!
Nancy Dickson
Monday
26, Apr 1999 11.39 AM
Some
tables in both the STUDENT database and the
STUDENT_AID database did not load last night
(4/25/99). We are working on getting them
loaded and I will send another note when they
are all there.
Nancy Dickson
Wednesday 14, Apr 1999
While
we were all checking on the new Student table
to make sure it loaded last Sunday.... lo
and behold.... all the rest of the tables
failed to load! The data was extracted from
the SIS on Sunday so the IT
staff reran the load process last night (Tuesday
4/14/99) and the missing 4/11/99 records are
now in the Student tables.
Thanks
to Hans Debano in Liberal Arts for bringing
this to our attention!
And
PS.... the Student table seems to be loading
fine on a nightly basis!
Nancy Dickson
Tuesday
13, Apr 1999
WARE-Q
has been transferred to the Exchange distribution
list. The change should be transparent for
the most part (customers sending mail to ""ware-q@asu.edu""
will do the same, as will consultants on the
other end).
I'm
keeping the old WARE-Q OLCS Group & Mailserv
list in place for a little while until all
of the Post Offices are updated with the change.
But from this point on, you should just need
to modify the Exchange DL membership when
you wish to add or remove someone.
Joseph A. McDonald
Friday
09, Apr 1999
This
weekend the new Student table will make its
appearance in the Student database of the
Data Warehouse. This table was first announced
back at the Sept 98 WUG meeting. The table's
main claim to fame is that it will be updated
DAILY and it has degree/major, minority status,
and several other data elements that we could
only get on a weekly basis before. It also
has daily athlete and Honors College status
that is not frozen.
During
and after the birth of this table, you will
see changes that will impact some of your
saved queries. You may have noticed the STUDENT_NEW
table that appeared this week. This table
has the structure of the new table. The data
will be loaded in this table on Sunday (4/11/99).
After the table is loaded and indexed, the
current Student table will be renamed as STUDENT_OLD.
It will hang around until we are sure we don't
need it. Then the STUDENT_NEW table will be
renamed STUDENT.
We
are trying to complete the change by Monday,
but there are a number of complexities (view
tables that are looking at the STUDENT table
etc.). We don't anticipate any problems but
if there are, we will alert you.
ANY
QUERIES YOU HAVE THAT CONTAIN THE STUDENT
TABLE WILL HAVE TO BE MODIFIED TO RUN AFTER
MONDAY! The reason for this is that the new
STUDENT
table has the real 10 digit Affiliate ID in
the field that says Affiliate ID. (The old
STUDENT table had ASU ID in the Affiliate
ID field.) The new STUDENT table has a separate
field for ASU ID. If you don't modify your
queries that contain the STUDENT table, you
will get no records, because you will be joining
ASU ID in the other tables to Affiliate ID
in the new STUDENT table.
To
modify your query you must remove the old
STUDENT table and replace it with the new
STUDENT table:
1.
Open the query
2. Write down all the limits you have on the
Student table.
3. Write down all the data elements you put
on the Request Line that came from the Student
table.
4. Make a note of any joins to the Student
table.
5. Click on the Student table and click Remove
(click ok too).
6. Click File/Logon and logon to the Warehouse
(DONT PROCESS).
7. Click Data Model/Table Catalog.
8. Drag the Student table out.
9. Replace limits on the Student table.
10. Replace joins..joining to ASU ID in the
Student table!!
11. Put data elements back on the Request
Line.
12. Save it!
13. Click Process.
14. Whistle a happy tune! (I had to add an
extra step so it wasn't 13!)
And
if all that fails.... you know where to send
it...... ware-q@asu.edu!
Just attach the query you are having problems
with to your email.
Oh... and need I say.... you may as well stay
off the Student
Database on Sunday while things are in process
of change.
Happy Querying!
Nancy Dickson
Monday
03, Mar 1999
The
21st Day data has been checked by Institutional
Analysis. The census
views are available in the Warehouse database.
HAVE AT IT :)
Nancy Dickson
Friday
19, Feb 1999
In
order to facilitate creating some enhancements
to the financial data warehouse, the warehouse
team has restored fiscal 1996 transactions
detail records on a VERY TEMPORARY basis.
Beginning February 18, 1999, you will see
a new table called TRANSACTION_DETAIL_1996
in the financial table listing. However, due
to limitations with disk space, this table
will be ERASED March 1, 1999.
We are letting you know of the availability of this data in case you
desire to copy selected records for your department
or college to individual data marts.
Please let us know if you would also make copies of selected detail records
for years prior to 1996 by sending a note
to ware-q@asu.edu. The level of interest communicated
will influence whether prior year data is
also temporarily made available.
Kevin Walker
Thursday
11, Feb 1999
As
you may know, the 21st day of the semester
is the day we freeze student data to get official
census numbers that are reported to the Arizona
Board of Regents. The SIS was frozen Tuesday
night to reflect the data on the 21st day
of the semester and a special Warehouse extract
was run. The Student and
Course databases of the Data Warehouse were
loaded on Wednesday night. The 21st Day Flags
in the Student Profile table and the Census
Flags in the Student Class and other course
tables were set last night and are available
this morning.
Before
the data is proclaimed as official, Insitutional
Analysis will check the data, making sure
that the flags are correct and that the data
matches official numbers. This checking process
has begun and worst case, may take 2 weeks.
In the past some ethnic codes, some GPA's
and a rare academic level were wrong and had
to be corrected.
Once
Institutional Anaysis fixes the data, it will
be loaded into the Warehouse (Census) database.
Until then, if you want an approximation of
census data, you can use the Student Profile
and Student Class tables with limits on the
Census Flags. I will send out another note
when data is available in the Census database.
If
you have any questions about 21st day data,
please send a note to ware-q@asu.edu. THANKS!
Nancy Dickson
Tueesday
19, Jan 1999
The
Student Class table has been updated. All
the data is now available...have at it!
Nancy Dickson
Tuesday
19, Jan 1999 12.18 PM
"The
Class Roster data has been successfully loaded.
Class Roster should reflect students enrolled
as of last Saturday.
Nancy Dickson
Tuesday
19, Jan 1999 12.18 PM
if
you don't update the roster during registration
week for over a week when our dept is making
decisions on classes to discontinue because
of lack of enrollment (which affects one of
my classes with low enrollment a week ago)
- there isn't much value to the service
Donald S. Miller
Monday
11, Jan 1999
The
following tables in the Course database have
not been updating:
Class
Class Running Totals
Class Meeting Time and Place
Class Meeting Time Assignment
Class Footnote
Class Enrollment by Hours
These
tables were last updated 12/20/98. A special
run will update them tonight. Thanks to Janice
Garcia in the Registrar's Office for noticing
the problem!
Nancy Dickson
Friday
08, Jan 1999
Data
Administration would like your input regarding
the ideal Student Information System at ASU.
As you may have heard, a new Student Information
System is on the drawing board. (More info
on new SIS at
http://biz.yahoo.com/bw/981210/sap_americ_1.html
and http://www.asu.edu/it/fyi/insideit/ if
you scroll down left frame.) Data Administration
has been asked for input regarding what the
new SIS needs to
DO (ie. system requirements). As a Warehouse
user, you probably have some good ideas on
that very subject! If you have time, please
answer the questions below:
1.
What tasks do you use the Student Warehouse
for that are important to your ""operations""
(operations being day to day processes as
compared to decision support type activities)?
2.
What tasks do you use the Student Warehouse
for that help you to make decisions or support
management decision making?
3.
What needs do you have for student information
that are NOT currently met by either the SIS
or Warehouse? (For instance daily, up-to-date
Class Roster downloads or management planning
reports that are needed?)
4.
Are there any other functions or data that
you would suggest be included in a new SIS?
We
are sure there will be other opportunities
for you and your department to make suggestions
as the system develops, but Data Administration
is very interested in the suggestions of our
Data Warehouse users. We will gather all your
input, organize it and present it to the design
teams that are
forming. Thanks for any time you have to spend
on this!
John Porter, John Rome, Nancy Dickson, Cyndee Miller, and Susan Moore
Tuesday
05, Jan 1999
As
many of you have noticed, the normal Sunday
update of the Warehouse did not happen. The
STUDENT database will be loaded tonight (Tuesday,
1/5/99) with data that was extracted from
the SIS last night. The reason the load didn't
happen last night was the extract of the data
from the SIS ran until
well into today. The Registrar's programs
for updating GPA's, grades, and determining
Probation/Disqualification ran last night
and we think the Warehouse data will include
this new data. Sorry for any inconvenience
but actually... I'm excited to get some of
the grade information almost a week
early!
Nancy Dickson
The
next Warehouse User's Group Meeting will be:
Thursday, Nov. 5, 1998
1:30 to 3:00 PM
In LSE (Life Science E Wing) Room 104
Notice this is a new location!!!!!
So
far on the agenda, I will demonstrate the
EIS (expert information system) layer of BrioQuery5.
This layer allows you to ""buttonize"" your
queries, arranging them into a package where
you keep a bunch of related queries. This
is similar to what Tom showed with Access
at our September meeting.
Although I will use STUDENT data, this technique
can be used with any query.The rest of the
agenda is still forming, so if you have any
requests... yell out!
Also, thanks to the folks at Academic Facilities (especially Deana Lamm)
who found us this wonderful room in the Life
Science E Wing! It holds over 100 so there
should be room for all.
Nancy Dickson
The
Student data was not updated Sunday, 10/11/1998.
A problem with the extract process from the
SIS caused the process to stop before the
Warehouse data was loaded. The load will be
rescheduled for tonight. Sorry for the inconvenience.
Nancy Dickson
Did
you remember there is a Warehouse Users Group
meeting Tuesday?
WUG
Meeting
Tuesday, October 6th, 1998
10:30 a.m. to 12:00 p.m.
Computer Commons Auditorium
Featuring
Human Resource Data Warehouse
The
Agenda items that will be discussed are:
1.
How to Change Your Password
Presented by Ernie Santiago (this is a change
from previous agenda item)
2.
Phase 2 development of the Human Resource
Warehouse
Presented by Sue Bishop
3.
Email Distribution (as was presented at the
last WUG meeting)
Presented by Nancy Dickson
4.
Faculty Profiles using BrioQuery
Presented by Elaine Moore
Looking
forward to seeing you there!
Clara Adams, HR and Nancy Dickson, DA
Notice to Users of ASU Financial Data Warehouse:
A
change is going to be made to the Transactions_Detail
table over the next couple of weeks that we
believe will improve usefulness and response
time. The plan is to create a separate table
for each fiscal year. Once the database administrators
(DBAs) have completed the transition work,
you
should see several new tables:
Transactions_detail_1997
Transactions_detail_1998
The
existing Transaction_detail table will also
remain with its present structure, BUT records
for fiscal years 1997 and 1998 will be removed
since they are being added to new, separate
tables. Because the Transaction_detail table
name is not being changed, most of your queries
will not have to be modified to continue to
run properly. Only if you have queries that
search for prior year records will you need
to make any modifications.
Once
we complete fiscal 1999, a new table called
Transactions_detail_1999 would then be added
and 1999 records would be deleted from Transactions_detail.
At that point, only fiscal year 2000 (sounds
kinda scary, huh?) records would then be in
the table at that point.
Plans
are to retain two completed years and the
current year in process on the warehouse server.
Thus, under this schema, around September
1999 the Transaction_detail_1997 table would
be removed from the server.
The
DBAs will be processing these changes in the
late afternoon and early evening so it would
be appreciated if you could plan to avoid
running queries using the Transaction_detail
table at this time of day. We will send out
an update notice once these changes are complete.
Thank
you for your cooperation. Look for additional
financial warehouse changes in the upcoming
months
Kevin Walker
Major_Code
NOT Begins With 8
You
would have to do this one on the Limit Line
and create an AND condition as follows:
((Major Code to NOT Equal 0930,0990,5300,5303,5400,1955,1956,1957)
AND (Major_Code NOT Begins With 8))
Sorry
for the omission... nothing is ever simple!
Thanks to those of you who pointed out East
just changed a number of their major codes
to an 8000 number!
PS
If you need a query that separates out East
and matches the ""official"" IA Enrollment
Summary, I can send one to you if you have
email that does attachments!
Nancy Dickson
The census data for Fall 98 has been corrected by Institutional Analysis
(IA) and the Student Profile table in the
STUDENT database matches IA's official data.
The WAREHOUSE database now contains data for
Fall 98, however, keep in mind that the Main
Campus Enrollment Census table still has
East Campus students in it.
To remove East data from the Main table, you can limit Major Code to
NOT Equal 0930,0990,5300,5303,5400,1955,1956,1957.
HERE'S ANOTHER TIP! .........If you are trying to match ""official""
student numbers as reported by Institutional
Analysis, there are a multitude of quirks
in the data that can give you trouble. Some
things that are reported have very specific
(and non-intuitive) definitions. If you have
trouble matching ""official"" numbers, be
sure to contact ware-q@asu.edu and find out
about these quirks!
Happy Querying!
Nancy Dickson
Financial
Warehouse Users--Tired of Numbers?
Do
you struggle with mental gymnastics every
time you run a query with fiscal month fields
to translate that fiscal month 08 really means
February?
If
yes, you may want to check out two new quasi-lookup
tables that have recently been added to the
financial data warehouse. You may not have
noticed these two new tables because the titles
do not begin with the word ""code"" as most
other lookup tables do; the two tables are:
Fiscal_month and
Fiscal_Quarter.
With
the Fiscal_Month table joined as part of your
queries, you can now get words such as ""February""
to appear in your results besides or in addition
to ""08"" for the time period. Is that cool
or what?
Kevin Walker
The
WISE office is trying to set up a database
for our department. This will be used to track
students that have participated in our programs
for the past five years. I am looking for
someone out there (student perhaps?) that
has worked with Access and Data Warehouse
that would like to be hired to help us create
a very user-friendly system that would directly
download info. from the Data Warehouse. We
have data for middle school and high school
students that we need information concerning
if they have come to ASU and whether or not
they have chosen Engineering.
I
know there are several departments that already
have these types of systems in place, and
I wondered if any of you have any suggestions
of how to do this or if you know someone we
can hire to help us.
P.S.
We really do have $ to hire someone if you
know of anyone!!
Shawna Fletcher
Hello.
I am interested in finding out when the next
Brio Query computer Training class is. We
are trying to access Data warehouse but unfortunately
we haven't had training on Brio. We do not
even know the passwords to use to get in to
the system.
Felicity Snyder
Sorry
to say Felicity, we have no Warehouse classes
on the schedule. The room that we usually
do training in is busy doing CUFS training.
I have asked about rooms in the Computing
Commons and I do have a line on one but have
not heard back yet.
Please
send me a note back and let me know specifically
what you need to do with the Warehouse AND
how much experience you (and others waiting?)
have with computers and maybe we can do something
to meet that need.
Nancy Dickson
Happy
21st Day of the Fall 98 Semester All!
The Census Flags were loaded into the Warehouse
last night. All tables in the Student and
Course database were updated with data from
the SIS last night also. The Census Flags
at this point are approximate. Institutional
Analysis will be checking the data to make
sure it jives with their
""official"" files. Based on what they find,
flags and data in the Warehouse may be adjusted.
This process usually takes about 2 weeks.
I will send another note after adjustments
are made and let you know when the Warehouse
is blessed as ""official.""
Nancy Dickson
