Category Archives: Tabular Model

Include post related to SQL Server 2012, BI Semantic Tabular Model

Missing out on SSAS Data Source Views

We usually don’t give much importance to the Data Source Views (DSV). But if we work on it properly, it makes your life very easy.

Refer to few of the following notes on DSV, which might help in making it more useful for your project.

  1. Its only DSV where you can pull multiple data sources tables. This is the only place where you bring multiple data sources table in 1 DSV and later use in cube. how else ?? ūüėõ
  2. While creating manual DSV (New Diagram), you can pull one table only and then add related tables one by one (option is Right Click–> Show related tables). So you for example you can literally pull a fact table and call related tables for related dimensions. Also for those dimension call related tables for snow flake tables and in the end get one good quality Diagram without much hassle.
  3. You also get to see original name of column even if you change to Friendly¬† Name for it (doesn’t work for table though). Following is the screenshot of how column name appears after renaming it. Table is also renamed from original DimGeography but only new name appear.
  4. you might want to see only original name, to avoid any confusion with table or column or may be just want to know what came from source. Right Click –> un-check option Show Friendly Names.
  5. You cannot delete a column just by Click–>Delete on DSV. Change the table to Named Query and remove the column from list of SELECT is your option.
  6. DSV always warn you for incorrect relationships. For example, where it should be Many to one or one to many, Matching data type warning etc. Always listed to these warning. This is your practice ground for dimension usage relationships.
  7. Did you use Zoom –> To Fit option ? If not then try it thank me later.
  8. You can also manipulate values in Explore Data. Like select random or top sorted sample. Even increase/decrease the number of sample rows, by default its 5000. To achieve this, Explore Data for a table and in window with sample click on Setting option and manipulate as your choice (Don’t forget to click on refresh button to sample, all highlighted in following screenshot).
  9. If you are new to SSAS DSV or tutoring, use Name Matching Criteria for quick relationships from tables pulled from data source. Click anywhere on DSV and look at properties with NameMatchingCriteria.

I could talk about some more interesting DSV related things, but length of the post is a concern. Let me know if you need details on these or anything more under comments section.

Advertisements

Copying Cube in same SSAS Solution/ Database

To start with, apologies to my friends and followers who keep an eye on the blog. It‚Äôs been a long time¬†since I have written anything here. But¬†I’ll try my best no gaps now on.

Requirement:

  • So¬†it‚Äôs a small requirement of creating one more Cube in¬†same SSAS Solution. New cube should be¬†same as the cube we have in solution, after getting this copy we can whatever necessary change required.

Solution:

  • Yeah I know this shouldn’t be too difficult to implement but guess what, I was not able to find a small and easy way mentioned anywhere. So I am giving you most obvious two options:

a) COPY and PASTE

It as simple as they say. See the initial solution outline in Fig 1:

 InitialCubes

Once you copy the cube (fig 1) and tries to paste it (fig 2) it asks for a new name (fig 3) and finally you get a new cube solution (fig 4).

 Creating Cube CopyFig 1  Paste CubeFig 2
 Name Copy CubeFig 3  CubeCopyFig 4

b) Use CREATE XMLA

Connect to the cube using SSMS. Select the Cube you want to copy and Right-Click cube and select option Create XMLA for Cube.

 CreateXMLA

Once you get the Cube XMLA in front of you, update two tags only. below is the figure that shows “Before” we updated the query which is actual cube XMLA, then “After” we updated the query which is what changes we need to do. And the changes are new cube Name and ID.

 CubeXMLA

Execute your new query in SSMS and VOILA!! you get your new cube under same solution.

Now you can update this new cube for whatsoever reason you want.

But where is the climax here, What’s the point I am sharing this all, Is there anything interesting left in the post ??????? YES ūüôā

When we created a copy of cube using second the Option; it creates the cube which you cannot process, which you can open at any BIDS solution.

So the learning is:

  • Cube created from a copy of older should have different PERSPECTIVEs. Two or more cubes in same Solution/ database cannot have same perspectives. So in first Option COPY/PASTE, new cube is created with new name and same Name changes are done to Perspectives automatically (you can observe in solution. So you might want to change the name as per your requirement for perspectives.In second Option CREATE XMLA, we need to find the Perspective names and IDs in code and update those with new names. And then job is done.