Building my first BI solution with Power Pivot

[vegasbaby207]

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]

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.

 

[vegasbaby207]

I'm using "Power Pivot For Excel"

 

[mrshl9898]

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]

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]

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]

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.