Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Kicking up Business Intelligence Data in Sage ACT! 2012

with 4 comments

Starting with Sage ACT version 2012, you can now create some nifty Excel pivot tables using what is called a Star schema.  The data goes after histories and opportunities. It’s a bit complicated to set up, but once done you can start to do some sophisticated drill downs in Excel to show history records.  Here’s a screen shot of one pivot table I just created.

The setup took longer than creating the table.  Here’s what you need to do to get access to this data in ACT.  Note – this is really geeky so you may want to find your local IT person to help out – or send us an email at pregen@egenconsulting.com and we can quickly set this up for you for a small fee.

Setting Up or Refreshing ACT! Business Intelligence (BI) Data

You need to do this on the machine that is hosting the database with your data.  Using the free SQL Management studio, you logon to SQL Server  and point to the ACT! 2012 database you want to refresh.  Here’s a series of screen shots of what I did to connect to our own database.

The shot above is where I point SQL Management studio at my ACT SQL .  Next I located my database.

I right clicked on the database name and chose NEW QUERY.  That brought up the following empty query box.

Into this box I pasted the following commands.

exec dbo.PRC_BI_Refresh_DIMs;
exec dbo.PRC_BI_Refresh_History_FACT;
exec dbo.PRC_BI_Refresh_Opportunity_FACT;

After they were pasted in, I right clicked in the box and chose EXECUTE.  This went rattling thru my database and created two new views called:

VWX_BI_HistoryView_Full
VWX_BI_OpportunityView_Full

It’s these two views that have the magic.   After the views have been created, you can close SQL Management studio (see below on how to download SQL management studio for free).  You are now ready to look for this information using Excel. The instructions coming up next are for Excel 2007 or 2010.  Email me and I can send you instructions on how to do this on older versions.

Open Excel and click on the data tab.  Next, choose the FROM OTHER SOURCES button and then choose SQL Server.  You will get the following box into which you type in the name of your server.  If you are running this on your own computer and have a database there it would be the name of your machine followed by a \act7.  If it’s on the server, then you put in the server name followed by \act7.  Here’s what it looks like going after a database on a server:

In the case above, since I am going after the data on a machine other than my own, I need to provide a username and password to access the internal data.  You cannot use your normal ACT username and password.  If this was on my own computer, and I was going after a database running on my own computer, I could use the windows authentication.  In the case above though, I want to go after the main database on the server so I am using the ACTREADER login.

The ACTREADER logon  is a great way to get to data without having to know the System Admin password.  It comes free with Sage ACT.  All you have to do, one time only, is run a little program called ACTREADER which lives in the Program Files\ACT\ACT for windows folder. When you run the program it will ask you for a password.  Enter one of your choosing and write it down so you can remember it later.  You can run ACTREADER on your own computer and it will work for databases, like remotes, that live on your computer.  If you want to run it against a server-based database, you need to run the ACTREADER program once on the server, set the password and then the password is set FOR ALL databases on that server.

After entering the server name and username and password, you click NEXT and choose your database from the dropdown.

In the box shown below I am going to choose PEC_Master.

Make sure you check the Connect to a specific table box.  It will display a set of tables.  Scroll down until you see the VWX_BI… tables as per the example below.

Click on one of the BI.. tables and then NEXT and FINISH and then choose PIVOT TABLE REPORT and a location for the table.  Let it default to $A$1.

You now have a blank Pivot Table with fields to the right that you can start dragging and dropping into the Pivot Table Layout.  The first image in this blog article was done using the Histories table.  The one below is using the Opportunities table.

Here’s what I was able to produce in just three clicks: A report of opportunities by state by year. Pretty darn cool if you ask me.

Even better, with another few clicks I was able to change the report to one by sales person and by year.

A little work on my part setting this up created some great tools for drill down reporting.  Once you save these spreadsheets, they remember the query instructions.  All you have to do is click REFRESH and it will pull in up to date data.

Nifty indeed.

Footnote:  Here’s the link to download SQL management Studio Express: http://www.microsoft.com/en-us/download/details.aspx?id=7593

Advertisements

Written by pregen

July 5, 2012 at 10:20 pm

4 Responses

Subscribe to comments with RSS.

  1. Also, in ActDiag, you can go to Database List and right click on a database and there is now an option for Refresh Analytics, that I believe does the same thing as the SQL Management Studio part.

    Amy Y.

    July 6, 2012 at 5:34 pm

    • Awesome. I didn’t realize that. Cool. Thanks Amy

      pregen

      July 6, 2012 at 6:08 pm

    • Just found out that occasionally the ACTDIAG trick doesn’t work. Thanks to Danielle Major for pointing me to this SQL command that does work.

      sqlcmd -d%1 -S.\ACT7 -E -Q”exec dbo.PRC_BI_Refresh_DIMs;exec dbo.PRC_BI_Refresh_History_FACT;exec.PRC_BI_Refresh_Opportunity_FACT;”

      pregen

      July 19, 2012 at 6:49 pm

      • Ok, just found out there is some missing details to my reply above. You need to replace the %1 in the following command with the Database name.

        sqlcmd -d%1 -S.\ACT7 -E -Q”exec dbo.PRC_BI_Refresh_DIMs;exec dbo.PRC_BI_Refresh_History_FACT;exec PRC_BI_Refresh_Opportunity_FACT;”

        According to Xavier from Sage Development, there can be a bug in the ACTDIAG refresh analytics component. You can,get around that by executing the procs directly. Make sure you are an admin on tne database server. Either enter the command above directly on a DOS prompt or put it in a bat file.

        One additional note. If you have had Excel open and couldn’t find the views, and then run this proc, be sure to close and reopen Excel to see the two views.

        pregen

        July 24, 2012 at 5:21 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: