Power Pivot Deployment

  • Blue Mancunian

    Right there with Babe

    Points: 736

    Hi all, I am SQL Server DBA. I am investigating different BI options for an organisation. Today I just had QlickView did a demo for us. It is good to know the new trend of in-memory analysis solutions. I know Microsoft has released an Excel add-on Powerpivot which is also capable of in-memory analysis. But as Power Pivot is based on Excel, could you guys shed some lights on the best way of deploying Power Pivot in the following enterprise environment.

    5-10 Business analysts/Report developers

    5-10 internal report users (in domain)

    20-50 external report users who prefer to access report via browser

    (outside the domain).

    I would like to have a Power Pivot server, if possible, for dataset version control and reduce resource requirement on individual desktop. But I prefer not to use SharePoint. The reason for this is not to increase the scale of the project, as we are not currently running SharePoint

    We also have asp.net application we would like to provide on-line analysis service in the future. Does Microsoft has plan to release a in memory BI services, which can be integrated into non-SharePoint web application. (Please let me know if there is such a thing already.) Thank you very much. Discussions are welcome.

  • stevefromOZ

    SSC-Forever

    Points: 43646

    I can't remember exactly what I've been NDA'd to say or not say, so, I'll let Chris Webb say some of it 🙂

    Some notes from recent PASS summit[/url]

    But in general, "they're moving that way"

    Steve.

  • Blue Mancunian

    Right there with Babe

    Points: 736

    Thanks, Steve.

    But with current Power Pivot, do I have to deploy it through SharePoint?

  • stevefromOZ

    SSC-Forever

    Points: 43646

    "Sort of" 🙂

    If you mean, to have a Server based instance of this PPivot database, do I need SharePoint? Then yes, you do.

    If you mean, can I deploy my PPivot DB's by either using a file share or emailing them to all and sundry, then yes, you can do that too.

    Not sure in it's current state, that this will do what you need it to do. You 'could' expose data from the Sharepoint hosted DB instance to the outside world using a report/s but this would require you to code up the access (ie write the query/s to extract the data and present it - not nice).

    Steve.

  • Blue Mancunian

    Right there with Babe

    Points: 736

    Thank you, Steve.

    We have compared the demo from both QlickView and PowerPivot from Sharepoint. We did find that Qlickview is more user intuitive, for example the click-able charts and intelligent filters. Moreover, speed wise Qlickview seems to be quicker than PowerPivot in our demos. But I do like the easiness of setting up a analysis in Excel with PowerPivot. And I am looking forward to what MS can offer in SQL Server Denali.

  • dave_lumley

    SSC Enthusiast

    Points: 164

    before you go rushing into ClickView - there is one key point I think I should make.............

    almost all roads lead to Excel...... :w00t:

  • jeffelderton

    Grasshopper

    Points: 11

    No need to have your own SharePoint environment. That is what Pivotstream does - maintain a SharePoint environment and offer tabular models, SQL instance, or you can just use workbook publishing. It is less expensive, more powerful, and more scalable than Power BI.

    Public-facing demos are here... https://explore.pivotstream.com/Pages/default.aspx.

  • Blue Mancunian

    Right there with Babe

    Points: 736

    Pandas deals with the following three data structures -
    Series
    DataFrame
    Panel
    These data structures are built on top of Numpy array, which means they are fast.

    The best way to think of these data structures is that the higher dimensional data structure is a container of its lower dimensional data structure. For example, DataFrame is a container of Series, Panel is a container of DataFrame.

    Series
    Homogeneous data
    Size Immutable
    Values of Data Mutable

    A pandas Series can be created using the following constructor -
    pandas.Series( data, index, dtype, copy)

    A series can be created using various inputs like -
    Array
    Dict
    Scalar value or constant

    https://www.tutorialspoint.com/python_pandas/python_pandas_series.htm

    Create a series Example:
    import pandas as pd
    import numpy as np
    data = np.array(['a','b','c','d'])
    s = pd.Series(data,index=[100,101,102,103])
    print s

    outputs:
    100 a
    101 b
    102 c
    103 d
    dtype: object

    Accessing data from series with position and with labels/index
    import pandas as pd
    s = pd.Series([1,2,3,4,5],index = ['a','b','c','d','e'])
    #retrieve the first element
    print s[0]
    1

    #retrieve the first three element
    print s[:3]
    a 1
    b 2
    c 3

    #retrieve the last three element
    print s[-3:]
    c 3
    d 4
    e 5


    #retrieve a single element
    print s['a']
    1


    DataFrame
    Heterogeneous data
    Size Mutable
    Data Mutable

    A pandas DataFrame can be created using the following constructor -
    pandas.DataFrame( data, index, columns, dtype, copy)

    A pandas DataFrame can be created using various inputs like -
    Lists
    dict
    Series
    Numpy ndarrays
    Another DataFrame

    Example: Creating df from list
    import pandas as pd
    data = [['Alex',10],['Bob',12],['Clarke',13]]
    df = pd.DataFrame(data,columns=['Name','Age'])
    print df

    output
    Name Age
    0 Alex 10
    1 Bob 12
    2 Clarke 13

    Example : Create with index, note the column name

    import pandas as pd
    data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42]}
    df = pd.DataFrame(data, index=['rank1','rank2','rank3','rank4'])
    print df

    Age Name
    rank1 28 Tom
    rank2 34 Jack
    rank3 29 Steve
    rank4 42 Ricky

    Example: create df from Dicts

    import pandas as pd
    data = [{'a': 1, 'b': 2},{'a': 5, 'b': 10, 'c': 20}]
    df = pd.DataFrame(data, index=['first', 'second'])
    print df

    a b c
    first 1 2 NaN
    second 5 10 20.0

    Example: Create df from Dict of Series
    import pandas as pd

    d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
    'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}

    df = pd.DataFrame(d)
    print df

    one two
    a 1.0 1
    b 2.0 2
    c 3.0 3
    d NaN 4

    Example Column/Row selection
    import pandas as pd

    d = {'one' : pd.Series([1, 2, 3], index=['a', 'b', 'c']),
    'two' : pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])}
    df = pd.DataFrame(d)
    print df ['one']

    a 1.0
    b 2.0
    c 3.0
    d NaN

    Row selection with loc on index:
    print df.loc['b']

    one 2.0
    two 2.0
    Name: b, dtype: float64

    Integer location with iloc function
    print df.iloc[2]

    one 3.0
    two 3.0
    Name: c, dtype: float64

    Slice Row
    print df[2:4]

    one two
    c 3.0 3
    d NaN 4

    Column addition:

    df['three']=pd.Series([10,20,30],index=['a','b','c'])

    df['four']=df['one']+df['three']


    Row addition:

    Append

    Column deletion:
    del df['one']
    df.pop('two')

    Row deletion:

    df = df.drop(0)





    Panel

    Heterogeneous data
    Size Mutable
    Data Mutable
  • williamjoseph.lpa

    Newbie

    Points: 3

    I have compared the demo version of both QuickView and PowerPivot from Sharepoint and found that Qlickview is more user intuitive than PowerPivot.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply