.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Tuesday, November 02, 2004
  From the datawarehouse to the chart (part 2)
Last week I introduce the MS Query feature of Excel.
Let's talk today about a few tricks to optimize its utilization with Oracle.

Ok first you may have notice that this a very slow feature. Changes to the source query are uneasy. You need to open the source query. Then to use an old fashion query defintion tool. And then to go back to the Excel file to finally realize that you have to change some criteria in the SQL code.

How to speed up this?

Use your knowledge of the database
Make Views.
It seems to be an easy-to-say thing but, to my point of view, this it is critical. I think that Excel by it self don't have to be a datamart and shouldn't. You should Use Oracle Views to reformat or re-structure your data. There is many standard and Oracle specific SQL statements which can help you for this (that is a good idea for a next post).
Use routine to change SQL.
You are the developper. You know where to get the information in the database.
Most of the time you define your queries in your fovarite Oracle tool and not in Excel or MS Query. So you don't need a wizard to explore the database. Instead you may need, as I, a quick way to seek-and-destroy bugs.

I prefer to use my own a VB statement (which will be not stored into the final file).
Here is the sub I use :

Sub createquery(strODBC, strSQL, strDestinationRange, strNAme)
With ActiveSheet.QueryTables.Add(strODBC, Destination:=Range(strDestinationRange))
.CommandText = strSQL
.Name = strNAme
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

strODBC is the an ODBC string something like :
strSQL is the SQL statement you want to execute here
strDestinationRange is something like "B2" or "$A$12:$H$52"

You should use this statement carefully at your own risq and, off course, fit it to your needs.

Notice that you may change the ODBC string structure, for instance if you omit thePassword excel will prompt you to logon. Notice that

Using parameters.

Parameters allow you to change criteria of a query without any VB code.
Here is the way I use parameters in MS query :
I use my favorite Oracle developpement tool and make a SQL query without parameters for instance :

select name from customers where max_ammount>1000
and min_amount <100

here is the versions with parameters

select name from customers where max_ammount>?
and min_amount < ?

Yes, there is no named parameters. Excel have specifical way to bind those things, just using order.

Try it, for instance with the preceding VB code, or with the MS Query tool.
When refreshing the data, Excel ask you to fill those parameters.

At any time you can specify the source for each parameters:

The first option is interesting. It allows you set the text of a question which will be asked to the user for this parameter.

Notice that when you use VB code to create a query with parameter, Excel gives default names to these parameters. If you want set your own names use the following syntax in a vb module :

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables("MyQuery")

qt.Parameters("Parameter1").Name = ("myFirstParam")

qt.Parameters("Parameter2").Name = ("mySecondParam")

All properties of parameters seems to be reachable by rightclick->parameters but the name which can be change only by code.


Now you know how to make get data from parameterized query over Oracle into your Excel workbook, I suggest you the following if you need to produce many charts from yout datawarehouse.

I - Let's suppose you have built a View describing the monthly 's turnover of your products. In a new excel sheet type-in the product id of one of your products, build a query like the following :

Select MonthNumber, turnover from v_monthly_turn where product_Id = ? and year = 2004

II - When the wizard prompt you for the parameter's value, select a cell which contains your product's ID and check the box to make this permanent.

III - When the data are returned select the range. Click on the chart button an choose your chart's style and location.

IV - Change your product's ID and refresh the data by right click->refresh data over the data range.

You may customise this:

Instead of a cell based parameter use a parameter whith a question like "What is the product ID?" . You may then set the "Refresh on file open" property to yes :(right click on the range then "properties") your file will then become a little decisional tool. And this without any VB code!

That 's all for today thank you for your attention.

All comments are welcome.

Hi, i'm trying to give parameters in excel cells to a subquery in MicroS Query using VB. my problem is that MS query does not allow to give parameters to a sub query, so that's why i'm doing it in VB.

This is what i have:

Sub Macro4()
' Macro4 Macro
' Macro grabada el 25-01-2007 por aruizs

Año = ActiveCell.FormulaR1C1
RangoDeAproximacionEnAños = ActiveCell.FormulaR1C1

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\practica\depositos.mdb;Defa" _
, "ultDir=C:\practica;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTi" _
), Array("meout=5;")), Destination:=Range("A7"))
.CommandText = Array( _
"SELECT d.instrumento, d.reajuste,d.`fecha cintap`, abs(d.duracion-" & Año & "" _
, "*365) as Alcance, d.Duracion, d.TIR" & Chr(13) & "" & Chr(10) & "FROM `C:\practica\deposito" _
, "s`.Capital d inner join(select c.instrumento, c.reajuste, c.`fecha cintap`, min(abs(c.duracion-" & Año & "*365))" _
, "as Alcance " & Chr(13) & "" & Chr(10) & "from `C:\practica\depositos`.Capital c " & Chr(13) & "" & Chr(10) & "where (c.instrumento='bcp' and c.reajuste='no' and (c.duracion>" & Año & "" _
, "*365-" & RangoDeAproximacionEnAños & "*365/2 and c.duracion<" & Año & "*365+" & RangoDeAproximacionEnAños & "*365/2)) " & Chr(13) & "" & Chr(10) & "group by c.instrumento," _
, "c.reajuste, c.`fecha cintap` " & Chr(13) & "" & Chr(10) & "having (min(abs(c.duracion-" & Año & "*365)))) a " & Chr(13) & "" & Chr(10) & "on a.alcance=abs(d.duracion-" & Año & "*365) and" _
, "d.instrumento=a.instrumento and d.reajuste=a.reajuste and d.`fecha cintap`=a.`fecha cintap`")
.Name = "Consulta desde MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

If yo know how to work it out, please let me know.

can't answer your question directly . But just try to make your sql code a bit more readable and your idea should work.
Post a Comment

Links to this post:

Create a Link

<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

From the datawarehouse to the chart (part 1)
Using updatable views in Access
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /

Powered by Blogger