Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model

19 06 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

From Power Pivot to SSAS Tabular

As companies move through the cycle of building Excel based solutions for business intelligence and analytics, they eventually end up with a SQL Server Analysis Services Tabular Model. The tabular model comes into play when you need more data in your model or want to support more granular security.

Up to this point, users have been happily using Power Pivot models in Excel to build their analysis solutions. However, once the model is deployed to tabular some functionality or interaction with the model changes in significant ways.

To summarize this point, power users or data modelers will create Power Pivot models in Excel. These models may or may not be deployed SharePoint, but they need to take them to the next level. You can migrate a Power Pivot model to tabular with ease by using the import option in SQL Server Data Tools.

image

Interacting with Power Pivot

I started by creating a simple Power Pivot model using Adventure Works DW data based on the Internet Sales fact table. I am using seven tables in my model as shown here.

image

I am not going to add any calculated measures to the model because Power Pivot allows me to use the data as it sets. Next we create a pivot table based on this model. I dropped the Fiscal Year onto rows and added OrderQuantity and ExtendedAmount to the values region. When OrderQuantity and ExtendedAmount are added to the pivot table, Excel defaults to a sum calculation when working with the data. Basically Excel creates the calculation for you based on what it knows about the data.

The point here is that I have data that can be used as values without doing any additional work with the model. I saved the workbook, closed Excel and moved on to the next step.

Interacting with Tabular

First we need to convert the Power Pivot model to a tabular model. Which is done by importing the model we just saved in SQL Server Data Tools. Once we have the project open, we need to deploy the model to a SSAS tabular instance so we can connect to it with Excel.

image

Now that it has been deployed to SSAS we can reopen our workbook and add a connection to the tabular model. In the field list we notice three differences now that the model is tabular.

1. The SUM symbol (sigma) is used to highlight values or measures that can be calculated.

2. The values we created in the Power Pivot model show up here.

3. In the Values section, “_No measures defined” is shown.

image

When working with multidimensional models, the Values section are represented the same. That makes sense as the connection that Excel is using is based on MDX not DAX. This significantly changes the user experience.

Let’s add a new measure to our Power Pivot model and try to do the same in the tabular model. We can still drop the DiscountAmount into the values section in our pivot table based on Power Pivot. However, when we try to do the same on tabular we get an error saying that we cannot add it to that area of the report.

image

In order for us to use DiscountAmount as a measure we will need to create an OLAP measure (See Excel Tip #8 for details) to use it in this Excel workbook or we will need to add it as a calculated measure in tabular and redeploy for it to be available.

What’s Happening

Because Excel treats a tabular model the same as a multidimensional model in SSAS you will need to add calculated measures for all measures you want to use as values in pivot tables in Excel. Multidimensional models are highly structured using the dimension and measure group techniques. While tabular “feels” like Power Pivot, to be used by Excel it needs to appear structured like multidimensional cubes.

Making this more interesting is that Excel uses MDX to communicate with tabular models, not DAX. As a result, we are able to use the OLAP tools in the PivotTable Tools ribbon.

image

This option is not available when working with Power Pivot models in Excel.

Impact to Users

Overall the impact to users, in particular power users and report builders, is that they have less “freedom” to design when using a tabular model. If they want to add more calculations, they need to be familiar with MDX. Furthermore, if they want the calculations to be generally available they need to work with IT to deploy updated models.

Hopefully we will see DAX supported interaction with SSAS in the future, but for the moment you need to understand how tabular and Power Pivot differ when using pivot tables in Excel.





Exploring Excel 2013 for BI Tip #15: Locking Slicer Position

18 06 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

A picture, or in this case two pictures, are worth a thousand words. I created a pivot table from Power Pivot and then added two slicers above the pivot table. The pivot table contains a date hierarchy which can be expanded and collapsed. During this process the slicer moves around which is not optimal when you are creating a visualization in Excel such as a dashboard. Here are the screenshots which highlight the issue.

How I set it up:

image

What happens when the date gets expanded:

image

How to Fix the Slicer Position

Right click the slicer you want to keep from moving, in my case that is the second one. I first looked in the settings, but saw nothing. I stumbled onto the Size and Properties option which opened the Format Slicer slide out menu. If you expand the Properties section select “Don’t move or size with cells” option, the slicer will no longer move.

image

This is just one more way to use slicers to improve the user experience in your Excel dashboards and reports.





Setting Up Tabular Models on Windows Azure

12 03 2014

In my last post, I discussed how to set up Oracle in Windows Azure. During a customer call, there were questions about using SQL Server Analysis Services (SSAS) tabular models with Azure. This post will walk through setting up an Azure VM and deploy a tabular model to that VM.

If you do not have an Windows Azure account you can use a trial account with your Microsoft or Live account. Check out http://www.windowsazure.com for details on how to “try it free.”

Setting Up the VM in Azure

From the Management Portal on your Azure account, select Virtual Machines from the left then Add at the bottom. On the next screen, choose to create your VM from the gallery. You should see the Choose an Image option as seen below. As you can see, I have the SQL Server 2012 SP1 Enterprise image highlighted. You will need to use the Enterprise license as Tabular does not run on Standard. In this case, the Data Warehousing image is NOT the Business Intelligence Edition of SQL Server.

image

You can also choose to create a “blank” VM and load up SQL Server on your own. I chose to use the image to speed up the process – which it did substantially.

After selecting the image, the next few steps guide you through setting up the VM. For the most part, the defaults will work fine. Be aware that once this is turned on, you will be charged for it running. It is up to you to make sure you understand the costs, even if you are using the free trial.

During the setup steps, you will create the VM and its related cloud service. Remember that the account is your admin account for the VM and you will need those credentials to Remote Desktop into the VM. On the last, setup page is the Endpoints. Leave the defaults, we will add an endpoint for our tabular model later.

At this point, it will take a few minutes to set up your new VM. Once it is setup, open a Remote Desktop session into it. If you look at services or at the SQL Configuration console you will notice that everything except a tabular instance have been set up for you. As a result, I would not recommend using this gallery image for a production deployment. You should look at creating your own template if you want a more locked down and refined setup.

Setting Up the Tabular Instance in Azure

As noted before, the tabular instance is not set up. The installation media is on the server, so you can run that to install your tabular instance. I won’t walk through the install process, but this was great to find because that meant I did not have to copy media to my VM.

Making the Tabular Instance Available

This section covers the tedious tasks required to make your tabular instance available for querying outside of the VM. There are three basic steps to getting your instance “online”: setting the port number in SSAS, updating the firewall, and adding endpoints. I will walk you through the steps I used to get this done followed by some references that helped me get here.

Setting the Port Number in SSAS

By default, SSAS, both multidimensional and tabular instances, use dynamic ports. In order, to allow connections through the firewall and endpoints, the port number needs to be fixed. I used guidance from TechNet and did the following steps to set the port.

    1. Opened the Task Manager to get the PID for MSOLAP$<<instance name>>.
    2. Ran netstat –ao –p TCP to get a list of ports used by current processes. Once I had identified my port number, I also noted the server IP address which is required in the next step.
    3. I chose to confirm that I had the correct port by connecting to the instance using the IP address and port number.
    4. Next, we have to go old school and modify the msmdsrv.ini file. The typical install path for this file is C:\Program Files\Microsoft SQL Server\<<instance name>>\OLAP\Config\msmdsrv.ini.
    5. Open the file in notepad and find the <Port>0</Port> tag.
    6. Change the port number to the port number that was identified above. (Technically we could have used any open port number. I chose to do this because I was sure the port number was available.)
    7. Save the changes and restart the service.
    8. Once again confirm you can connect to the server with SSMS using the IP address and port number.

Now you have set up the SSAS instance to use a fixed port number.

Updating the Firewall

Now that we have a port number, we can create a firewall rule. We access the firewall rules from the Server Manger. In the Windows Firewall console, we will be creating a new Inbound Rule..

image

  1. The rule type is Port
  2. We will apply the rule to TCP and specify the port we defined above.
  3. On the action tab, we selected Allow the Connection. (If you are planning to use this in a production environment, you will need to verify your connection requirements.)
  4. Next, we clear any connection we don’t want to apply.
  5. Finally, I named the rule with a descriptive name.

Now we have the firewall rule in place to allow external connections for the tabular instance.

Adding Endpoints

The final step to making the instance available is to add the endpoints in Azure. In the WIndows Azure portal, we need to go the VMs page again, select our VM, and open the ENDPOINTS tab. From here we create a new endpoint.

  1. We are creating a standalone endpoint.
  2. Next, we select the TCP protocol and add the port number to both the private and public port textboxes.
  3. Finally, we apply the changes.

We are now ready to test connectivity.

References

Setting up VM Endpoints

Configuring Windows Firewall

Configuring Windows Firewall with SSAS

Connecting to the Tabular Instance in Azure

So to verify this works, I wanted to connect to the model with SSMS on my desktop. However, it uses Windows authentication and I am not on the same domain. (My IT staff is likely still laughing about me joining my VM to our domain.)

Thankfully, Greg Galloway (blog) reminded me of how to set up runas to use different credentials to connect to SSAS. Here is the syntax I used to connect to the tabular instance on Azure using a command window:

runas /netonly /user:<<VM name>>\<<username>> “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

This allowed me to open SSMS using those credentials. Next, I used the VM + port to connect. You will see that the Windows credentials in the dialog are not what you entered on the command line. This is expected and the credentials you entered in the command line will be passed through to the VM.

Deploying a Tabular Model to Azure

I attempted three different ways to deploy my model to the Azure VM. Two worked, one did not.

Deploying from Visual Studio on My Desktop

My first attempt was to deploy from Visual Studio on my desktop. I set the deployment properties to point to the Azure instance using the same credentials I had used with SSMS. I also set up a command line execution to use runas like with SSMS.

It appeared I could connect, but I continually got a permissions error which is shown below. After much frustration, I gave up on it and moved to the next options.

image

Using XMLA to Deploy

This is the most straightforward way to deploy an SSAS DB on a different server. I used SSMS to generate the Create Database XMLA statement. Because I had not deployed it locally, I needed to modify the XMLA statement to remove the user name and guid from the database name and database ID. (AdvWorksTab1_SteveH_<<Some GUID>>)

In a bit of irony, I can use the SSMS connection from my desktop using the runas to deploy the database to the VM.

The reality is that this is easy and acceptable way to deploy SSAS databases to production environments, in Azure or in your datacenter.

Deploying from Visual Studio on the VM

The final method I used was deploying Visual Studio onto the VM and deploying from there. I used VisualStudio.com (TFS online) to transfer the source code from my desktop to the VM. I had to install the TFS client on the VM, but SSDT with BI tools is already there.

  1. Installed the VS 2010 TFS Client: http://www.microsoft.com/en-us/download/details.aspx?id=329
  2. Then installed Visual Studio SP1  http://social.msdn.microsoft.com/Forums/vstudio/en-US/4e4851dc-eb29-4081-9484-d38a6efa07ee/unable-to-connect-to-tfs-online?forum=TFService
  3. Finally installed VS2010 Team Foundation Server Compatibility GDR (KB2662296) http://www.microsoft.com/en-us/download/details.aspx?id=29082

Now it will connect to TFS Online. I got the latest from my project and pointed the project to my tabular instance.

Be sure to check the impersonation settings.

Next, I deployed the project to the local tabular instance on the VM and it worked great. This might make sense for development, but I would not use this method in a production environment.

Some Closing Thoughts

I was amazed at how simple it was to create the VM and set up tabular in Azure. Knowing what I know now, I would be able to set up a usable instance fairly quickly and deploy a database using XMLA without much effort. That was very nice.

Doesn’t work with Office 365

I started this project to determine the connectivity capability with Office 365. Well, that does not work in my current configuration. I was able to create a workbook on my desktop using my Azure tabular model and Excel. It works just as you would expect. However, when I deployed the workbook to Office 365, data refresh always failed.

image

My next thought was to use a data gateway, but those only work with SQL Server Data Engine and Oracle, not SSAS. This is still a significant issue with making Power BI able to fully take advantage of existing BI assets at a company.

Using Azure Active Directory

My next step would be to use Azure Active Directory services to try to get Windows Authentication to work throughout. But that is for a later project and post.





Exploring Excel 2013 for BI Tip #14: Sparklines and Pivot Tables

7 01 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Sparklines and Dashboards

There are a lot of visualization possibilities with Excel. When creating dashboards, sparklines are a good visualization of what happened over a data series. My goal was to add sparklines to a pivot table so it could be added to a dashboard. After many failed attempts, I was able to get the following to work.

On the INSERT tab, you will find the Sparklines options. In my pivot table I am going to add Line and Column Sparkline visualizations using the MyVote submission counts.

image

Here are the steps that I used to add this visualization to my pivot table.

First, I created a pivot table with Submission Count as the measure, the rows were the Poll Categories, and the columns are the quarters of the year. Here is what the original data looks like.

image

In this case, I kept the Grand Totals for both columns and rows turned on. I am going to use these areas as the targets for the sparklines. I am going to use lines for trends over time on the Grand Total column. Then I am going to use the column visualization to show the category distribution on Grand Total row.

Adding the Line Sparkline

To add the line sparkline, select all of the data cells (no grand totals). Next, select the Line Sparkline option. This will open the Create Sparklines dialog. In the dialog, you can see the Data Range is already populated with the highlighted cells. The Location Range is empty as shown below.

image

Next, you select the columns in the Grand Total column, and that cell range will be added to the Location Range field. This will put the sparklines in those columns and they will match the data trend. For clarity, the final step would be to change the column name to “Trend” and change the font color to white so the text is not seen. Here is the result.

image

Adding the Column Sparkline

Next up, we will add the Column Sparkline. Highlight the same cells as before. Once the cells have been highlighted, select the Column Sparkline option. Select the Grand Total row for the location. This will show the distribution within the quarter for the categories. Changing the font to white does not hide the value in this case. I actually reduced the font size to 1 to make it nearly invisible. (There is no transparent font available.) Here is the result.

image

I also added lower right corner by selecting the Grand Total column cells as the data and that cell as the location to get a consistent look at distribution. One other note, the Grand Total row is called “Trend” as well because they have to have the same name. But, overall, this was the look I was working toward.

Limitations and Nuances with Sparklines

Now for the stuff that doesn’t work as you would like. Sparklines are technically not part of the pivot table. As a result, the table needs to be static in shape. This means rows and columns need to stay the same in count and position.

I am going to add a category slicer to my example. When I select the Entertainment category, all of the sparklines are “stranded” in space. Quarter 2 disappears because it has no data and as a result the trendlines are no longer in the table. This is also true for the columns as four categories are eliminated by the filter. Worse yet, if you look at the filter, you will notice we have no poll submissions in the News category. When that is added the sparklines will end up in the last data row as opposed to the Grand Total sections.

image

Sparklines are a nice tool to have, but you need to understand what is the best way to use them in the context of what you are doing.

Reference and Credit

I ran across this during my search for how sparklines work in pivot tables: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-you-insert-a-sparkline-into-a-pivot-table/e072570d-b367-41f1-b2d6-2dbe939db311.  As I note with the limitations to my solution, the forum post above calls out some alternatives which allow for more dynamic approaches, but they also involve coding. Furthermore, the comment from Andrew Lavinsky (MVP) confirmed that this was possible and that it is supported in SharePoint Excel Services.





Exploring Excel 2013 for BI Tip #13: Connecting Slicers in Pivot Tables Sourced from a Power Pivot Model

17 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

At Magenic’s Code Mastery event in Boston, Stevo Smocilac and I were presenting on Power BI and Tabular Models. After our presentations, one of the attendees presented an issue to us about how he was unable to get a slicer to filter two separate pivot tables in his workbook. This will be the third tip on slicers as we investigate this issue to find a resolution. (Tip #4: Adding a Slicer and Tip #5: Cleaning Up Slicers)

To reproduce the issue, you will need to create a Power Pivot model with two related tables. In neither case will we have calculations. The primary table in my illustration is the Poll table with two fields PollID and PollQuestion. Create a flattened pivot table from the Power Pivot window as shown below.

image

Now that I have the Power Pivot connection and the pivot table ready to design in the workbook, I added the PollID and PollQuestion fields to the pivot table. Next, turn off subtotals for all fields. The next step is to add the slicer for the PollID.

Once all this is complete, we need to add another pivot table. In my case, I can use the PollOption table which has each of the valid options for each poll, essentially a clean one-to-many relationship that was built into the Power Pivot model. I added the next pivot table to the worksheet using the following steps:

1. From the Insert tab on the ribbon, select Pivot Table

image

2. In the dialog that opens, select “Use an external data source” and click “Choose Connection.”

image

3. Select the Power Pivot model as your source and click Open. Then select OK on the next dialog. This will result in another pivot table which shares the Power Pivot model as the source.

image

Once you have the new pivot table add the columns from the related table. In my worksheet I selected the PollID and OptionText from the Poll Option table. The final step to reproduce the issue is to relate the existing slicer to the the new pivot table. This is done by right-clicking the slicer box and choosing the Report Connections option. From here you can add the new pivot table to the Slicer connections.

Now that this is set up, when you click a slicer option, it filters the first table, but not the second as shown here:

image

As you can see the first table filtered correctly, but not the second.

Solving the Issue

It turns out the solution is fairly straightforward, but not something you would normally think of. If you add an aggregation to the related table, Poll Option, then the slicer works just fine.

image

It does not seem to matter what is aggregated, but an aggregation is required. While I was unable to find any reason why, my best guess is that this is related to the fact that pivot tables still communicate with Power Pivot models using MDX. I have seen this happen when working with multidimensional models where you needed a value in order to get the slicer or filter to work correctly, even in MDX itself.

While this solves the problem, we are now left with a column we did not want on the pivot table. It would appear that the only option we have to make the column not appear is to hide the column. However you will need to tread carefully here as it hides the column for all tables below as well.





Exploring Excel 2013 for BI Tip #11: Color Scales

28 08 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Color Scales

In the last tip we covered using conditional formatting with Data Bars. Next up is Color Scales.

What Are Color Scales?

Color Scales are a conditional formatting feature that can be applied to cells in Excel.  Color scales “color” the cell based on the data that the formatting is applied to.  The color scale feature works with pivot tables and standard cells in Excel.  Our focus will be on using color scale with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image_thumb1

image

First starting with a single column, we can use use Quick Analysis or the Ribbon to apply our Color Scale rules. While the data bar used size to highlight the differences in the data, color scales will, obviously, use color. The most common color scale to use is to use Red for lower numbers to green for higher with yellow or white in the middle. Let’s apply that to our column of data.

image

This is particularly effective when working with percentages or other values where red can mean “bad”.  Keep in mind that you can change the order of the color scaling so that a higher number can be red as well.  You can also use two color scales when that is all that is needed.  I have used two color scales in cases where only two values are present such as a Boolean value.

When setting up the formatting, you may notice a “box” in the corner.  This can be used to tell Excel how to apply the formatting.

image

This works great when you pick the first cell in question then use this short cut to set the formatting based on how you want to apply the changes. The  only rule type that applies in this scenario is the “Format all cells based on their values” which options are shown in the shortcut.

While a very simple visualization technique it can be to highlight differences in a compelling way.

Next tip will cover Icon Sets. See you then.





Exploring Excel 2013 for BI Tip #10: The Data Bar

30 07 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using the Data Bar

This feature has been a part of Excel for a long time.  However, as with any tool, some of the oldies are really good.  As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years.  So as part of this series, I will also highlight some important visualizations that have been around.

What is the Data Bar?

The data bar is a conditional formatting feature that can be applied to cells in Excel.  Data bars “fill” the cell proportionally based on the data that the formatting is applied to.  Data bars work with pivot table and standard data in Excel.  Our focus will be on using the data bar with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image

image

As you can see Data Bars are one option under Conditional Formatting.  Look for future tips to come on some of the other Conditional formatting options.

Adding Data Bars

The following sample is from the MyVote data generated from the Modern Apps Live! project.  In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.

image

To add data bars highlight the area to add the bars and choose a format.

image

Advanced Settings

By clicking More Rules … you will be able to apply advanced options.

image

In my opinion, the most important setting to use here is at the top.  When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.

However, this option will also highlight the any total columns (in our case Grand Total was included).  If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”.   You will find that this is the most common option to select when using data bars for data visualization.

Modifying Existing Data Bars

Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down.  This will open a dialog box which has the formatting rules for the selected pivot table.  There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook.  From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.

Data bars are a simple, but effective data visualization when you need to highlight the variance between values.  With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.





Exploring Excel 2013 for BI Tip #9: Adding Calculated Members

25 06 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Adding Calculated Members to the Excel 2013 Workbook

In my last tip (#8), I discussed using calculated measures.  In this tip, I will talk about creating a calculated member.  The primary difference is that a member becomes a part of a dimension and can be used as a filter, column header, row header, or even a slicer.

In my simple example, I want to created an aggregated set of categories used in my polls called “Cool Cats” which contained the Fun, Entertainment, and Sports categories and puts my new member in the Poll Category attribute hierarchy.  I will use this to see how many submissions there were in these categories.

As before, you can create and manage calculated members from the ANALYZE tab in the PIVOTTABLE TOOLS ribbon.  Use the OLAP Tools menu and select the MDX Calculated Member option.  If you have already created the member, use the Manage Calculations option to edit existing members and measures.

image

The New Calculated Member dialog is different from the dialog used to edit the member.  Let’s start with the create dialog as noted below.

image

(1) Assign your measure a name.  It will have to be unique in the context of the the Parent Hierarchy.

(2) Assign the Parent Hierarchy and Parent Member.  This establishes where you plan to locate the new member.  As you can see you can choose any hierarchy you use in your cube.  The Parent Member property lets you choose that first level within the hierarchy.  In my case, I am choosing the Poll Category attribute hierarchy and the All member as the parent.

(3) Create your member with MDX.  Because you are creating a member, it is important that your MDX resolves to a member.  As a side note I used a set initially which passed the Test MDX operation, but displayed as #VALUE in Excel.

(4) Test MDX will allow you verify you have no syntax errors in your member creation.  However, as I noted in step 3 it is not flawless, so you may still have issues even though it is valid syntax.

A couple of important concepts. In my situation, the Cool Cats member stopped at the top level.  If I put this in the Category hierarchy which has multiple levels, Cool Cats would have no children as it is a stand alone member.  However, when applied at a filter level it will filter results properly. The image below shows the filter in use.

image

Now when I pull the Poll Category hierarchy into the pivot table you will see that Cool Cats is a peer member and has the valid value.  By default Excel will not calculate the Grand Total with those members twice.

image

You will need to be prepared to properly call this out for your users so the understand how calculated members operate in this scenario.

A common use case for creating calculated members is to create date members that aren’t easily created or tested.  This will allow you to work out the member and how it affects the user experience.





The Changing World of BI, A New White Paper for Magenic

19 06 2013

MagenicLogo2012x70tallIn the ever changing landscape that is Business Intelligence (or is that Business Analytics?), a fellow business analyst from Magenic, Chuck Whittemore (B), and I authored a white paper based on our experiences over the past months.

What I think makes our work unique, a BI architect and a business analyst came together to show our worlds colliding in the age of modern BI tools.  While the goal has always been to bring the data to the users and let them work with it as creatively as possible, the tools to do this were IT focused.  What we see now is that with the advent of in-memory, client-side BI tools, users are now able to get to this on their own.  Microsoft has invested heavily in Excel to make it a first-class BI tool.  Our paper discusses this disruptive nature of the new tools and how Excel is being pushed to the next level.  After all, Excel is everywhere already.

Enjoy the read and I welcome your feedback.

The Changing World of Business Intelligence: Leading with Microsoft Excel





Exploring Excel 2013 for BI Tip #7: Retaining Pivot Table Size After Data Refreshes

23 05 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Retaining the Pivot Table Size after a Data Refresh

Let’s start with the fact that these tips are a result of my work with Excel to support BI tasks.  This turns out to be one of those things that power users of Excel probably already know.  I had to find this out and thus I am sharing it here.

The Problem

When I was working on the dashboard for Modern Apps Live! I had some nicely formatted tables.  However, I had two pivot tables stacked as seen in the picture below on the right.

Summary Dashboard

Whenever I did a data refresh the columns would automatically resize to the “one inch” width.  Making the Poll Questions in the second table unreadable.

The Solution

I went looking for how to fix this.  I searched for solutions online and I poked around to see if I could fix this problem.  After all, it was very important to be able to refresh the data on the dashboard.

I was able to locate the solution here. As it turns out, you need to unselect the Autofit column widths on update property on the PivotTable.  You can get to those options in a couple of ways in Excel 2013.  First you can right click on the table to get the short cut menu and select PivotTable Options.

image

Or you can select Options on the far left of the ANALYZE tab on the PIVOTTABLE TOOLS ribbon.

image

Once you are in PivotTable Options, you go to the Layout & Format tab and deselect the Autofit option as mentioned above.

image

Voila! Problem resolved, now my dashboard does not need the columns resized in the pivot tables each time it is refreshed.








Follow

Get every new post delivered to your Inbox.

Join 730 other followers

%d bloggers like this: