I was working on a requirement that needed me to write MDX code that had an alias for a measure name. If it was SQL, you could just do something like select [column name] as [new column name] from [table name] but in MDX it’s not straight forward. What you could do though is declare members in your MDX statement. so to alias a measure name & your code would look something like:
[code type=”sql”]
with member [measures].[new name] as [measures].[old name] select {[measures].[new name]} ON COLUMNS, {[Date].[Calendar Hierarchy].[ALL]} ON ROWS FROM [Cube Name] [/code]
if you’re executing MDX code using OpenQuery, then you’ve one other option, something like this would work:
[code type=”sql”]
Select "[measures].[old name]" as "[measures].[new name]" from openquery(ConnectionName,’select {[measures].[old name]} ON COLUMNS from [cube name]’)
[/code]
Note: After taking inputs from business, Naming should be taken care in the cube. My requirement was to pull data from multiple cubes & so I was aliasing it so that I could standardize the names across different cubes.
I hope this gives you a good starting to alias names while writing MDX code.
In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #1 of N:
a. How to Sort Data?
There are two ways to do this & here they are:
#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:
#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:
b. How to add slicers?
They might not know what slicers are but I’ve been asked how can I add filters where users can see the filter values before picking the value. I’ve also been asked by a Power user for the capability for adding “global filters” who had couple of pivot tables in a single sheet. They also come in handy if you’re building Excel Dashboards. With that, here are the steps:
1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click
2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:
Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.
3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)
4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar
5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK
6. you’ll see a slicer on your sheet now:
7. Let’s format the report to make it look better:
#1: move the slicer such that it does not overlap any of the pivot tables.
#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:
select the slicer > from the toolbar > slicer tools > column
#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:
8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables
9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:
10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:
#1: Sliced by Bikes
#2: Sliced by Accessories
c. How to change the layout of the Pivot Tables?
Without formatting and changing the layout, the excel pivot table looked like this:
Now, let’s work step by step to meet the requirement:
Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools
Step 2: Now here, Go to Report Layout > Show in Tabular Form
Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.
Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3
Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:
Step 5: so now it looks as follows:
To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.
There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?
Solution:
Here are the steps:
1. Open Power Pivot Model.
2. Go to Design Tab.
3. Click on Table Properties:
4. From here, You can add additional columns:
5. click on save and that should add the column to your model:
FAQ:
#1: I can’t see what you see inside Design Tab. What do I do?
#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:
you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!
Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.
What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:
BTW, the above data is from my Facebook Page Insights.
So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.
Solution:
Here are the steps:
1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.
2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:
3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column
Note that I’ve selected all columns that I want to UnPivot:
4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.
If everything looks OK, click on Done in the bottom right corner
5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!
And then its super easy to create charts, Here’s one I created after I had unpivoted the data:
Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.
FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.
That’s about it for this post, Here are some Related articles:
In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.
Problem:
Our Excel Table has following Data:
Month
Month Name
1
January
1
January
1
January
2
February
2
February
3
March
And we want to remove duplicates to make the data-set look like this:
Month
Month Name
1
January
2
February
3
March
In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.
Solution:
If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:
1. Open Data in Excel. Switch to Data Explorer Tab
2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.
3. Data Explorer add-in > Excel Data> From Table
4. After you’ve clicked on the From Table, a query editor will pop up:
5. Select both columns
(you can select both columns by: select first column > hold down the ctrl key and then click on second column)
6. Right click > Remove Duplicates
7. click on done if you see that the duplicates have been removed correctly
Conclusion:
In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.
I wanted to explore a data-set in excel. I thought I would do that using Excel. The problem was that when I opened the data-set, data was in one column. It was “supposed” to be in different columns but no – I found that it was in one excel cell. This was not Excels fault – it was just the way the data-set was defined. Here’s what I mean:
Can you see that the TWO values are in ONE column?
Problem? Yes. How do we solve it? Turns out there’s a nice feature called “Text to Columns” that should be of help here. Let’s try that:
1) Excel Toolbar > Data > Data Tools > Text to columns
2) This should open the “convert text to columns wizard”
Step 1: I chose Delimited
Step 2: I chose Comma as the delimiter.
Here are other delimiters that you could choose:
Step 3: I left the default choices. But you could change the data format if you want. You could also choose the destination cells.
Clicked on FINISH
3) Nice! Here’s what I wanted – And I added a header row.
And my data exploration:
Conclusion:
In this blog-post, we saw how one can split excel cell into separate columns at each comma, tab, space, semicolon or user-defined-character.
