It’s inescapable how important these four characters mean to the product’s integration with Excel.

💡 To learn more about the syntax, head to the DBRW functions page.

I want to get data from the ‘Currency Exchange Rates’ Cube.

This is how each parameter for the DBRW will be defined.

TM1ServerName = AdventureWorks

CubeName = Currency Exchange Rates

Element1 = Actual (Version)
Element2 = 2012 (Year)
Element3 = USD (Currency From)
Element4 = AUD (Currency To)
Element5 = Spot Rate (Currency Measure)

RESULT

DBRW('AdventureWorks':'Currency Exchange Rates', 'Actual', '2012', 'Jan', 'USD', 'AUD', 'Spot Rate');

In Excel.

The same value is also shown in IBM Planning Analytics via the Viewer.

Watch this tutorial on what is a DBRW and how the example above is put together.

If you would like to contact someone to find out more about TM1 and IBM Planning Analytics, email edu@cubewise.com. Head to our End User Training course (link) to learn more great TM1-Excel worksheet functions like the DBRW formula.

(disclaimer: this transcript has been automatically transcribed so it may contain errors)

Do you know what DBRW stands for? Pause right here if you’d like to think about it for a moment. So we got in contact with the inventor of TM1, Manny Perez, and he confirmed that DBRW stands for database retrieve WAN, where WAN is the abbreviation for Wide Area Network. What is a DBRW? It’s a TM1 formula used in Excel to retrieve data from an intersection in a cube.

Let’s delve a little deeper into this. To the left I have a TM1 cube and to my right is an Excel spreadsheet. A TM1 cube is a huge data storage of values and text, which can store way more information that can be stored in Excel. The best part of a TM1 cube it is cell oriented just like Excel. With this similar framework, it makes it simple for any Excel users to work with a TM1 cube.

This is where the DBRW formula is super useful. By using the formula in Excel, you can transform any cell into a window to the TM1 cube and it will have features like read and write capability. So why would you want to do this, you may ask? Have you ever been dragged down in Excel because the file is too big and it becomes sluggish? Or have you created an Excel report with the same type of contents on each tab over and over and over again?

I think we have all been in this type of frustrating situation before and have thought, is there a better solution? One of the main reasons for poor performance in Excel is the amount of data Excel is holding and calculating, so it’s working way too hard. What if this heavy data could be moved outside of Excel but still be accessible? Well, we can use the TM1 cube to take care of this heavy lifting leaving Excel super light.

Not only can TM1 store data, but it can also handle calculations as well. With TM1, all Excel would need to do is retrieve what it needs using a DBRW formula. The DBRW is an unsung hero in TM1 and Planning Analytics. We all have to give Manny credit for this simple idea. Created originally as the DBR formula all the way back in 1983 to demonstrate cell orientation between Excel and a TM1 cube.I’m going to use a cube that holds foreign exchange rates and write a DBRW formula in Excel to retrieve a value from that cube.

I will also show you how the DBRW formula can be used to write back from Excel to the TM1 cube. The cube I’m going to use is called Currency Exchange Rates. I’ll expand it so you can see how it is structured. Keep note on the cube’s dimension order as this will play a key role in the DBRW formula. Let’s say I want to grab the converted value from USD to AUD which is 0.98

in this view. To make it easier for me to write the DBRW formula, I’ll be defining the elements I want to retrieve in each dimension starting from the first dimension on top the Currency Exchange Rates cube. In the Version dimension, I want Actual. For the Year dimension it’s 2012. Period will be Jan. Currency From is USD currency, To is AUD currency. Measure will be Spot Rate. These are all elements within each dimension.

The DBRW has three areas. The first is the TM1 Server name, then the cube you want to retrieve information from, then the references to a cell for that cube. Think of this as an address to a cube cell. The cube’s address contains an element from each dimension and it must be referenced based on the cube’s dimension order. Now let’s write the DBRW formula to retrieve data from the Currency Exchange Rates cube into Excel.

I’ll begin by writing DBRW. Open bracket the server name AdventureWorks colon to separate it from the cube Currency Exchange Rates. Then encase this in double quotes. Following this I will reference the elements I had set previously that would create an address to a cell. The Version goes first. The Year, Period, Currency From, Currency To and the Currency Measure. Don’t forget to separate each reference with a comma. A closed bracket is used to close off the DBRW formula.

Once completed, the value we wanted appears in Excel and this cell with the DBRW formula is now a window into the TM1 cube. Not only can a DBRW retrieve data, but it can also be used to write back into the cube. Let me demonstrate. Notice the new value in Excel has been written back to the TM1 cube. Intrigued by the DBRW formula? Find out how you can extend Excel’s analytics capabilities with IBM TM1 and Planning Analytics through Cubewise EDU’s end user training courses.

I’ll provide the links for you below so you can find out more information.