tag:blogger.com,1999:blog-66985390857229653262024-02-07T23:22:31.804+01:00Enterprise Performance ManagementSolutions and tips for different applications and tools used in EPMUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-6698539085722965326.post-10356234712046004592017-01-16T14:56:00.002+01:002017-01-16T20:45:15.671+01:00Automate reports distribution across the corporate network using Excel VBA<div style="text-align: justify;">
<style type="text/css">
.gist {width:500px !important;}
.gist-file
.gist-data {max-height: 500px;max-width: 500px;}
</style>During the reporting process it's vital that not only reports contain the correct data, but also that the distribution of those reports reach the right destinees..... A challenging task if your manual distribution matrix contain a lot of people/folders. Whereas the right execution sometimes gets little attention, any human errors surely create a lot of fuss. In such a context, an automated solution can be considered. An investment in a good, simple but powerful mechanism ensures a quick, stable and error free task execution.</div>
<div style="text-align: justify;">
As the finance person's most favorite tool is Excel, you quickly end up writing <b>Excel VBA</b> code to do so. This blog's posting focusses on file distribution via the corporate network. <i>Another major method for distributing information is via email. A later posting will cover the tips and tricks for this solution.</i></div>
<div style="text-align: justify;">
<br /></div>
<i><u>Target audience</u>: finance readers with VBA for Excel experience</i><br />
<i><br />
</i> <br />
There are some advantages in automating the task:<br />
<ul>
<li>it also immediately serves as a documentation too </li>
<li>hand-over of the task can be easily done (even due to an ad-hoc event)</li>
<li>error-free process</li>
</ul>
<h2>
Required</h2>
<div>
<ul>
<li>a overview/matrix of <i>(source)</i> files and <i>(target)</i> directories <i>(we assume for simplicity sake that the filenames do not require renaming during the copying)</i></li>
<li>intuitive way for the end-user to alter the setup without the need for any VBA code changes </li>
<li>visual indication in case of any errors encountered during the execution</li>
</ul>
<h2>
Steps in general</h2>
</div>
<div>
<ul>
<li>Create a sheet in Excel that lists the files to copy and the directories to copy to. Foresee as many target columns as copies of the file you expect to need.</li>
<li>Run a check to see if the specified files and directories exist before kicking off the mechanism</li>
<li>Add your VBA to the workbook to copy the source files to the target directories</li>
<li>Check for any encountered errors</li>
</ul>
<h3>
Step 1: setup a distribution matrix</h3>
</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn9AXxRR5l1KeQJWPj4VqNpehtXN5hDfobRf_eIkgLoO54S5UNFHXaIT3FMQBsSGthHoybP5pC1Xujoj8bIMZnL-9ihCIdXF8CtOb2bm5QDeXqQ9b-J5uzirZVkyoMHTLLsTeySxp0yL2v/s1600/Capture_Filecopier1.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="171" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn9AXxRR5l1KeQJWPj4VqNpehtXN5hDfobRf_eIkgLoO54S5UNFHXaIT3FMQBsSGthHoybP5pC1Xujoj8bIMZnL-9ihCIdXF8CtOb2bm5QDeXqQ9b-J5uzirZVkyoMHTLLsTeySxp0yL2v/s640/Capture_Filecopier1.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Excel sheet showing files and directories</td></tr>
</tbody></table>
<div>
<b>Each line</b> on the screenshot shown above shows a single file, potentially going to multiple locations (in this example up to 3 possible copies have been foreseen).<br />
<blockquote class="tr_bq">
<i>e.g. (row 2): the Excel report for Belgium is copied into a folder for the European reporting team and another copy of the same report is put in the folder used by the team analyzing the Gross Margin. For this report the 3th option is not used.</i></blockquote>
The <b>first column </b>("Include") is a column with a switch to activate of deactivate a certain line/file.<br />
<blockquote class="tr_bq">
<i>e.g. (row 3) if we do not need to (re)copy the file for Germany, we could change the "Y" into a "N" and our code will skip this line from execution. For advanced use you could consider putting formulae that check the name of your running process, current date, reporting set, ..</i></blockquote>
</div>
<h3>
Step 2: Foresee a graphical mechanism to easily add files and directories to the matrix</h3>
As we want to avoid that the end-user constantly needs to copy in paths from his Windows Explorer to his Excel while extending or altering his list, a file dialog to "point and pick" is needed. A good way to tell the user what can be "done" at a certain sheet in the workbook is to have a message popup when he starts working with a specific worksheet tab. <br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPcEA1tCQqbumJmdR_atbKo6tHu5uc2qdNJDE1xjVvFAbziqV35i2rdt4z-enC8WWzkDpW0I3i0hixqsR5CgW4Lc3B0b_PGyMy7VXjgM9sbar5-oBFzYReEJqIHwHSeAwNMGTjn4Rqc33m/s1600/Capture_Filecopier2.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPcEA1tCQqbumJmdR_atbKo6tHu5uc2qdNJDE1xjVvFAbziqV35i2rdt4z-enC8WWzkDpW0I3i0hixqsR5CgW4Lc3B0b_PGyMy7VXjgM9sbar5-oBFzYReEJqIHwHSeAwNMGTjn4Rqc33m/s400/Capture_Filecopier2.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">User clicks on the Sheet tab (On worksheet activate event)</td></tr>
</tbody></table>
<blockquote class="tr_bq">
<i><br />e.g. The above message explains that any double clicks in the columns between (B) and (E) will trigger a dialog to select either a file (B) or a directory (C to E).</i></blockquote>
<br />
**Code**<br />
<script src="https://gist.github.com/ederouck/fddeb42388b3d931ea6ff895c75aa200.js"></script> **End of Code**<br />
<br />
Paste in above VBA code into the worksheet object to see following message box after the double-click:<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju3-I09MUsQDcPIf8lC1dGkE2wjPEJo831wsQ4wX9bFmZ95ZlHyXULU58qLSa6sSJagr5Lm6iljVoecC5ZXQu5M1rAl6NI9sh3QcD1nqQlpvbNytx7DiamZ7G9sfCWrggwJCVUOBaohQlb/s1600/Capture_Filecopier3.JPG" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="280" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju3-I09MUsQDcPIf8lC1dGkE2wjPEJo831wsQ4wX9bFmZ95ZlHyXULU58qLSa6sSJagr5Lm6iljVoecC5ZXQu5M1rAl6NI9sh3QcD1nqQlpvbNytx7DiamZ7G9sfCWrggwJCVUOBaohQlb/s400/Capture_Filecopier3.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">File Dialog that pops up after double-clicking anywhere in columns B:E</td></tr>
</tbody></table>
The choosen file is not opened but it's name and full path are written down as text in the cell from where we started our double-click.<br />
<div>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjosIFzpwGlKisFbkk5Gcv_L7nJoG1Cxa8iBO-AHuiVVPt-RTUUhYDLzm6mgxj8VtEwu02TD_iLikaVz_-ONtcY49QMYEqDeDZuaL-KQxYOq6R1WITaBXMOIUrRCnvx87urbWb0cF_dKD0c/s1600/Capture_Filecopier4.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="36" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjosIFzpwGlKisFbkk5Gcv_L7nJoG1Cxa8iBO-AHuiVVPt-RTUUhYDLzm6mgxj8VtEwu02TD_iLikaVz_-ONtcY49QMYEqDeDZuaL-KQxYOq6R1WITaBXMOIUrRCnvx87urbWb0cF_dKD0c/s320/Capture_Filecopier4.JPG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Result in the cell after clickin on "Open"</td></tr>
</tbody></table>
<div>
<h3>
Step 3: Make copies</h3>
<div>
Then you will have to build in your VBA to loop through the files .<br />
You might find some inspiration in below code.<br />
The copy action is done using following command:<br />
<blockquote class="tr_bq">
<span style="color: #666666;">filecopy file1 file2 </span></blockquote>
See Microsoft site for more info on the command: <a href="https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.filesystem.filecopy(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1" target="_blank">link</a><br />
<br />
<br /></div>
<div>
** Code **<br />
<br /></div>
<script src="https://gist.github.com/ederouck/916f2dc5eb251f5cb632a6173b21dec3.js"></script><br />
<div>
**End of Code**<br />
<br /></div>
<h3>
Step 4: Checking files and folders.</h3>
When we launch our macro it will return errors when either (1) the source file or the (2) target directory does not exist. We need to add a way to check any of those objects for existence. In case of issues with a certain source file or target directory we are going to color the cell red instead of green. I inserted the full function below.<br />
<br />
<div>
** Code **<br />
<script src="https://gist.github.com/ederouck/b6549d7da3542faf9ae3c20a4356c5a7.js"></script><br />
**End of Code**</div>
<br />
That's all folks!</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6698539085722965326.post-5531236478220499512016-12-09T14:30:00.000+01:002016-12-13T09:49:14.473+01:00Using Drill Through Reports in Hyperion Smartview add-in (for metadata)<div>
<h3>
Extra metadata information needed please</h3>
<div style="text-align: justify;">
On a regular basis following question pops up with Essbase users: </div>
<blockquote class="tr_bq">
<div style="text-align: justify;">
"We want to show <a href="https://en.wikipedia.org/wiki/Metadata" target="_blank">metadata</a> linked to our clients (or suppliers, employees,..) in Essbase. How can this be done? But do we need to load all of this into Essbase to do so?"</div>
</blockquote>
<div style="text-align: justify;">
The answer is yes, it's possible to lookup - while analyzing data in your Smartview sheet - any additional information for (a) specific dimension(s) from external tables by means of "drill through" report. </div>
<div style="text-align: justify;">
Interested? You might want to check out this video to see how the end-user experience looks like in Smartview before you dive into the below specified technical details.</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/uP4DMyaDQt0/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/uP4DMyaDQt0?feature=player_embedded" width="320"></iframe></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-size: x-small;"><a href="https://youtu.be/uP4DMyaDQt0">https://youtu.be/uP4DMyaDQt0</a></span></div>
<div>
<h3>
Essbase Studio</h3>
</div>
<div style="text-align: justify;">
There are a couple of possibilities to link data residing in relational tables (e.g. Oracle Database, txt files,..) with the data or metadata stored in Essbase. This posting explains the use of <b>Essbase Studio Console </b>for <u>querying additional metadata residing outside of the Essbase cubes.</u></div>
<div style="text-align: justify;">
Essbase Studio is the updated version of the old Integration Services but combines this concept with the possibility to do more "classic" admin actions, as we know them from the <b>Essbase Administration Console.</b> <b> </b> You might already have heard that Oracle positions the Studio as the future standard toolbox for the Essbase administrator, so it's worth spending some time on this.</div>
<div style="text-align: justify;">
A typical setup within Studio is to make a connection to a number of tables containing either data and/or metadata. Studio can then automatically propose a star schema that can be used to populate your dimensions and to load in the data.</div>
<div>
<ol>
</ol>
<div>
<h2>
To Drill-Through or not, that's the question</h2>
<h3>
Cube Setup</h3>
<div style="text-align: justify;">
In our example we would like to see the phone-number for Adam, one of our employees. We have following information available in a table of our sample HR system:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOhitm_byEr7ARY4EVMRRFLqHF5y-c0fWHbRSiUQoCAF7pipBFiisxWMcKN45dlUhp_F3GiBjHgeAw-rSomku0SEl35gkSOj6j24VRqQwCeflB_GVmZK_19lN4BYeFfqSCzwg85-qScOtA/s1600/Database+values+employees.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="313" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOhitm_byEr7ARY4EVMRRFLqHF5y-c0fWHbRSiUQoCAF7pipBFiisxWMcKN45dlUhp_F3GiBjHgeAw-rSomku0SEl35gkSOj6j24VRqQwCeflB_GVmZK_19lN4BYeFfqSCzwg85-qScOtA/s640/Database+values+employees.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Content of the relational table from Oracle Database visualized in an Excel sheet</td></tr>
</tbody></table>
</div>
<div style="text-align: justify;">
We only loaded in the Employee-id, using the combined First and Last Name field as the alias. One could off-course decide to additionally include these fields into the Essbase database and query them natively, but there might be some advantages in not doing so:<br />
<div style="text-align: start;">
<ul>
<li style="text-align: justify;">infrequent use of the data</li>
<li style="text-align: justify;">ownership of the data</li>
<li style="text-align: justify;">or you just don't want to make this part of the process</li>
</ul>
</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk1WKAo8wEfKAObGE_tyLHchFFyoIcKtW9r5ZG7gKQm8s8p8YmGOnz0J81dJjo8V843OaEdQxIGK2BR3leacNorjV1LaiEkrnzSTC1-KlWeod8yiTB4W2_k5hIvg3FBtDWngVPMuNS7lQn/s1600/employee_dim_setup.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="321" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk1WKAo8wEfKAObGE_tyLHchFFyoIcKtW9r5ZG7gKQm8s8p8YmGOnz0J81dJjo8V843OaEdQxIGK2BR3leacNorjV1LaiEkrnzSTC1-KlWeod8yiTB4W2_k5hIvg3FBtDWngVPMuNS7lQn/s640/employee_dim_setup.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Studio - view of the setup of the hierarchy/dimension</td></tr>
</tbody></table>
<br />
In our outline editor (Essbase Administration Console) this looks like this:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnARJ_ssXgJEm3tyRDPUkXRs3GJK6g2io3-aKtcA7dwNnOZ30NNBLU9e1VrymtzG26KuyJnGhWKk0o7i-9j7v5H5Mc22PuTB2eq7347TBI1ioW3oUBhCkJMQ-4b_D2ED-jKJur8tZnMPuC/s1600/ess+con+otl.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="340" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnARJ_ssXgJEm3tyRDPUkXRs3GJK6g2io3-aKtcA7dwNnOZ30NNBLU9e1VrymtzG26KuyJnGhWKk0o7i-9j7v5H5Mc22PuTB2eq7347TBI1ioW3oUBhCkJMQ-4b_D2ED-jKJur8tZnMPuC/s400/ess+con+otl.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Administration Console - Outline Editor</td></tr>
</tbody></table>
<br /></div>
<ul>
</ul>
<div>
<div style="text-align: justify;">
<h3>
Drill Through Report Setup</h3>
</div>
</div>
<div>
One of the objects that can be added in the Studio Editor is the "Drill Through Report" option we are looking for.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivxboKydqA88o6yHwdjJevicithxjhlCe_dfH2qHjDMtKRrqSBPJT26wlB3V4Trmp7M44y-xQMwv9cu2BX5btPhLs3mXvsSyEad1Zyu6ZbP9UIH3-SvZbfj4T339ejTNn7Fv9Xre-8QYIa/s1600/drill+t+option.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="340" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivxboKydqA88o6yHwdjJevicithxjhlCe_dfH2qHjDMtKRrqSBPJT26wlB3V4Trmp7M44y-xQMwv9cu2BX5btPhLs3mXvsSyEad1Zyu6ZbP9UIH3-SvZbfj4T339ejTNn7Fv9Xre-8QYIa/s400/drill+t+option.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Studio</td></tr>
</tbody></table>
On the 'Context' tab, you choose the dimension(s) the report should work with..<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmKLPtPPPR805uJ800AtUNMptV7qiAePZBPOLd7iBVN5L9qHGOmOdXXuk_40FVQNO1tdKipSrtvtVmbNUxgtk1EXA6n17iv3uGeCq2s9_ALEvev0_ZD5v4DVeqjADZwXbG2lrt87OnlL9a/s1600/setup1.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="367" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmKLPtPPPR805uJ800AtUNMptV7qiAePZBPOLd7iBVN5L9qHGOmOdXXuk_40FVQNO1tdKipSrtvtVmbNUxgtk1EXA6n17iv3uGeCq2s9_ALEvev0_ZD5v4DVeqjADZwXbG2lrt87OnlL9a/s400/setup1.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Studio - Content Tab</td></tr>
</tbody></table>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
.. and in the 'Report Contents'- tab, select the columns from the table that should be shown when the drill is executed.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihyphenhyphen_bO2SvcwUJ7ivfd2_H5GTc5rJGmxsTJtVM61d_Vx7RC9X4YQCKP1ArVqomcxmDIq42lYfBIVkmpBALkkdrslPbVOjFwSIG94hUuC3eLaOkZuj9o5l2lXa6VUxA0aEwHwqpdr2Zx5TX9/s1600/setup2.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="544" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihyphenhyphen_bO2SvcwUJ7ivfd2_H5GTc5rJGmxsTJtVM61d_Vx7RC9X4YQCKP1ArVqomcxmDIq42lYfBIVkmpBALkkdrslPbVOjFwSIG94hUuC3eLaOkZuj9o5l2lXa6VUxA0aEwHwqpdr2Zx5TX9/s640/setup2.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Studio - Report Content</td></tr>
</tbody></table>
<br />
VoilĂ , this will make our example work. The result can be seen in the video on top of this posting (if you haven't done so).<br />
More advanced setups may require the tuning of the SQL query with the necessary variables to link table and dimension(s).<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTcQKxtLf_X9VkHgE_b6b9JTqt7yhyphenhyphenTuVzBJx2ls0cnYUO26NR5KC7IqRfoilIvsv9anoyCnnugm6kHEOivmlAd04B0H7Setk3X7cXWreVpDToBbQtQMoVgLvQG1anCPax_ppC8FHHXmS6/s1600/sql+query.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTcQKxtLf_X9VkHgE_b6b9JTqt7yhyphenhyphenTuVzBJx2ls0cnYUO26NR5KC7IqRfoilIvsv9anoyCnnugm6kHEOivmlAd04B0H7Setk3X7cXWreVpDToBbQtQMoVgLvQG1anCPax_ppC8FHHXmS6/s400/sql+query.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Essbase Studio - Report Contents - Template SQL</td></tr>
</tbody></table>
<br />
<br /></div>
</div>
</div>
Unknownnoreply@blogger.com1