Building my first BI solution with Power Pivot

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
48
Hi,
I am a long time Excel user and would consider myself to sit somewhere between intermediate and advanced. In my current work environment we have been using Excel extensively as a reporting tool, linking to our internal databases (Access and MS SQL).
I have been playing with Power Pivot for a short while and am looking to use it whereever possible. I'd like a little guidance on how to best architect the solutions... ie: what goes in the source database, what goes in Power Pivot and what goes in Excel. Best illustrated by an example:

I have a single data source (local MS SQL table) containing approx 1m records. Each represents a sales transaction from one of our 30 retail stores. Relevant fields would be: TransactionDate, StoreName, NetSalesTotal, DiscountTotal and CostTotal

I'd like to build a ranking report which shows the store name and a ranking for NetSalesTotal for a preset date range.

Something like:

StoreName RankingLastWeek RankingLastMonth RankingLastYear
Sydney 1 2 1
Melbourne 2 1 3
Brisbane 5 3 2
etc

I've added the table to my data model. I've designed the report layout in a worksheet. What is the recommended way to link the two, given that the report is a highly summarised version of the data source? If the source data was in a "regular" MS Excel table then I would be comfortable getting it into my report.

Any suggestions appreciated.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,055
Bit confused if youre using BI or Excel

Excel: Insert>Pivot table>USe External Data Source>Choose Connection>Tables. Your data should be there from Power Pivot.

BI: Get Data from SQL server and add in your query.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,055
Sounds like you need to add some columns to the data also.

Can you then Rank through Pivots?

Last Month = IF(MONTH([TransactionDate]) = MONTH(EDATE(TODAY(), -1)), "Yes", "No")

Last Week =
VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(DATA, DATA[TransactionDate] = TODAY()))
RETURN
IF([Week No] = Current_Week-1, "Yes", "No")


Week No =WEEKNUM(DATA[TransactionDate])

IsYear= IF(YEAR([TransactionDate]) = YEAR(TODAY()) -1, "Yes", "No")
 

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
48
I'm ok with the ranking side of things, although it gets a little technical depending on the timing of the report. Our weeks run from Mon-Sun. If the person open the file on a Thursday (say) then they'd probably want to run it for the current week. But if they open it on a Monday then they are probably interested in last week. So the formulas get a little trickier. But I digress... this is not where i am stuck.
It more about how (or rather, where) to put the calculated summary data required to do the ranking. Should I put the data in a new table in the source DB, somehow derive it in Power Pivot or create data table in Excel and then add it back to the model and build a pivot off it? That seems a little inefficient.
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
I would add a couple of suggestions:
If you need to manipulate or transform or combine your data before loading to the Data Model, use Power Query as a first step.
You will probably require a Calendar Table in the Model to fully utilise some of the Date functions you need to calculate your rankings. In my opinion the Calendar should be constructed in PQ.

Peter
 

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
48
Thanks Peter,
I am thinking that the current solution's requirements might be a little to complex for a beginner to take on as a first project in Power Pivot. I might stick to what I know for this one and build it directly in Excel.
 

Forum statistics

Threads
1,085,183
Messages
5,382,176
Members
401,779
Latest member
Thonor

Some videos you may like

This Week's Hot Topics

Top