Stairway to MDX - STEP 1: Getting Started with MDX

  • pauln

    SSC Enthusiast

    Points: 156

    I'm having a problem with getting the cubes setup. I deployed the Adventure Works DW 2008 SE to my local machine and when I connect to the Analysis Server I can see the Adventure Works DW 2008R2 SE database. And I can drill down and see the Adventure Works cube, and drill further I see Measure Groups.

    But when I highlighted the AW cube and clicked New Query I get a message: "Error loading metadata. No cubes were found". So I clicked the Process and get an error 'The datasource, "Adventure Works DW', contains an ImpersonationMode that is not supported for processing operations."

    This is the same set of errors I was getting when I initially tried to deploy from BIDS. I found a post on here about this and changed the Deployment option to 'Do not process' and the Impersonation Information of the Data Source to 'Use the credentials of the current user'.

    What security settings do I need to process these cubes?

    *************************************************************************************

    Well, one of the errors from some setting on the Inheritance tab referred to NT Network Authority. So, I gave that account db_owner on the AWDW2008R2 and I was able to process the project in BIDS. And I have items in the Measure Group of the query window.

    Now the question is whether I have to always give NT AUTHORITY\NETWORK SERVICE db_owner rights to the databases I want to participate in AS?

    Thanks.

  • MWise

    SSCarpal Tunnel

    Points: 4074

    You should be able to limit the access to read only of the tables you need to build your cube.

  • pauln

    SSC Enthusiast

    Points: 156

    Mhlewis (5/25/2011)


    You should be able to limit the access to read only of the tables you need to build your cube.

    But it will be necessary to give the NT AUTHORITY\NETWORK SERVICE access to the underlying database? Can't some other account be used for this?

  • YSLGuru

    SSC-Insane

    Points: 21517

    Newbie Question

    I was referenced this series by a post on the forums but after reading the highlights (not the article in full) I'm not sure this is the right starting point.

    I am familiar with BI in the buzz words and some terms but all of my DB experience has been on the relational model side and not in Data warehouses or Cubes and certainly not MDX. I inquired about the Date Warehouse/Model/Cube business because I thought it might provide a solution for reporting performance issues we have but now I'm not so sure. Part of my problem is that I believe many of the BI terms like Data MArt and Data Warehouse have been misused by others I have listened to and so I have the incorrect idea of what these are and how they work.

    Is there any non-BI guru overview of ANALYSIS SEVICES and when/why/how to use it and how it relates to the traditional relation database model?

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • abhij33t

    SSC Eights!

    Points: 898

    SELECT

    {[Date].[Calendar].[Calendar Quarter].&[2007]&[1],

    [Date].[Calendar].[Calendar Quarter].&[2007]&[2] } ON AXIS (0),

    [Reseller].[Reseller Type].[All Resellers] ON AXIS (1)

    FROM [Adventure Works]

    where [Measures].[Reseller Sales Amount]

    and

    SELECT

    {[Date].[Calendar].[Calendar Quarter].&[2007]&[1],

    [Date].[Calendar].[Calendar Quarter].&[2007]&[2] } ON AXIS (0),

    [Reseller].[Reseller Type].[All Resellers] ON AXIS (1)

    FROM [Adventure Works]

    give me same result. Can somebody explain why ?

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    abhijeet.send2me (11/2/2011)


    SELECT

    {[Date].[Calendar].[Calendar Quarter].&[2007]&[1],

    [Date].[Calendar].[Calendar Quarter].&[2007]&[2] } ON AXIS (0),

    [Reseller].[Reseller Type].[All Resellers] ON AXIS (1)

    FROM [Adventure Works]

    where [Measures].[Reseller Sales Amount]

    and

    SELECT

    {[Date].[Calendar].[Calendar Quarter].&[2007]&[1],

    [Date].[Calendar].[Calendar Quarter].&[2007]&[2] } ON AXIS (0),

    [Reseller].[Reseller Type].[All Resellers] ON AXIS (1)

    FROM [Adventure Works]

    give me same result. Can somebody explain why ?

    If you open the AdventureWorks cube in BIDS, and select the properties of the cube (in the Cube Structure tab), you'll notice that Reseller Sales Amount is chosen as the default measure of the cube. This means that this measure is taken if no measure is selected in the query.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    I'm starting to learn MDX, and this article is a great start.

    On to the rest of the articles of this stairway series!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • abhij33t

    SSC Eights!

    Points: 898

    Thanks.... Got it.

    Could you please tell me how can we change this default measure, if we have to !!!

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    abhijeet.send2me (11/7/2011)


    Thanks.... Got it.

    Could you please tell me how can we change this default measure, if we have to !!!

    By changing the property DefaultMeasure on the cube? It gives you a nice dropdown box where you can choose the measure that you want.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • abhij33t

    SSC Eights!

    Points: 898

    Thanks man... u rock.

    Koen Verbeeck (11/7/2011)


    abhijeet.send2me (11/7/2011)


    Thanks.... Got it.

    Could you please tell me how can we change this default measure, if we have to !!!

    By changing the property DefaultMeasure on the cube? It gives you a nice dropdown box where you can choose the measure that you want.

  • depolo81

    SSC Rookie

    Points: 31

    Hello all!

    I'm really proud to join this forum.

    I really like the BI topics, and lately, i read several books of stacia Misner.

    Can somebody advise me how to contact her?

    Thanks in advance.

  • qutub

    SSC Veteran

    Points: 224

    This gona be fun learning.... i will keep looking for this series.. Thanks

  • depolo81

    SSC Rookie

    Points: 31

    Hello all!

    Please where can i find a copy of the book "SQL Server 2008 Reporting Services step by step" of stacia Misner?

    I can't buy it at amazon. If somebody can do it for me, i'll appreciate.

    Even if i can have a copy in pdf format, i'll be proud of it.

    Thanks in advance.

  • Duran

    SSChampion

    Points: 13259

    I enjoyed it too, but could someone please explain what the following represents, please?

    &[1] and the &[2} at the end of the Calender Quarter parts, I'm not sure what they do.

    Regards,

    D.

  • arturm_007

    SSC Enthusiast

    Points: 191

    Another quite good lesson:

    http://www.learn-with-video-tutorials.com/mdx-video-tutorial-free

    https://www.youtube.com/user/learnwithtutorials/playlists

Viewing 15 posts - 16 through 30 (of 34 total)

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